Insert into table from temporary table

Question

I have the following table:

Example:

create table test
(
 col1 varchar(10),
 col2 varchar(20),
 col3 varchar(30)
);

Now I want to insert two values by variables and last one by #temp table.

#Temp:

create table #temp
(
  col3 varchar(30)
);

#Temp: Contains

col3
-----
A1
A2
A3

Insertion into test table:

Declare @col1 varchar(10) = 'A'
Declare @col1 varchar(20) = 'B'
Declare @sql varchar(max)

SET @SQL = N'insert into test values('+@col1+','+@col2+',........); 
EXEC(@SQL)
/* How to insert `@col3` from #temp to test table*/

Expected Result:

col1   col2   col3
------------------
A      B      A1
A      B      A2
A      B      A3

Note: The variables values must repeat until the #temp values inserted into table test.

Mureinik · Accepted Answer · 2014-12-12 14:00:28Z

45

You could use an insert-select statement:

INSERT INTO test
SELECT @col1, @col2, col3
FROM   #temp

answered Dec 12, 2014 at 14:00

Mureinik

316k54 gold badges402 silver badges406 bronze badges

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

3 Comments

MAK Over a year ago

What if I like to insert into test(col1,col3)? Not col2

Mureinik Over a year ago

Just specify the columns you want in the insert: INSERT INTO test (col1, col3) SELECT @col1, col3 FROM #temp

Dalbir Singh Over a year ago

Great answer, simple and effective.

Collectives™ on Stack Overflow

Insert into table from temporary table

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related