INSERT into Multiple Databases (SPROC)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

INSERT into Multiple Databases (SPROC)

Author

Topic

cinewest
Starting Member

8 Posts

Posted - 2007-09-30 : 00:58:45

Gentlepersons:

I am trying to perform the following process via a Stored Procedure or best recommend methodology:

Situation: I currently have 1 DB (ClientMaster) that will receive new records and / or updates to it. Once in this DB, I need to add/update to a seperate DB (DB2) that contains 5 tables. These 5 tables represent 2 of the tables that need to be added/updated to in 2 other DBs (DB3 and DB4) and the remaining 3 tables represent those in one final DB (DB5).

I currently perform these additions/updates via SQL Analyzer and Local Packages.

GOAL: My goal is to create a Stored Procedure (or proposed Method) that will enable me to grab any record that was added or updated to ClientMaster; post additions/updates to DB2 tables; and, ultimately, post these changes directly to DB3, DB4 and DB5, accordingly.

Preferrably, process will require me to only use one Stored Procedure.

NOTE: I currently use DB2 to allow me to transfer records from ClientMaster, flag these records as Exported=0 so that I may perform data cleaning (change telephone format for one table, create updates to records where certain conditions exist (i.e., if State=CA then Shipping=UPS), etc), use DB2 via the 5 tables to distribute to each intended DB (DB3-DB5), mark records as Exported=1. It should be noted that the tables in DB2 have been configured to match 1-1 those tables intended for DB3-DB5, whereas ClientMaster table/fields do not match any of the intended DBs schemas.

EXAMPLE OF INTENDED PROCESS FLOW (Please excuse any incorrect syntax, using only to aid in explaining my desired outcome):

1. FOR A NEW RECORD: I want to select the newly created record from ClientMaster

@AccountID = Unique Client Record

SELECT FIELD1, FIELD2, FIELD3
FROM CientMaster
WHERE AccountID=@AccountID

2. Once the fields are selected from ClientMaster, I will create records in respective 5 Tables of DB2

INSERT INTO DB2.TABLE1 FIELD1, FIELD2, FIELD3
VALUES 'X', 'Y', 'Z'

INSERT INTO DB2.TABLE2 FIELD1, FIELD2, FIELD3
VALUES 'X', 'Y', 'Z'

INSERT INTO DB2.TABLE3 FIELD1, FIELD2, FIELD3
VALUES 'X', 'Y', 'Z'

INSERT INTO DB2.TABLE4 FIELD1, FIELD2, FIELD3
VALUES 'X', 'Y', 'Z'

INSERT INTO DB2.TABLE5 FIELD1, FIELD2, FIELD3
VALUES 'X', 'Y', 'Z'

3. I will update created record (marked with Exported=0) with any necessary changes based on certain conditions (the following two are samples of some of the changes I do to 'clean and validate' the data prior to insert into live DBs (DB3-DB5)

UPDATE DB2.TABLE1
SET FIELD2='UPS'
WHERE FIELD3='CA' AND EXPORTED=0

UPDATE DB2.TABLE3
SET FIELD1=(replace(replace(replace(PHONE1,'(',''),')',''),'-',''))+'0000'
WHERE EXPORTED=0

4. Insert marked record (Export=0) from DB2.Table1 to DB3.Table1 and DB4.Table1, DB2.Table2 to DB3.Table2 and DB4.Table2, DB2.Table3 to DB5.Table3, DB2.Table4 to DB5.Table4, and DB2Table5 to DB5.Table5

Please note that I will need to handle updates similarly.

Thank you in advance for your assistance.

Kristen
Test

22859 Posts

Posted - 2007-09-30 : 05:17:34

So what problem have you encountered that you need help with pls?

cinewest
Starting Member

8 Posts

Posted - 2007-09-30 : 05:44:27

Sorry for not clarifying: I need help constructing the correct SPROC or an alternative recommended method. Thanks.

Kristen
Test

22859 Posts

Posted - 2007-09-30 : 05:52:07

If you have a SProc on ClientMaster, which also Inserts to other databases, then all processes that can change data must go through that Sproc.

The alternative is to use a Trigger on the table, and then ANY alteration to that table, HOWEVER caused, will also "propagate" the change the the other databases.

For the Sproc the sample dataflow you posted above looks OK.

Sorry, but I haven't got the time to write the code for you, unless you want to pay?!, but if you post your code here if you hit any problems I will try to help.

Kristen

cinewest
Starting Member

8 Posts

Posted - 2007-10-01 : 03:40:06

Thanks Kristen. I will advise if I run into snags.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources