1

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.

Community
  • 1
  • 1
Mr Moose
  • 5,946
  • 7
  • 34
  • 69
  • 3
    Usually helpful to explain what "couldn't get to work" means. Did you get an error? What was it? – Aaron Bertrand Aug 07 '12 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 Aug 07 '12 at 13:55

1 Answers1

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;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 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 Aug 07 '12 at 13:58
  • 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](http://stackoverflow.com/a/6100350/685760), the structure of the temp table was defined as a result of the query being run. – Mr Moose Aug 07 '12 at 14:09
  • 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? – Aaron Bertrand Aug 07 '12 at 14:11
  • 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 Aug 07 '12 at 14:28
  • 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. – Mr Moose Aug 07 '12 at 14:37
  • Can you show a full example of what you would ideally like to do please, using pseudo-code where you have to, so I can better understand the underlying goal? (Please update the question, don't post code in a comment.) – Aaron Bertrand Aug 07 '12 at 14:39
  • I really appreciate your help, but I think this is going to get too localised. I think I've realised that what I am trying to do isn't really possible without global temp tables or a different approach all togther. I'll probably leave it there for now and most likely even delete this entire question as it is of limited use to the SO community. I'll leave it open for a day or so you can receive this comment, but delete it after that. Thanks again. – Mr Moose Aug 07 '12 at 14:54
  • I don't know that it needs to be deleted, and I don't think ##global temp table is going to be the answer either (unless concurrency is really not required). I think you should give your scenario a shot - just because nobody else is doing *exactly* what you're doing doesn't mean a potential solution for others who might be doing something similar. – Aaron Bertrand Aug 07 '12 at 15:08
  • I've edited the question to provide further details in the hope that someone might have some other solutions. Thanks again. – Mr Moose Aug 08 '12 at 02:08