1

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 populated.

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?

LesterDove
  • 3,014
  • 1
  • 23
  • 24
  • Is the desire to export the stuff defined in the original query criteria to another database (flattened data structure) or was that just to indicate there is some non-trivial criteria for determining what data elements belong to a customer but the end result needs to be a copy of the database with only customer A's data populated? – billinkc Aug 30 '11 at 20:03

2 Answers2

1

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Thanks, but INTO would dump all my fields into a single destination table. What I need is for the destination tables to mirror the source tables. Say, 5 joined tables --> 5 destination tables of equivalent schema. Hope I'm clear. – LesterDove Aug 30 '11 at 19:56
  • @LesterDove: You can't create, insert or update more than one table in a single SQL statement. So you'd need 5 `select into` statements. – Andomar Aug 30 '11 at 20:01
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.

Vijay
  • 76
  • 1