Help!!! STORED PROC THAT DOES MULTIPLE INSERTS

• pespes009

  Mr or Mrs. 500

  Points: 574

  More actions

  October 12, 2011 at 5:01 am

  #258761

  Hello all,

  I need help in writing a stored proc that will insert into multiple servers i.e.qa/uat/int/para servers

  I would want to deploy new inserts on the dev serverand copy this change across all these qa/uat/int/para servers...all help is well appreciated

  Regards

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  October 12, 2011 at 6:15 am

  #1393364

  You can use my stored procedure code template from here[/url] and add the insert statements where you find "insert code here".

  Given that you have a linked server in place for each target database, you will have a code block resembling this:

  INSERT INTO SomeTable VALUES('SomeValue')

  INSERT INTO qa.DatabaseName.SchemaName.SomeTable VALUES('SomeValue')

  INSERT INTO uat.DatabaseName.SchemaName.SomeTable VALUES('SomeValue')

  INSERT INTO int.DatabaseName.SchemaName.SomeTable VALUES('SomeValue')

  INSERT INTO para.DatabaseName.SchemaName.SomeTable VALUES('SomeValue')

  The statements will run in a (auto promoted) distributed transaction.

  Hope this helps

  Gianluca

  -- Gianluca Sartori