Export Tables (objects And Data) Based On Select Criteria

I have some data SELECT [field names] FROM [several joined tables] WHERE [some criteria is true] and I would like to export this data to another database, keeping the table struct

Solution 1:

Easy and simple way to do is.

Step 1. Create the tables you want in new database (2005/2008) Step 1 A. Right click on the table - Script Table As - Create To New Query Editor window. Now run this script on your new db.

Step 2. Export the data from old DB to your New DB based on your criteria. You can do all the steps if you utilize BIDS SSIS.

Solution 2:

You can use select into to copy rows to a newly created table:

select  col1, col2, ...
into    DestinationTable
from    SourceServer.SourceDb.dbo.SourceTable
where   col1 = 'A'and ...

The four part name assumes you're using multiple SQL Servers, and that you have a linked server called SourceServer. If you're using two databases on the same server, just remove the server part of the name.