I've tried to find this info via Google and Tech-tips searches to no avail.
I've got a multi-client SQL Server 2000 database. I've got a client identifier (partial or full primary key to some tables, column value in all) and I've got RI set up through the DB.
I want to pull out ONE client from the database and create its own database of duplicate info.
IDEAL OPTION:
I'm looking for a way to make use of RI via DTS to do this without having to specify each table and the client code on each table, but it does not look like I can. In other words, I'd like to be able to specify the client code, and then have DTS follow it through my RI to pull just that client's data from all RI related tables and post that client's data into a new DB in identical tables. Does that make sense?
TABLE BY TABLE OPTION:
The best I've found so far is to set up a global variable to pass the client code and then individual tasks to pull just one client for EACH table, specifying the tables one at a time. Then putting it all together in a multi-stepped package (at least one step per table). I have not coded this yet, so I'm stating this approach without precision, but I'm pretty sure this will work, even so, it seems like there ought to be a better way...
Anyone know if there is a way to do my "IDEAL OPTION" approach?
Please advise, thanks!
I've got a multi-client SQL Server 2000 database. I've got a client identifier (partial or full primary key to some tables, column value in all) and I've got RI set up through the DB.
I want to pull out ONE client from the database and create its own database of duplicate info.
IDEAL OPTION:
I'm looking for a way to make use of RI via DTS to do this without having to specify each table and the client code on each table, but it does not look like I can. In other words, I'd like to be able to specify the client code, and then have DTS follow it through my RI to pull just that client's data from all RI related tables and post that client's data into a new DB in identical tables. Does that make sense?
TABLE BY TABLE OPTION:
The best I've found so far is to set up a global variable to pass the client code and then individual tasks to pull just one client for EACH table, specifying the tables one at a time. Then putting it all together in a multi-stepped package (at least one step per table). I have not coded this yet, so I'm stating this approach without precision, but I'm pretty sure this will work, even so, it seems like there ought to be a better way...
Anyone know if there is a way to do my "IDEAL OPTION" approach?
Please advise, thanks!