CODEWITHSUNDEEP
t-sql
LP13
LP13

Reputation: 34079

How to insert dynamic sql result into temp table without knowing the columns in advance

I am trying to insert result of dynamic sql into temp table. Important thing is i dont know the column names in advance. As per the SO suggestion the following should work

INSERT into #T1 execute ('execute ' + @SQLString )

also, omit the EXECUTE if the sql string is something other than a procedure.

However this is not working on SQL 2017

CREATE TABLE Documents(DocumentID INT, Status NVARCHAR(10))

INSERT INTO Documents(DocumentID,Status)
VALUES
(1,'Active'),
(2,'Active'),
(3,'Active'),
(4,'Active')


DECLARE @SQLString NVARCHAR(MAX)
SET @SQLString = 'SELECT * FROM Documents'


INSERT into #T1 execute ('execute ' + @SQLString )

I get error `Invalid object name '#T1'.`

Then i tried by omitting execute

INSERT into #T1 execute (@SQLString)
with same error `Invalid object name '#T1'.`

I should be able to do SELECT * FROM #T1

Upvotes: 0

Views: 1179

Answers (1)

JMabee
JMabee

Reputation: 2300

You cannot do an INSERT INTO without having the table predefined. But what I believe you are asking is to do a SELECT INTO. I am aware of two ways of doing it. The first uses OPENROWSET, but I believe this has some drawbacks for security purposes. You could do the following:

sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * 
INTO #T1
FROM OPENROWSET('SQLNCLI', 
                'Server=localhost;Trusted_Connection=yes;', 
                'SELECT * from <YOURDATABASE>.dbo.Documents')

Your second option is to create an inline TVF that will generate the table structure for you. So you could do the following:

CREATE FUNCTION getDocuments()
RETURNS TABLE
AS
    RETURN
    SELECT * from Documents
GO

SELECT * into #T1 FROM getDocuments()

Upvotes: 1

Related Questions