October 2, 2002 at 9:51 am
Heres is what I want to do: I have 20 identical databases for seperate clients. Data in a specific table in one of the databases (my database) is updated by me. I want to select all records from the table in my database, delete all records from the same table in all other databases, then append my data to all others. Make sense? Can I do this in the query analyzer? Any ideas?
(sql server 2000)
Thanks in advance
Edited by - mdriscoll on 10/02/2002 09:52:32 AM
October 2, 2002 at 9:58 am
You can't do it with one query.
I dirty and quick way to do it is to use a cursor. Build the cursor full of DB names. You could then use dynamic sql to build up your delete from and insert statements and execute them on the database.
This would repeat until all 20 databases have been updated.
Personally, if this was a regular job tho, I would create a DTS. You could run the DTS manually or schedule it to run whenever you need it to.
Clive Strong
October 2, 2002 at 10:00 am
Can I update multiple databases with 1 dts package? Do you know of any sites that might help guide me through making a package of this type (I new to this if you cant tell..:))
October 2, 2002 at 10:08 am
If you can do it in SQL, you can do it in DTS. Use hte Execute SQL task.
It will take multiple steps, but could be done in a stored proc, which can be run from DTS.
Steve Jones
October 2, 2002 at 10:14 am
Yes, you can call different DB's on one server, different Servers even within one DTS.
Feel free to ask anything on this forum. Lots of knowledge to be shared here.
If you want a dedicated DTS site, you might want to try SQLDTS.COM. Excellent site.
I will try and get you off on the right foot here..
1) Drag a SQL Connection onto your DTS Pane, open it and set the DB to your Source DB.
2) Drag another SQL Connection onto the Pane and set it to one of the Destination DB's
3) Drag a "Transfer Data Task" onto the pane. Set the Source and Destination connections by clicking on the relevent objects.
4) Double click on the link between the connections. In the Source and Destination tabs, set the relevent tables. Click on the Transformations tab and the links should be in place.
5) Click OK.
If you run this, the data should be copied to the Destination DB.
6) Repeat for 19 other DB's (Using same Source Connection for each DB).
You will also need to include an Execute SQL Task Object before any copying is done to Delete records or Truncate all the destination tables.
Hope that gives you a quick insight on how to get this done quickly!
Clive Strong
October 2, 2002 at 10:16 am
Or instead of multiple connections, you could create several Execute SQL Task Objects and step through doing each DB. (One for each DB to INSERT to).
Clive Strong
October 2, 2002 at 10:16 am
Clive, you rock!! thanks, I'll give it a try!
October 2, 2002 at 10:31 am
If all your user databases reside on a single server you could run something like this via QA. Kind of convoluted, but will work.
declare @cmd varchar(1000)
set @cmd = 'if ''?'' in (''db1'',''db2'',''db3'',''db4'')' + char(13) +
'begin' + char(13) +
'truncate table ?.dbo.tablex where name like ''usp_%''' + char(13) +
'insert into ?.dbo.tablex select * from yourdb.dbo.tablex' + char(13) +
'end'
exec master.dbo.sp_MSforeachdb @cmd
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply