Populating a column while inserting

Question

How do i populate the column colNEW in table #tt with the value from column col5 table @t2 in my INSERT statement ? Can my current expression be reused, or do i have to use merge ?

I am using mssql server 10.

DECLARE @t1 TABLE (id INT IDENTITY(1,1), col1 INT, col2 INT)
DECLARE @t2 TABLE (col3 INT, col4 INT, col5 INT)

INSERT @t2 VALUES (1,2,3);INSERT @t2 VALUES (2,3,4)

CREATE TABLE #tt (id INT, col3 INT, col4 INT, colNEW int)

INSERT #tt (id, col3, col4)
SELECT *
FROM
  ( 
INSERT INTO @t1(col1,col2)  
OUTPUT Inserted.id, Inserted.col1,Inserted.col2
SELECT col3, col4  
FROM @t2 
  ) t

I hope someone can help.

You have to use `merge`. http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id — Mikael Eriksson, Jul 12 '11 at 08:52
Actually, just realised - when you say mssql server 9, do you mean 2005? If so, merge doesn't exist in that version. — Damien_The_Unbeliever, Jul 12 '11 at 08:53

Damien_The_Unbeliever · Accepted Answer · 2011-07-12T09:04:03.493

2

You'll have to use merge:

DECLARE @t1 TABLE (id INT IDENTITY(1,1), col1 INT, col2 INT)
DECLARE @t2 TABLE (col3 INT, col4 INT, col5 INT)

INSERT @t2 VALUES (1,2,3);INSERT @t2 VALUES (2,3,4)

CREATE TABLE #tt (id INT, col3 INT, col4 INT, colNEW int)

INSERT #tt (id, col3, col4,colNew)
SELECT *
FROM
  ( 
MERGE INTO @t1 t1
using @t2 t2 on 1=0
when not matched then INSERT (col1,col2)  
VALUES(t2.col3, t2.col4  )
OUTPUT Inserted.id, Inserted.col1,Inserted.col2,t2.col5
  ) t

select * from #tt

This is a slight misuse of MERGE - I'm only using it because it allows other tables to be referenced in the OUTPUT clause - rather than (for insert) just the inserted table.

edited Jul 12 '11 at 09:04

answered Jul 12 '11 at 08:47

Damien_The_Unbeliever

234,701
27
340
448

Against all logic, this code doesnt work. I guess microsoft has an unintended feature (rewrite of the word 'bug'). – t-clausen.dk Jul 12 '11 at 09:32
@t-clausen.dk - I ran this script against a 2008 instance I had handy and it worked fine. Do you get an error message? – Damien_The_Unbeliever Jul 12 '11 at 09:39
@t-clausen.dk – Works fine for me. Just a copy-paste and run. – Mikael Eriksson Jul 12 '11 at 09:40
I get this message "Msg 156, Level 15, State 1, Line 12 Incorrect syntax near the keyword 'INTO'." I tried to rewrite it to a standard merge and it worked. Which mssql server version did you use ? – t-clausen.dk Jul 12 '11 at 09:51
@t-claused.dk - I tested on 2008 (not R2), but `INTO` is an optional keyword in all versions that support merge (2008, 2008 R2, Denali) – Damien_The_Unbeliever Jul 12 '11 at 09:54
removing the word "into" still gave the same error "Incorrect syntax near '@t1'" – t-clausen.dk Jul 12 '11 at 09:56
2

@t-clausen.dk - that's where an error will show if you're running on a 2005 instance, or the compatibility level is set to that. This query will only work for SQL Server 2008 or later, with compatibility 100 or later. – Damien_The_Unbeliever Jul 12 '11 at 10:06
You are right compatibility level is SQL Server 2000. Thanks alot (+1) – t-clausen.dk Jul 12 '11 at 10:41

Populating a column while inserting

1 Answers1