Marcus Aurelius
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
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
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)
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:
Example:
SELECT Column1, Column2, Column3
INTO #tempTable2
FROM #tempTable1
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
ASKER
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.
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
Now, the main question is how do you know which columns to use?
ASKER
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....????
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
ASKER
thx
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.