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

Can anyone explain what the pre-execute phase is doing for OLE DB data sources using sql com

I have a project which spans multiple servers and aggregates literally billions of rows of data into a much smaller and manageable result set which I store on another server. There are two stored procedures which take up 99.9% of the processing time. Each one of these SPs are estimated to run over 3 hours a piece (gives you an indication of how much data there really is). With in this SSIS package, I have two control flows, one for each SP. When I run the SSIS package from VS2005, I can see that there is a pre-execute phase which takes about an hour abnd a half to complete, and then move on to executing. My question is, what the heck is this, and what is it doing? I know it validates the sql in the procedure, but does it actually run the SP during that pre-execute phase? If it does, is there any way to get around that?

Any help would be appreciated.

Have tried to open a SQL profiler to see what is going on on the DB server during that period?

Rafael Salas

|||Yes, I have, and I did not see anything that stood to me as a procedure call to that SP. I just want to know why it takes so long to pre-execute.