Copying data from multiple related tables

• Flodpanter

  SSC Enthusiast

  Points: 175

  More actions

  November 22, 2010 at 2:18 am

  #231433

  Im wondering if there is a better way of copying data than the way Im thinking of doing in this case:

  I want to be able to make a copy of some of the data in my DB. My DB consists of many tables, all with identity columns that are also used for relationships between the tables.

  One of the tables called "Project" defines the entry point for the copy. I want a specific record in the "Project" table to be copied. At the same time all related records of the referencing tables must be copied as well to make the new project instance valid. Of course, the copy is not exact as all new records of each table must have their own unique identity value.

  Some of the tables have about 100 records that relate to the project instance while others just hold 1.

  In my world, the only option is to use CURSORS to make this work.

  Any suggestions/thoughts are appreciated.

• WO

  Old Hand

  Points: 339

  More actions

  November 25, 2010 at 3:50 pm

  #1255656

  You don't need a cursor to make this work, if you copy the project (insert into project (columns except identity column) select (columns except identity column) from project where projectID = @val, then you can use a function to determine the last identity value that was inserted, either in the table or the last identity value that was inserted in your session. In a multi user environment i would use the latter one, i think it is scope_identity() but you can find it in books online.

  You can use this identity value to perform similar inserts in the related tables. This way you can insert the 100 records in table x related to the project at once, instead of row by row.

  Note, if there are insert triggers defined on the project table that perform inserts that also consume a identity value this might not work since the scope_identity function probably gives back the incorrect identity value.

  Good luck

  Willem

  Willem
  

  ---

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  November 25, 2010 at 7:53 pm

  #1255667

  WO (11/25/2010)

  ---

  You don't need a cursor to make this work, if you copy the project (insert into project (columns except identity column) select (columns except identity column) from project where projectID = @val, then you can use a function to determine the last identity value that was inserted, either in the table or the last identity value that was inserted in your session. In a multi user environment i would use the latter one, i think it is scope_identity() but you can find it in books online.

  You can use this identity value to perform similar inserts in the related tables. This way you can insert the 100 records in table x related to the project at once, instead of row by row.

  Note, if there are insert triggers defined on the project table that perform inserts that also consume a identity value this might not work since the scope_identity function probably gives back the incorrect identity value.

  Good luck

  Willem

  The SCOPE_IDENTITY function was specifically designed to work around the problem of triggers and will return the correct value even in the presence of triggers. Take a look at Books Online.

  It's @@IDENTITY that has a problem in the presence of triggers. That, too, is in Books Online.

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

• WO

  Old Hand

  Points: 339

  More actions

  November 26, 2010 at 1:11 am

  #1255725

  Jeff, Thank you for correcting me, while posting my reply I suddenly remembered having this problem with identity values a while back so I thought it be worth mentioning. I probably used @@IDENTITY then and later changed it into scope_identity.

  Willem
  

  ---

• Flodpanter

  SSC Enthusiast

  Points: 175

  More actions

  November 26, 2010 at 1:27 am

  #1255731

  WO (11/25/2010)

  ---

  You don't need a cursor to make this work, if you copy the project (insert into project (columns except identity column) select (columns except identity column) from project where projectID = @val, then you can use a function to determine the last identity value that was inserted, either in the table or the last identity value that was inserted in your session. In a multi user environment i would use the latter one, i think it is scope_identity() but you can find it in books online.

  You can use this identity value to perform similar inserts in the related tables. This way you can insert the 100 records in table x related to the project at once, instead of row by row.

  Note, if there are insert triggers defined on the project table that perform inserts that also consume a identity value this might not work since the scope_identity function probably gives back the incorrect identity value.

  Good luck

  Willem

  Hi Willem,

  I am aware of the Scope_Identity / @@identity values. However these come up short after this Insert:

  Insert into dbo.table_referencing_project select a,b,c from dbo.table_referencing_project where projectId=@projectId

  This Insert will create 100 new records all with unique identity values in the table dbo.table_referencing_project. Now I have to copy all data that references the dbo.table_referencing_project table. These I cannot find, since I inserted 100 records and the Scope_Identity contains the last inserted value only.

• WO

  Old Hand

  Points: 339

  More actions

  November 26, 2010 at 3:27 am

  #1255765

  OK, suppose table X has an FK to table project, and table Y has a FK to table X

  First you copy project, and retrieve the identity value of the new project

  Then you copy the records in table X that are related to the project to be copied, using the identity value retrieved with scope_identity.

  With this identity value you can also create an insert statement for table Y, you just have to join table X in your select statement that you use for the insert

  So:

  insert into Y (columns of Y minus identity)

  select (columns of Y minus identity)

  from Y

  join X on (FK columns Y to X)

  where X.projectId = (new project ID identity)

  This can be done for every indirectly related table but you need more joins when you move "further away" from the project table

  Or am I missing something maybe?

  Willem
  

  ---

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  November 26, 2010 at 3:46 am

  #1255768

  Flodpanter (11/26/2010)

  ---

  This Insert will create 100 new records all with unique identity values in the table dbo.table_referencing_project. Now I have to copy all data that references the dbo.table_referencing_project table. These I cannot find, since I inserted 100 records and the Scope_Identity contains the last inserted value only.

  You can use the OUTPUT clause in your INSERT statement to return the 100 new identity values.

  John

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  November 26, 2010 at 8:14 am

  #1255826

  WO (11/26/2010)

  ---

  Jeff, Thank you for correcting me, while posting my reply I suddenly remembered having this problem with identity values a while back so I thought it be worth mentioning. I probably used @@IDENTITY then and later changed it into scope_identity.

  Thanks for the feedback and for taking my post the right way.

  Yeah... I agree. @@IDENTITY can really cause some problems in the presence of a trigger and I've not found a need (yet) to get the last value produced by a trigger especially with the advent of 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)
  

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  November 26, 2010 at 8:18 am

  #1255828

  Flodpanter (11/26/2010)

  ---

  This Insert will create 100 new records all with unique identity values in the table dbo.table_referencing_project. Now I have to copy all data that references the dbo.table_referencing_project table. These I cannot find, since I inserted 100 records and the Scope_Identity contains the last inserted value only.

  I agree with what John Mitchell wrote a couple of posts back... the OUTPUT clause of an INSERT can be directed to a "capture" table which will remember all of the ID's that were created during a multi-row insert. Look it up in Books Online. It's not something that people use a lot but it sure does make life easier than writing a trigger that captures the values from the "INSERTED" trigger table. OUTPUT can do the same thing but without the fuss or overhead of a trigger.

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

• Flodpanter

  SSC Enthusiast

  Points: 175

  More actions

  November 27, 2010 at 2:06 pm

  #1255984

  Thanks for the replies guys. It was this kind of discussion I was hoping for when posting. I have looked at the OUTPUT command and found it quite useful. Although MSDN states that it is not possible to retrieve identity values in this way (thats how I understand the documentation as seen below), I modified their example a bit and it worked.

  FROM MSDN: http://msdn.microsoft.com/en-us/library/ms177564.aspx

  ...The following example creates the EmployeeSales table and then inserts several rows into it using an INSERT statement with a SELECT statement to retrieve data from source tables. The EmployeeSales table contains an identity column (EmployeeID) and a computed column (ProjectedSales). Because these values are generated by the SQL Server Database Engine during the insert operation, neither of these columns can be defined in @MyTableVar....

  WORKING EXAMPLE:

  IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL

  DROP TABLE dbo.EmployeeSales;

  GO

  CREATE TABLE dbo.EmployeeSales

  ( EmployeeID int IDENTITY (1,5)NOT NULL,

  LastName nvarchar(20) NOT NULL,

  FirstName nvarchar(20) NOT NULL,

  CurrentSales money NOT NULL,

  ProjectedSales AS CurrentSales * 1.10

  );

  GO

  DECLARE @MyTableVar table(

  EmployeeID INT NOT NULL,

  LastName nvarchar(20) NOT NULL,

  FirstName nvarchar(20) NOT NULL,

  CurrentSales money NOT NULL

  );

  INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)

  OUTPUT INSERTED.EmployeeID,

  INSERTED.LastName,

  INSERTED.FirstName,

  INSERTED.CurrentSales

  INTO @MyTableVar

  SELECT 'Peter', 'Parker',2

  SELECT EmployeeID,LastName, FirstName, CurrentSales

  FROM @MyTableVar;

  GO

  SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales

  FROM dbo.EmployeeSales;

  GO

  Thanks for all the help.