Using dynamic sql to populate a temp table

Question

I know this isn't ideal, but I would like to know if it is possible to populate a temp table based on dynamic sql?

A very similar example to what I want to achieve is shown in this answer as either

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

or

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

I couldn't get either to work. It seems from the edit that the first option was wrong, so for the second option I've tried something like;

DECLARE @SQLString VARCHAR (2000) = 'SELECT * FROM INFORMATION_SCHEMA.COLUMNS'

INSERT into #MyTempTable execute (@SQLString )

Any ideas are much appreciated.

Edit:

In an attempt to clarify what I am trying to do without being too localised, I explain as briefly as I can below.

I have data in a staging area of my database that contains tables with dynamic names and a dynamic number of columns. However, a few of the column names are the same for each table. Rather than construct everything in dynamic sql, I would like to be able to simply extract the known columns into a temp table (or table variable, CTE, derived table or whatever) and act on that.

So given a table as so;

  CREATE TABLE SomeParticularNameThatCantBeKnownToAStoredProc (
      [1] AS VARCHAR(100),
      [2] AS VARCHAR(100),
      ... -- Could be any number of these columns
      [Id] AS INT,
      [KnownCol] AS VARCHAR(100),
      [KnownCol2] AS VARCHAR(100),
      ....
      [DboId] AS INT
  )

I'd like to be able to perform the necessary operations to allow my to process this data without having to do it all in dynamic sql. I was hoping to be able to do something like;

  DECLARE @TableName AS VARCHAR(1000) = 'SomeParticularNameThatCantBeKnownToAStoredProc'

  SELECT [Id], [KnownCol], [KnownCol2], [DboId] 
  INTO #KnownName
  FROM @TableName -- I know this isn't possible, but this is what I'd like to do

This would then allow me to perform SQL statements against a consistent #KnownName. Some of the other operations I need to do are quite lengthy such as using the data to relate to other existing tables, copying data from the staging table(s) to their dbo schema equivalents and matching the DboId against the staging table Id using MERGE with OUTPUT INTO as described here, and so on and so forth.

If you can think of any other way I can limit the amount of dynamic SQL I need to write given the fact that the table name is dynamic then please let me know.

Usually helpful to explain what "couldn't get to work" means. Did you get an error? What was it? — Aaron Bertrand
– Aaron Bertrand, Commented Aug 7, 2012 at 13:49
Sorry. If you copy the very simple snippet at the end of my post and execute it, you should get "Invalid object name '#MyTempTable'." — Mr Moose
– Mr Moose, Commented Aug 7, 2012 at 13:55

Aaron Bertrand · Accepted Answer · 2012-08-07 14:37:54Z

2

Assuming #MyTempTable already exists:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM INFORMATION_SCHEMA.COLUMNS;';
INSERT #MyTempTable EXEC sp_executesql @SQLString;

Otherwise please clarify what you are trying to do. If the table isn't already created, you can do everything inside of dynamic SQL, e.g.:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * INTO #MyTempTable FROM INFORMATION_SCHEMA.COLUMNS;
  SELECT * FROM #MyTempTable;';
EXEC sp_executesql @sql;

edited Aug 7, 2012 at 14:37

answered Aug 7, 2012 at 13:48

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Mr Moose Over a year ago

Once I changed SQLString to @sql from your example above, I get the same error I posted in my example above - Invalid object name '#MyTempTable'.

Mr Moose Over a year ago

I've just re-read your post regarding the assumption that #MyTempTable already exists. This is most likely my problem then. Are you aware of a way in which I can define the structure of the temp table as well as populate it via dynamic sql. That is most likely the cause of my problems. I was under the impression that similar to this answer, the structure of the temp table was defined as a result of the query being run.

Aaron Bertrand Over a year ago

Again clarifying what you are actually trying to do might help. Why is a #temp table required here? What else are you doing with it after it's been populated?

Mr Moose Over a year ago

Thanks again for the quick response. I didn't want to confuse the question with a long story. However, the aim of getting data into a temp table will enable me to minimise the amount of dynamic sql I need to write. The dynamic sql extracts data from a staging area of the database which originate from flat files. Staging table names are dynamic and each table has differing numbers of columns (hence the dynamic sql). With user validation and column mapping, we aim to copy the data to tables in the dbo schema section of the database. I couldn't get your last example to work by the way. Same err

Mr Moose Over a year ago

After playing with your example, I can kind of get it to work by removing the INSERT #MyTempTable from the last line. However, that means that the Temp table is only available from within the dynamic sql, which isn't really what I am after. I would like to not have to refer to my dynamic table name as the only way I know of to do this is via dynamic sql.

|

Collectives™ on Stack Overflow

Using dynamic sql to populate a temp table

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related