June 5, 2008 at 4:55 am
I need to at reuntime create at temp table that has the same structure as an other at-run-time known table.
Due to scoping restrictions, I am not able to perform a "select * into #temp from sourcetable". As its runtime, the wizards in SSMS are not helpful either.
My current plan is to:
1. Create the temp table with a dummy column
CREATE TABLE #temp(dummy bit)
2. Get all correct 'ALTER TABLE ADD' the columns from the sourcetable
3. EXEC the scripts from #2 to modify the #temp table
4. Drop the 'dummy' column
5. Use the #temp table to perform my evil work.
My problem is how to get a correct script for the column;datatype, lengths, precisions etc from the source table. Its easy enough to find this using information_schema views, but generating the script is not that straight forward as I would need to create a lot of different if's and elses to create the correct 'ALTER TABLE ADD' with the correct paramters for the datatype.
Anyone know about a script or proc or any other conjuring to perform this?
Any table cloning scripts I could modify or something?
June 5, 2008 at 8:51 am
Could you explain this in a little more detail?
Due to scoping restrictions, I am not able to perform a "select * into #temp from sourcetable".
😎
June 5, 2008 at 4:23 pm
Lynn Pettis (6/5/2008)
Could you explain this in a little more detail?Due to scoping restrictions, I am not able to perform a "select * into #temp from sourcetable".
😎
As the sourcetable is not known at design time, I need to sp_executeSql the "select * into #temp...". But then the #temp table created in the sp_executeSql would not be available outside the sp_executeSql and then quite useless.
June 5, 2008 at 4:33 pm
I'll do a little digging. Second question, though, could you use a uniquely named global temp table (##Temp01)?
😎
June 6, 2008 at 4:47 am
Lynn Pettis (6/5/2008)
I'll do a little digging. Second question, though, could you use a uniquely named global temp table (##Temp01)?😎
Great!
So far, I've looked into SQLDMO, but I'm only able to get a complete 'CREATE TABLE' script. So a little parsing could get me the result, but I'm a bit reluctant, as the SQLDMO stuff is being replaced by SMO (which is not possible to run within Sqlserver directly).
I have considered a global temp table, but this would need to be unique pr. batch, and then the rest of my code would have to be sp_executeSql (to use the runtime created unique GTT name). Because of sheer volume of the data, I have discarded the possibility to use of a session-key in the GTT to separate the batches.
It just baffles me that sqlserver dont have built in functionallity to get the type script for a column without the use of external tools.
June 6, 2008 at 7:13 am
If you don't know the name and structure of a table before runtime, then it seems something is not quite as it should be... I'm aware that it might be something that you simply cannot influence, but it isn't good anyway. That's probably why SQL Server doesn't have any tools for it.
Can you explain a little bit more about your problem - what it is you are doing, how is it possible that the tables can be different and how you can know what to do with the tables if you don't even know what columns they have until runtime?
BTW, if the only thing you need is to find out what columns are in a table, you can find them with a script from syscolumns table.
June 6, 2008 at 3:15 pm
Vladan (6/6/2008)
If you don't know the name and structure of a table before runtime, then it seems something is not quite as it should be...Can you explain a little bit more about your problem - what it is you are doing, how is it possible that the tables can be different and how you can know what to do with the tables if you don't even know what columns they have until runtime?
BTW, if the only thing you need is to find out what columns are in a table, you can find them with a script from syscolumns table.
The actual problem is related to loading unknown tables into a data warehouse. Part of the table source table is known (the basic columns used to map to dimensions), but the facts (measurements) can vary and a additional columns should be added to the target fact tables. To create theese columns correctly, I need to know the datatype of the source column preciscely.
I can navigate the sys.columns etc for the source table, but as mentioned before, I was hoping to avoid writing the logic for creating the correct 'ALTER TABLE ADD xxx [datatype spec]' my self.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply