Copy data/tables based on script......

Last Post 11 May 2006 08:20 AM by nosepicker. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
SQL_Jr
New Member
New Member

--
10 May 2006 12:03 PM
Hi, I guess I'm in the right forum area, b/c I'd probably need DTS to do this (correct me if there's another way)

Basically, I have a system with over 500 tables and many of them have (0) zero rows.
Every so often, were required to do a data refresh, and its just a pain in the butt to select only the ones that have data.

I already have the script that yields only tablenames that DO NOT have zero records.

How can I tell DTS or SQL to copy only the data from this list of tables?

Any way? Please advise
SQL_Jr
New Member
New Member

--
11 May 2006 05:54 AM
No, not really.

I guess an easier way to put the question is, can I force the selection of tables to copy data via script using DTS. Does this make sense? tx
nosepicker
New Member
New Member

--
11 May 2006 06:47 AM
If you just want to copy out data, you can do this:

SELECT 'bcp yourdatabase..' + sysobjects.[name] + ' out c:\temp\' + sysobjects.[name] + '_data.txt -c -Uuserid -Ppassword -Sservername'
FROM sysobjects JOIN sysindexes ON sysobjects.id = sysindexes.id AND sysindexes.indid IN (0, 1)
WHERE sysindexes.rowcnt > 0
AND sysobjects.xtype = 'U'

This will create a bcp statement for every table with data in your database. Copy the resulting statements into a batch or command file and run it. Change the statement to bcp in to copy the data back to your destination.
SQL_Jr
New Member
New Member

--
11 May 2006 07:13 AM
hmm. very creative I will look at this, and see if I can integrate this into dts.
What about getting the data in?


I was trying to stay within the SQL DTS environment though (any ideas?)

Thx a bunch!
nosepicker
New Member
New Member

--
11 May 2006 08:20 AM
Just change OUT to IN:

SELECT 'bcp yourNEWdatabase..' + sysobjects.[name] + ' in c:\temp\' + sysobjects.[name] + '_data.txt -c -Uuserid -Ppassword -Sservername'
FROM sysobjects JOIN sysindexes ON sysobjects.id = sysindexes.id AND sysindexes.indid IN (0, 1)
WHERE sysindexes.rowcnt > 0
AND sysobjects.xtype = 'U'


Acceptable Use Policy
---