![]() ![]() Using SEQUENCES made it possible to UPDATE the values. Picture credits: © Can Stock Photo / Andreus Solution Sounds like a complicated plan, right? This is what I thought as well. I would need a complicated script to create a new table, use IDENTITY_INSERT to copy over the rows from the current table, then recreate all foreign keys that refer to the old table so that they refer to the new table, then drop foreign keys referring to the old table, then drop the old table, then rename the new table to the old name. In my case this was not a simple solution. Identity values of another system needed to be preserved, I know normally it is trivial if you should want this, but without going into detail, I can tell you this time it made sense.īut the problem with IDENTITY columns is that you can only assign a specific value during INSERT using the IDENTITY_INSERT option. ![]() I was aware of their existence but there was never a need to use them. Recently I used SEQUENCES instead of IDENTITY columns. guid mssql SQLServer TSQL uniqueidentifier © 2018 – Do not steal the contents – spread the link instead – thank you. ) same orders with orderdetails rows and with a different ) if you would rerun the script starting with ) Note that the order detail rows are copied using the SalesOrderGUIDĭECLARE NVARCHAR ( MAX ) = ERROR_MESSAGE ( ) For we simply use NEWID(), because it is not used somewhere else. a hash collision could occur (but the chance that itĬONVERT ( UNIQUEIDENTIFIER, HASHBYTES ( 'MD5', CONVERT ( CHAR (36 ), src. in a transaction and implement TRY CATCH to rollback Obviously it is important to run the entire operation below is not likely for a real life example: we select 3 salesorders from a specific CustomerID and copy them to a different CustomerID. PRINT Again I keep the example very simple, hypothetical almost because the action We keep it simple and just use a timestamp as a salt value.ĭECLARE VARCHAR (30 ) = CONVERT ( VARCHAR, GETDATE ( ), 120 ) guids will be different every time the script runs. copy the same source rows multiple times to new rows, because the generated First we declare a value to use for 'Salt'. ![]() ) Using a new predictable unique identifier this lookup is ) for the row, and then use it to insert the detail rows. ) header first, then retrieve the value for ) a new one, without having the dependency to insert the ) want to copy an existing SalesOrder and its detail rows to Demonstrate how it works given the example that you would ) Script: Generate new predictable GUIDs from existing ones - demo ![]()
0 Comments
Leave a Reply. |