Export tables in SQL Server 2005

In SQL Server 2000 you had DTS where you had the option to choose to export table structure along with data and also to choose s开发者_开发百科pecific tables only. Is there a similar option in SQé Server 2005 version.

I would like to export just 5 tables along structure and data from an existing database to another database. The data is required as it contains auto increment id fields that are maintained as foreign keys.

Use right mouse click on your database >> tasks >> generate scripts

After a few times 'next', you can select the tables from which you want to export the structure.

To export the data, you can:

• Right mouse click on your database >> tasks >> export data
• or copy/paste from/to table data view

Yes, of course - the DTS-equivalent in SQL Server 2005 and up is SQL Server Integration Services (SSIS).

You'll find that in your start menu under the SQL Server tab called Import and Export Data.

If you need to do this operation often, you might also want to look at the bcp (bulk copy) command line tool for doing this kind of thing. This allows you to wrap the operation into a batch file and execute it regularly or on demand.

SSMS has a facility to do this available from the object explorer (somewhat counter-intuitively this is not available within BIDS). This invokes a wizard that generates an SSIS package that can export one or more tables to another database.

Right click on the node in the SSMS object explorer, select Tasks->Export Data and this will invoke the export data wizard. The rest it pretty self-explanatory.