T-SQL creating tables (MS-SQL 2000)

[joshuaguilty]

DTS does not transfer constraint (primary/foreign keys ..), is there a way to use T-SQL to copy whole table, this includes format, structure and data together !?. I have a table to include all tables to be copied and I would loop through this table for all copying.
Thanks.

 

[mutley1]

What are you trying to achieve? The help may depend on the response. you could restore a backup of the DB / script out the tables then DTS the data over or.........just depends what you're trying to do.

M.

 

[joshuaguilty]

Thanks for the response. I am copying some selected tables from DB1 to DB2. Using DTS does not copy the constraints, therefore I would like to find a way to copy table structure and data at the same time using T-SQL SP. Please tell me a way I can accomplish this. So that the files to copy and to be copies is the same.

 

[gmmastros]

I think you should approach this in two steps.

Step 1: make the tables, indexes, constraints, triggers, etc...
Step 2: copy the data

To create the table, you can use Enterprise Manager or SQL Server Management Studio to script the table. When scripting, you can also have it script the dependent object too. Ultimately, this will give you TSQL code that you can run on your other database.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[joshuaguilty]

One more question. Is it possible to copy the table's contraints WITHOUT involving EM or the Studio, and just play under T-SQL !? Because I would like to plan to have a dynamic SP to copy tables (looping based on a table list). I am doing this to copy tables between databases, and the only thing missing is the constraints. Thanks again !

 

[mutley1]

Right click on the database in question and choose all tasks and then Generate SQL script, selecting the correct options from the tabs. this will give you a script to create the tables you want with keys / constraints etc. George may be able to help with looping through a table as I am not sure if that can be done, but the best option at the moment is to script out the tables then DTS the data over after. It is still not clear what you want to do......as in could you not just restore a backup to A N Other databse and drop tables you dont want after?

Rgds,

M.

 

[joshuaguilty]

Thank you again. First your suggestion works perfectly, besides I have to drop/remove those not needed. And my data is large and within the same database I have "companies" which means that a company having the same table structures but different name. That is why I try to have a dynamic SQL SP for selected tables only. Actualy I need two loops, outer for all the companies and inner is for the selected tables (from another table). I have the script written only and it is working and scheduled, the only thing is NO constraints exists on the copied tables.
Thanks.