SQL Server : creating Temp Table with dynamic column

Question

I having a problem with my current situation, I searched through any solution on the net but still I can't get it.

Here the question:

I have a bunch of SQL statements that need to be executed in a stored procedure by using cursor inside, each of the statements is performing an insert by selecting from a different database as well as table.

For example:

INSERT INTO Database1.Table1(column1, column2, column3)
   SELECT column1,column2, column3
   FROM Database2.Table2
   WHERE --Some Condition

and maybe another executed SQL statement is like this

INSERT INTO Database1.Table3(column1, column2, column3, column4)
   SELECT column1, column2, column3, column4
   FROM Database3.Table3
   WHERE --Some Condition

Okay, basically my process is like this

Execute Sql to insert into temp Tables --> Insert into a permanent Table from Temp Tables

From above two SQL statements, my executed result from database2 or 3 or may be 4,5 and etc. I will goes to my database1 for permanent store. In more summary way of telling that's is, I just want to made another copy of data that get from different source of database and store into my local database to do some further processing.

My main problem is my person in charge (or manager) told me to trow all executed result into a TEMP table or #Table before execute into the permanent.

Something like this:

INSERT INTO #Table3(column1, column2, column3, column4)
   SELECT column1, column2, column3, column4
   FROM Database3.Table3
   WHERE --Some Condition

I went through some research on #Temp tables and I found it most of them is creating with 'FIX' column such as

CREATE TABLE #Table
(
    column1 VARCHAR(10),
    column2 VARCHAR(10),
    column3 VARCHAR(10)
)

PROBLEM: is there anyway to create it with dynamic columns? More detail way of asking, is there anyway to insert by select into a #Temp table without prefix the column? Because that is impossible for me to create a bunch of temp table for each of executed SQL.

Thank you

PS 1: I am quite a newbie to SQL Server, please don't hesitate to voice out my mistake or error. We all learn from mistakes.

PS 2: Sorry for my poor English level, I tried my best to elaborate it more clearly.

Regards:

LiangCk

You can use SELECT coloum1,coloum2,coloum3,coloum4 INTO #Table3 FROM Database3.Table3 to create a table based upon the results of the SELECT. Is that what you mean? — Martin Smith
– Martin Smith, Commented Oct 30, 2011 at 15:17
Not, this is not needed because It might be deal with few hundred of sql that may be each of them is having different coloum. If i do what u done, that mean i create a few hundred of Temp table? — Worgon
– Worgon, Commented Oct 30, 2011 at 15:44
I'm not sure what you want to accomplish; as I understand it, you have several tables with different columns, and you need to insert this data into a temp table. Can you not just make a backup and restore of the entire database into a temporary database? Either that, or you'll need to manage a bunch of different temp tables. — Stuart Ainsworth
– Stuart Ainsworth, Commented Oct 30, 2011 at 20:54
@StuartAinsworth I think your first statement "you have several tables with different columns, and you need to insert this data into a temp table" is what i want. On your last statement, is that mean manage a bunch of different temp tables is the only way to do it? — Worgon
– Worgon, Commented Oct 31, 2011 at 1:10

Jeremy Wiggins · Accepted Answer · 2011-11-03 11:15:03Z

You say that you want something like



INSERT INTO #Table3(coloum1,coloum2,coloum3,coloum4)
Select coloum1,coloum2,coloum3,coloum4
FROM Database3.Table3
WHERE --Some Condition

without having to create the temp table with fixed columns first.

This would work:



Select coloum1,coloum2,coloum3,coloum4
INTO #Table3
FROM Database3.Table3
WHERE --Some Condition

You don't have to create the temp table or specify the columns first, just select into the temp table and it will be created on the fly.

It sounds like you want to do something more than this... I can't quite figure out what that is, but it sounds like maybe selecting all of your data from various tables into a single temp table (I don't know why you would want to do this)... If that's the case then UNION or UNION ALL should work. You can still use these with the dynamically created temp table.



Select coloum1,coloum2,coloum3,coloum4
INTO #Table3
FROM Database3.Table3
WHERE --Some Condition

UNION

Select column1, column2, column3, null
FROM Database1.Table1
WHERE --Some condition

The null above is just to give the 2nd select the same number of columns as the first (I used both selects from your post); this is a requirement for UNION.

Collectives™ on Stack Overflow

SQL Server : creating Temp Table with dynamic column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related