How to get the column from two tables in output clause

Question

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?

They are not connected.. one table is a staging and the other is on mart.. they resemble same columns.. but I can not insert col4 in tableA. and I want col1 and col4 — Zerotoinfinity, Sep 26 '12 at 12:40
You can probably use [merge](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id) to do what you want. — Mikael Eriksson, Sep 26 '12 at 20:51
@MikaelEriksson Thanks for the suggestion. I have tried to do this but it seems like something is wrong in this query **merge A as T using B as S on 0=1 when not matched then insert (T.Col2, T.Col3) values (S.Col2,S.Col3) output inserted.Col1, S.Col4;** — Zerotoinfinity, Sep 26 '12 at 21:03
Remove the prefix in the columns spec of the insert statement. — Mikael Eriksson, Sep 26 '12 at 21:06

score 0 · Accepted Answer · answered Sep 26 '12 at 21:07

0

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;

answered Sep 26 '12 at 21:07

Mikael Eriksson

136,425
22
210
281

Thanks, can you guide me how I can use update statement here ? – Zerotoinfinity Sep 26 '12 at 21:23
I mean I have one more column in source Col1_Dup... Update B SET Col1_Dup = Inserted.Col1 Where Col4 = S.Col4 – Zerotoinfinity Sep 26 '12 at 21:24
@Zerotoinfinite Not sure what you mean. You should probably ask that in a new question. – Mikael Eriksson Sep 27 '12 at 05:31

How to get the column from two tables in output clause

1 Answers1