Link to home
Start Free TrialLog in
Avatar of Marcus Aurelius
Marcus AureliusFlag for United States of America

asked on

SQL Server how to create a DYNAMIC TABLE?

Experts,

What is the best way to load a table using a "Dynamic Source Temp Table" that may have between 1 and 10 columns on any given process run..??

I have a temp table within a Stored Procedure that will have between 1 and 10 total columns based on each process run.

The table looks like this:

Table_1
Col1, Col2, Col3, etc. etc.. up to 10 max based on run. For example, it can have 1, 5, 7 or 10 columns.

(I use a PIVOT in sql in order to create this table above so it doesn't have the ability here to dynamically create the full 10 cols as place holders...)

I need to load another  temp table "Table_2" that has a set total of 10 columns. This must be loaded "dynamically".

So sometimes I'll have 1 column to load, sometimes 4, sometimes 10 max. It can change.

What is the best way to code a table load within a stored procedure to handle the "dynamic aspect" of this load?

Thanks
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

It depends.
What are you going to do with the table after loaded?
And what's the scope for the table? You can keep the temp table or create a variable table.
Avatar of Marcus Aurelius

ASKER

Doesn't matter...I just need to load the TEMP table dynamically.

It will remain a TEMP table...I will use this further in my process to create a SQL Script for use to LOAD the actual DATA WAREHOUSE table.

But again...it shouldn't matter what the table is used for, I need to know how to load the TEMP table (10 columns)  with a DYNAMIC source temp table...(1-10 columns)
If you use SELECT ... INTO #tempTable it will create automatically the necessary columns from the SELECT command.
Example:
SELECT Column1, Column2, Column3
INTO #tempTable2
FROM #tempTable1

Open in new window

The above will create an exact copy from #tempTable1 into #tempTable2.
Just to add that if you use * then you won't need to bother with the number of columns at all:
SELECT *
INTO #tempTable2
FROM #tempTable1

Open in new window

Sorry, I forgot to mention, I must have the entire 10 columns of data,...EVEN IF NULL.

The temp table to load,... will be used to dynamically create a SQL Script to be used to load a final DW Table and so I must have ALL 10 columns listed...even if they do not have any data.

So I can't use the "INTO #Table2" technique because Table2 must have all 10 columns.
Then just create the #tempTable2 with the 10 columns before and the run the INSERT ... SELECT command:
CREATE TABLE #tempTable2
(Column1 INT,
Column2 VARCHAR(50),
....
Column10 DATE)

INSERT INTO #tempTable2 (-- put here the dynamic columns)
SELECT --dynamic columns here
FROM #tempTable1

Open in new window

Now, the main question is how do you know which columns to use?
Sorry for the confusion...but YES......That is what I need to do....

I need to know the "DYNAMIC COLUMN SQL" to use for this.....

So do you know how to create the DYNAMIC COLUMN INSERT SQL..for 1-10 potential columns....????
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm working to modify this for use.

Do you know how I can return the COLUMN NAMES in a sql script and NOT the column data...??

I need to include the COLUMN NAMES in the load script.
If you only want the column names then just use a WHERE clause that you know it won't return rows. For example:
SELECT *
FROM #tempTable1
WHERE 1=0

Open in new window

thx