0

I have multiple environments for an application, like DEV, TEST, UAT, PROD.

I would need to copy some objects from the database created in UAT environment into PROD environment. The object is stored in the DB spreaded to multiple tables. Most of the tables have PK as IDENTITY (autogenerated). I don't have access to PROD db data (it is sensitive data in general).

What I need is to generate SQL script for inserting the object that does not preserve the values of Ids but uses the Ids assigned in target environment to related records.

Example: let's say object Order composed of [Order] and list of [OrderItem] rows. I would need to select one specific row in [Order] table, specify that also related rows from [OrderItem] should be included and generate script that would insert new row for [Order], get the value of assigned Order.Id, keep it in a variable and use it for inserting [OrderItem] rows. This is trivial example, my object is spreaded to many more tables, but the concept is the same.

Is there any tool for doing this? All scripting out utilities I tried preserve values of Identity columns.

Dale K
  • 25,246
  • 15
  • 42
  • 71
eXavier
  • 4,821
  • 4
  • 35
  • 57

1 Answers1

0

I think you would need to write custom code to achieve the need of first loading to parent table, followed by loading to child table based on SCOPE_IDENTITY() value.

Instead, if you don't have huge number of rows, I would suggest you to follow below steps:

  1. Load data from Production to another environment using SSIS package or another means
  2. Add the foreign key constraint in the child table to have UPDATE CASCADE
    ALTER TABLE ChildTable ADD CONSTRAINT FK_OrderDetail_Order FOREIGN KEY([OrderID])
    REFERENCES [dbo].[Order] ([OrderID])
    ON UPDATE CASCADE
  1. Now, Update the identity value to another value using some logic
    set identity_insert Order ON

    UPDATE Order SET OrderID = OrderID + 1000000 -- Have some other logic for random generation

    set identity_Insert Order OFF

Now, automatically the child table will get updated with new OrderID.

UPDATE This SO link talks about automating the code generation for INSERT scripts: What is the best way to auto-generate INSERT statements for a SQL Server table?

Also, I would suggest you to first script out parent tables, followed by child tables.

You can identify parent,child tables using the below script. You need to generate scripts now as per the parent, child depency. 

select object_name(parent_object_id) as childTable, object_name(referenced_object_id) as parentTable

from sys.foreign_keys

WHERE object_name(parent_object_id) IN 'Your comma separated list of tables'
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
  • Thanks, but there's no chance to get data from PROD. Even if I got it (it would have to go through anonymization, etc), the system is live and those IDs would be taken. Actually, the IDs do not matter - they don't need to be the same in PROD as in UAT, only the object should be moved from one env to another and remain consistent – eXavier Dec 06 '19 at 08:53
  • for UAT, from where you will get data ? From production only ? – Venkataraman R Dec 06 '19 at 08:56
  • for UAT, some data are generated, some are created by users - but only for testing the functionality. data in PROD are classified and are not exported for testing – eXavier Dec 06 '19 at 09:14
  • anyway we are drifting out from the original question.. the problem does not seem so difficult to me and I could write tool for that by myself but I don't think I'm the only one with such need and believe there already exists something.. – eXavier Dec 06 '19 at 09:17