CODEWITHSUNDEEP
sqlsql-servert-sql
Mat Richardson
Mat Richardson

Reputation: 3606

Dynamically insert results of sp_executesql into table

I've written the following TSQL:-

declare @sql nvarchar(1000)
set @sql = N'select top 5 BusinessEntityID, PersonType from AdventureWorks2008R2.Person.Person'

create table #temp(BusinessEntityID int, PersonType varchar(10))
insert into #temp execute sp_executesql @statement=@sql
select * from #temp

This creates a temporary table with the two columns identified in @sql, and then inserts the rows of data into it, but I need to identify the columns in the create table statement first.

Is there a way to do this without prior knowledge of the columns? I'd like to be able to set @sql to any valid select statement but not have to map the columns in the create table statement. I've tried looking at using select into but I'm completely stuck now.

Any pointers would be appreciated.

Upvotes: 1

Views: 4764

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

SELECT INTO would be the only to do this. You can't use dynamic sql the way you are trying to. The select into portion would have to be part of the dynamic sql.

declare @sql nvarchar(1000)
set @sql = N'select top 5 BusinessEntityID, PersonType 
into MyNewTable 
from AdventureWorks2008R2.Person.Person'

exec sp_executesql @sql

I have to ask. Why do you need to use dynamic sql here at all? There is no reason to use this from what you posted. This could be greatly simplified to the following. No dynamic sql required at all.

select top 5 BusinessEntityID, PersonType 
    into MyNewTable 
    from AdventureWorks2008R2.Person.Person

Upvotes: 1

Related Questions