0

I am making an application which optimizes routes (kind of like a VRP). The user must be able to copy all input data, and make changes to this data. This way, they are able to find out what the impact is on the optimization.

Therefore I chose to let them be able to copy all input data, and have sort of like a version management of the data.

I want to copy the following tables:

TableOne

ID     UUID     WEIGHT     CODE     
1      abc        15        AB
2      abd        5         AC

TableTwo

ID     UUID      SIZE      TABLE1_FK
1      abe        1           1
2      abf        3           2

The resulting tables (after copying):

TableOne

ID     UUID     WEIGHT     CODE     
1      abc        15        AB
2      abd        5         AC
3      abg        15        AB
4      abh        5         AC

TableTwo

ID     UUID      SIZE      TABLE1_FK
1      abe        1           1
2      abf        3           2
3      abi        1           1
4      abj        3           2

Up to this point, I can manage. But now when I want to update the foreign keys of TableTwo to point to the copied lines in TableOne, I get stuck.

I want to do something like:

UPDATE TableOne 
SET TABLE1_FK = 
(SELECT t.ID 
 FROM TableOne t 
 WHERE t.WEIGHT = (SELECT t.WEIGHT 
                   FROM TableOne t1 
                   WHERE ...)
)

And this is where I get stuck. The wanted result is:

TableTwo

ID     UUID      SIZE      TABLE1_FK
1      abe        1           1
2      abf        3           2
3      abi        1           3
4      abj        3           4

Any suggestions?

SanderDN
  • 496
  • 6
  • 19

1 Answers1

0

After you insert in the first table , select it's @@IDENTITY and use it to insert in the second table as foreign key.

gohar.gasparyan
  • 233
  • 3
  • 8
  • This won't work for me, since I copy the data of one table all at once: INSERT INTO TableOne (UUID, WEIGHT, CODE) SELECT NEWID(), t.WEIGHT, t.CODE FROM TableOne t And @@IDENTITY returns the last inserted identity: "If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated." – SanderDN Dec 24 '13 at 08:34
  • 1
    have a look at this,you can solve this keeping another table for ids http://stackoverflow.com/questions/8982934/can-sql-insert-using-select-return-multiple-identities – gohar.gasparyan Dec 24 '13 at 08:38