Inserting into a temporary table from an Execute command

sql sql-server insert execute temporary
Daniel Billingham picture Daniel Billingham · Jul 17, 2013 · Viewed 36.1k times · Source

I need to insert data from a select statement into a temporary table using the execute command.

if OBJECT_ID('tempdb..#x') is not null
drop table #x

Create Table #x(aaa nvarchar(max))

declare @query2 nvarchar(max)
set @query2 = 'SELECT [aaa] from IMP_TEMP'

INSERT #x
SELECT [aaa] from IMP_TEMP -- THIS WORKS
SELECT *from #x

INSERT #x
exec @query2 -- THIS DOES NOT WORKS, WHY?
SELECT *from #x

Answer

Nenad Zivkovic picture Nenad Zivkovic · Jul 17, 2013

You just need parenthesis around @query2 variable. EXEC command is to execute stored procedure, while EXEC() function is for executing dynamic sql taken as parameter.

INSERT #x
exec (@query2)
SELECT *from #x

Reading material

Related questions

Inserting multiple rows in a single SQL query?

I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "…

Read More
Solutions for INSERT OR UPDATE on SQL Server

Assume a table structure of MyTable(KEY, datafield1, datafield2...). Often I want to either update an existing record, or insert a new record if it doesn't exist. Essentially: IF (key exists) run update command ELSE run insert command What's the …

Read More
Exporting data In SQL Server as INSERT INTO

I am using SQL Server 2008 Management Studio and have a table I want to migrate to a different db server. Is there any option to export the data as an insert into SQL script??

Read More