x

how to auto create a table in one schema from another and load data?

Thanks for the response. My structure changes every time a new file comes in. Here is what I am trying to do. I used the following script to create a staging table every time a new file comes into a folder.

http://sqlage.blogspot.com/2011/02/create-table-for-each-flate-file-and.html

I updated the script to pass the file name to a variable along with the schema name.

for eg:

1)I have a new csv file called employee. (*completed*)

2)The above script will get the file from the specified location and create a table called staging.employee in staging schema and also will pass a value to the variable called TableName : dw.employee. (*completed*)

3)Now I need to create a table called employee in dw schema if it does not exist based on staging table we just created and insert data, and if table exist just insert the data. (*no clue*)

I don't know how to proceed further.
more ▼

asked Aug 23, 2012 at 07:32 PM in Default

hope gravatar image

hope
50 4 4 5

Please share something more about what you are trying to achieve than a diffuse title.
Aug 23, 2012 at 08:06 PM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

As Magnus said, more detail would help a great deal.

If you just want a quick way to use one table as a general template for another, you could use something like

select * 
into table2
from dbo.temp
where 1 = 0

I would not recommend ever using something like that in production. But it occasionally comes in handy for quickly generating a table to test things with.

You can also use SSMS to script out the table structure and then change the name of the table before you run the script.
more ▼

answered Aug 23, 2012 at 10:47 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

Thanks for the response. My structure changes every time a new file comes in. Here is what I am trying to do. I used the following script to create a staging table every time a new file comes into a folder.

http://sqlage.blogspot.com/2011/02/create-table-for-each-flate-file-and.html

I updated the script to pass the file name to a variable along with the schema name.

for eg:

1)I have a new csv file called employee.

2)The above script will get the file from the specified location and create a table called staging.employee in staging schema and also will pass a value to the variable called TableName : dw.employee.

3)Now I need to create a table called employee in dw schema if it does not exist based on staging table we just created.

I don't know how to proceed further.
more ▼

answered Aug 24, 2012 at 05:22 PM

hope gravatar image

hope
50 4 4 5

(comments are locked)
10|1200 characters needed characters left

Hi, you can use a subquery to insert data from one table within or from another schema into the new table. This can be done using the "as select..." clause. e.g

create table employee as select * from staging.employee;

If you require only certain columns from the referenced table, you will list the columns in a bracket() in lieu of '*'.

I hope I clearly understood your question as you find this helpful. Cheers
more ▼

answered Apr 05, 2013 at 04:17 AM

Adigwe gravatar image

Adigwe
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x84
x14

asked: Aug 23, 2012 at 07:32 PM

Seen: 1433 times

Last Updated: Apr 05, 2013 at 04:17 AM