MAK MAK -4 years ago 117
SQL Question

Insert into table from temporary table

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.

Answer Source

You could use an insert-select statement:

INSERT INTO test
SELECT @col1, @col2, col3
FROM   #temp
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download