0

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

Raymondo
  • 483
  • 2
  • 6
  • 20
LP13
  • 30,567
  • 53
  • 217
  • 400
  • It might be me, but where are you creating the #T1 table or was it omitted by accident in your question? If you review the "SO Suggestion" link you provided, the table does get created there – Raymondo May 22 '20 at 07:57

1 Answers1

1

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()
JMabee
  • 2,230
  • 2
  • 9
  • 13