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 structure intact, but only populating them with the rows that fit the WHERE criteria.

So if I had 5 joined tables as the source, my resulting destination tables would also be 5. But they'd only be sparsely populated with the data that passes that WHERE clause constraint.

Even more briefly, I have a database full of customer data, and I'd like to send a stand alone database to a single customer, with only his/her records p开发者_开发技巧opulated.

Some thoughts I had were to export the whole database, then delete all records where [criteria is not true] but I don't think the referential integrity of the database is such that all unwanted records would be purged.

Is there an easy or 'right' (aka SSIS) way to do this?

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.

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.