Script to INSERT/UPDATE the same records on different DBs

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  July 19, 2007 at 2:49 am

  #372870

  Hello comunity

  I have on one server 6 databases that represent 6 independent factories.

  I need to know an example of script for when i introduce one new article on 1 factory DB, this script verify if the reference article exists on the other 5 DBs. If they exist , then make an Update of the fields on articles table and if not exist they INSERT the same article on each 5 DBs.

  The field key to verify is the reference article for example : REF

  What the best solution without using replication and if someone could give me a script example, that´s was ok.

  Many thanks

  Luis Santos

• Fracn

  Old Hand

  Points: 389

  More actions

  July 19, 2007 at 3:34 am

  #720660

  Hi!

  You can use a cursor like this one:

  DECLARE @name varchar(100)

  DECLARE @Database varchar(100)

  DECLARE @query nvarchar(4000)

  DECLARE DB CURSOR FOR

  SELECT name

  FROM master.dbo.sysdatabases

  where name not in ('tempdb', 'master', 'model', 'msdb', 'Northwind')

  order by name

  OPEN DB

  FETCH NEXT FROM DB INTO @name

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SET @Database = @name

  SET @query =

  '

  -- Introduce your query

  '

  EXEC sp_executesql @query

  FETCH NEXT FROM DB INTO @name

  END

  CLOSE DB

  DEALLOCATE DB

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  July 19, 2007 at 7:32 am

  #720756

  Thanks for your reply

  I have just one question ,this script must be verify if the article reference exist in the others DB to know if they make an UPDATE or INSERT .

  How or where i can place my query to do that .

  Could you give my a very simple example based on your Cursor script

  Thanks

  Luis Santos

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  July 20, 2007 at 12:08 pm

  #721240

  Hello again

  Someone have an ideia regarding my last post, about where in this script i can place an INSERT or UPDATE Query depending if the article exit or not on the others databases.

  Thanks

  Luis Santos

   

• steve smith-401573

  SSChampion

  Points: 10889

  More actions

  July 20, 2007 at 12:51 pm

  #721257

  as posted in other questions, this question lends itself to a subsidiary CASE statement, where you control the logic based on the count of the records selected according to your insert criteria vs update criteria. If you have 3 key fields to uniquely identify the record, then either the results of the

  SELECT count(*) from tablex where cond1 = criteria1, cond2 = criteria2, cond3 = criteria3

  controls the logical flow. Crude, but it should work. Refinements can be found in other posts in the board.