I have two tables
TableA (Col1 Pk identity, Col2, Col3)
TableB (Col2,Col3, Col4)
Now, I want to get the the combination of Col1, Col4 .
I am using this
INSERT INTO TableA (Col2, Col3)
OUTPUT inserted.*
SELECT DISTINCT Col2,Col3
FROM TableB
But below will give me only Col1, Col2 & Col3 of tableA.. if I am not wrong. Here I want Col1 (TableA) & Col4 (TableB) Now, how can I get Col4 and the respected indentity row in TableA.
Edit
Below is a sample scenrio which may help you
CREATE TABLE [dbo].[A](
[Col1] [int] IDENTITY(1,1) NOT NULL,
[Col2] [varchar](50) NULL,
[Col3] [varchar](50) NULL,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[Col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[B](
[Col2] [varchar](50) NULL,
[Col3] [varchar](50) NULL,
[Col4] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO A (Col2,Col3)
OUTPUT INSERTED.*
SELECT COL2, Col3, Col4
FROM B
Can I do it with Checksum?
merge A as T
using B as S
on 0=1
when not matched then
insert (Col2, Col3) values (S.Col2,S.Col3)
output inserted.Col1, S.Col4;