question

hope avatar image
hope asked

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.
tableautomation
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please share _something_ more about what you are trying to achieve than a diffuse title.
1 Like 1 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hope avatar image
hope answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Adigwe avatar image
Adigwe answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.