script to copy missing data

• FTdenali

  SSCertifiable

  Points: 5362

  More actions

  July 20, 2012 at 7:27 am

  #276560

  SCENARIO,

  i have tbl1, tbl2,tbl3 in qa and prod.

  tbl1(id int identity(1,1),DomainName varchar(250),Daate datetime,results varchar(20))

  tbl2(id int identity(1,1),tbl1_id int,Desc varchar(max))

  tbl3(id int identity(1,1) ,tbl2_id int,comment varchar(max))

  At this point i assume we all understand the relationships between these 3 tables,

  tbl1 -<tbl2 ->tbl3

  I need to import data from QA to Prod where the date is between startdate and endDate.

  Can somebody help me with a script that can do this while carrying over the id value from tbl1 to be inserted to tbl2 field called tbl1_id and

  then inserting the required data from tbl2 QA to tbl2 PROD.

  however as the insertion to tbl2 happens the tbl2 id needs to be carried to tbl3 as a foreign key.

  how can i write this while loop or something?

• CapnHector

  SSCoach

  Points: 16143

  More actions

  July 20, 2012 at 7:33 am

  #1515517

  THE-FHA (7/20/2012)

  ---

  SCENARIO,

  i have tbl1, tbl2,tbl3 in qa and prod.

  tbl1(id int identity(1,1),DomainName varchar(250),Daate datetime,results varchar(20))

  tbl2(id int identity(1,1),tbl1_id int,Desc varchar(max))

  tbl3(id int identity(1,1) ,tbl2_id int,comment varchar(max))

  At this point i assume we all understand the relationships between these 3 tables,

  tbl1 -<tbl2 ->tbl3

  I need to import data from QA to Prod where the date is between startdate and endDate.

  Can somebody help me with a script that can do this while carrying over the id value from tbl1 to be inserted to tbl2 field called tbl1_id and

  then inserting the required data from tbl2 QA to tbl2 PROD.

  however as the insertion to tbl2 happens the tbl2 id needs to be carried to tbl3 as a foreign key.

  how can i write this while loop or something?

  Making assumptions is a bad thing around here. can you also post some sample data and the foreign keys between the tables? with out sample data its hard to work with things. since you said QA and Production im guessing the tables are the same in each??

  Here are the tables in a nice and readily consumable format. for the sample data please see the link in my signature for the best way to post the sample data.

  CREATE TABLE tbl1(

  id int identity(1,1),

  DomainName varchar(250),

  [Date] datetime,

  results varchar(20)

  )

  CREATE TABLE tbl2(

  id int identity(1,1),

  tbl1_id int,

  [Description] varchar(max)

  )

  CREATE TABLE tbl3(

  id int identity(1,1),

  tbl2_id int,

  comment varchar(max)

  )

  ---

  For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

  For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

  Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
  Jeff Moden's Cross tab and Pivots Part 1[/url]
  Jeff Moden's Cross tab and Pivots Part 2[/url]

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  July 22, 2012 at 3:49 pm

  #1516019

  CELKO (7/21/2012)

  ---

  No. There is no “carry” operator in SQL or RDBMS.

  Actually, there is. It's called OUTPUT. 😉

  --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)