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

Answer 1

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.