Can I generate Table Script in a Stored Procedure?

I have to load around 40 tables and I want to write one Stored Procedure that I pass a table name into. Data is coming from 3 different sources and to fill each of the 40 tables. I want to create a temp table that I can load first to make sure it succeeds first before I truncate and insert into the production table.

Is there a system procedure or something that will allow me to create a temp table from a table that alresdy exist in my database?

You can use SELECT INTO to create a new table based on a query like:

SELECT t.col1, t.col2, t.col3

INTO #t

FROM your_table AS t

Above query will create table with same structure as your_table without the defaults, constraints, indexes etc. Identity property on columns will be transferred though. You may also want to look at SSIS to automate your bulk loading process. You can also take a look at the link below for more pointers on how to improve the bulk load process:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx

Note that with above query, you can create permanent table too. If you create a temporary table then you can only use BULK INSERT statement since the temporary table is connection scoped. And I am not sure if SSIS can perform SELECT...INTO and bulk load from the client side within context of single connection (You will have to ask in the SSIS forum).

|||

Good to know you can create Temps like this.

I might be able to work with this let me know what you think.

Problem:

I’m getting my data from an AS400 using

Insert Into @.TableName

Select *

from Opendatasource(“Connection Info”).AS400ServerName.AS400Library.@.TableName

The table name and column structure in the AS400 are the same as in my database but the column type is different i.e. A lot of the AS400 columns are Char when I need them to be Decimal. I need to create the temp table with the same structure as my table because the majority of the time my process fails is because the AS400 has Char data when it should be Decimal

My Solution:

Select Top 1 *

Into #t

From @.TableName

Truncate Table #t

--Insert temp data from sites

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.California.@.TableName

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.Nevada.@.TableName

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.Arizona.@.TableName

Truncate Table @.TableName

--Insert data to Production

Insert Into @.TableName

Select *

From #t