How do I do an update/insert to certain fields in a certain order?

Question

How do I do an update/insert to certain fields in a certain order?

mohaminho10

Old Hand

Points: 323
More actions
January 29, 2010 at 12:25 pm

#216058

I have 5 Db's...DB1, DB2, DB3, DB4, and DB5. Now when an account is created in the front end, I want to be able to capture the account number into a particular table (TBL1) in DB1. So when one account is created in the front end, this is how i want the insert/update to happen in DB1...the table should look like the table below. Then after that is done in DB1, I also want it to do the same thing in the same table (TBL1) that also exists in all the other DB's. How can i put this logic together in SQL?
Table:
AccountNumber ActCenter Lead
12345 000 ABC
12345 000 DEF
12345 000 GHI
12345 001 ABC
12345 001 DEF
12345 001 GHI
12345 002 ABC
12345 002 DEF
12345 002 GHI
the "ActCenter" info would be the same for all the DB's, but the "Lead" info is DB specific. Meaning in DB2, we could have "leads" JKL, MNO, PQR. Hence the "Lead" is different for each DB.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Have you considered replication for this scenario?

Of course, since you are writing the application to perform the insert, you could also have it insert into the subsequent databases using the same logic you did for the first database.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mohaminho10 Old Hand Points: 323 More actions · Answer 2

That's a good idea...that is what I need assitance with, that logic for the first one. If i can nail that one down, I would then try your suggestion and see if that resolves the entire situation.

SMAAG Old Hand Points: 385 More actions · Answer 3

Have you considered using Service Broker to replay transactions in the other databases? Or, you could set up linked servers between each of the databases, and if your using a SP to make the update in DB1 it can then make the same updates in the other DB's too. However, linked servers can have performance issues and be a little unreliable in an enterprise. Service Broker is a better long term solution.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

If it's only 5 DB's, why not simply have a stored proc that takes the parameters and does 5 inserts using the 3 part naming convention?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mohaminho10 Old Hand Points: 323 More actions · Answer 5

It is actually going to update more that 5 DB's...the total runs to about 25 DB's.

Does anyone have a sample code that I can take a look at?

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Do you have your initial insert script written?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

mohaminho10 (2/1/2010)
It is actually going to update more that 5 DB's...the total runs to about 25 DB's.
Does anyone have a sample code that I can take a look at?
Thanks

Simple concatenation of code should do it. All you need is a table with the database you want it done to.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)