How to define T-SQL variables in Insert statement

Question

I have this example insert statement that used to work for copying matching rows from Table2 to Table1:

INSERT INTO Table1 SELECT * FROM Table2 WHERE ID='555'

When this statement was working, both tables had the same three columns, FirstName, LastName, and EmailAddr1.

I've since added a column, EmailAddr2, to Table1 that doesn't appear in Table2 and I want to make sure that my modified insert statement will work as I expect.

Will this accomplish my goal?

SELECT @EmailAddr2 = '[email protected]'
INSERT INTO Table1 SELECT FirstName, LastName, EmailAddr1, @EmailAddr2 
  FROM Table2 WHERE ID='555'

you should always used named inserts, never just "insert into table" e.g. INSERT INTO table1(col1, col2, col3) SELECT col1, col2, col3 FROM table2 — Rich
– Rich, Commented Jan 31, 2013 at 18:16

Avitus · Accepted Answer · 2013-01-31 18:14:31Z

3

You should do:

declare @emailAddr2 varchar(50)
set @emailAddr2 = '[email protected]'

Insert into Table1 ( firstname, lastname, emailaddr1, emailaddr2 ) 
select firstname, lastname, emailaddr1, @emailAddr2
from table2 where id = '555'

answered Jan 31, 2013 at 18:14

Avitus

16k6 gold badges47 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to define T-SQL variables in Insert statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related