Triggers & Index Question 1

[LostCait]

I am using DTS with Enterprise Manager to copy a table that exists on our server. I only want to copy certain rows of the table into the new table, and I have a query that does that. I want the new table to have the same triggers, indexes, etc. attached to it that the original table has. When I do the copy, there are no triggers/indexes attached to the table when I look at it listing on Query Analyzer. How do I get them to copy with the table structure?

Thanks for your help!

 

[tlbroadbent]

When you use the DTS Import/Export Wizard, the 3rd screen offers three options.

Copy Table(s) and view(s) from the source database
Use a query to specify the data to transfer
Copy objects and data between SQL Server databases

You must choose the 3rd option, if you want SQL Server to create all the indexes, contraints, defaults, etc. If you choose one of the other options, SQL Server will create the destination tables but not any of the indexes, etc.

I usually prefer to script the table(s) with all constraints, defaults and indexes and use the script to create the new table. Then I'll either use T-SQL or DTS to transfer the data depending on the location and the size of the table(s).

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm