By Chatra

2019-07-29 15:21:14 8 Comments

I have some rules set up in Dev and working fine. Now, I want to copy the same rules to QA. There are 4 tables involved and the table structure is the same in dev and QA. I just want to move data from dev to QA.

If anything breaks, I would like revert completely.

I have

  1. Products table
  2. Products Group Table (Product_Id FK from products table)
  3. Products SubGroup Table (Group_Id FK from Products Group table)
  4. Properties Table (Subgroup_Id FK from Products SubGroup table)

What is the efficient way of writing a stored procedure to do this?

I tried below and because of cross join, it is dupicating records.

DECLARE @TempProducts  TABLE (ProductId INT);
OUTPUT INSERTED.product_ID INTO @TempProducts  
SELECT Product_NAME, Product_DESCRIPTION, getDate(), getDate(), UPDATED_BY, CREATED_BY,  FROM [dbo].[Products] P
JOIN Product_GROUP PG ON P.Product_ID = PG.Product_Id
CROSS JOIN @TempProducts  WHERE PG.Product_ID = 2


@VAT 2019-08-14 15:27:52

We have rather created a .Net utility to copy data from one environment to other.

This has helped us to cater to many possible scenarios. I am listing down the methods that we have used in our utility:

  • Create CSV Method
  • Create Headers Method
  • Create Rows Method
  • Log Exceptions Method (very important)
  • Class Structure for every import you are planning
  • Class Structure for input params in your CSV
  • Class for calling any Triggers
  • Class for Rollback any changes
  • Class for Mapping columns (you may use Auto-Mapper)
  • Class for writing data to any table
  • Class for reading data from any table

If you can provide details about your schema, I can suggest more.

@PeterHe 2019-08-13 20:18:32

You need to have a database deployment package, mostly just a sql file. It should contain all the table/view ddls, seed data, functions stored procedures, triggers etc.

Your set of rules should become bunch of insert statements (insert if not exists, or update if exists, depends on your business requirement).

@Chatra 2019-08-14 12:29:23

I have only tables, no views, triggers etc. I am not worried about update, I just want to insert

@A. B. Duran 2019-07-29 16:33:47

Assuming dev and qa are on different servers, you need to make a linked server first.

To be able to make sure it reverts if it fails, I would invoke a transaction in your stored procedure and if it fails, you can rollback to what it was before the transaction started (that's a feature of transaction). The stored procedure I would imagine would just be an insert statement from one server to the other assuming the same tables exists in both. If you need this stored procedure to run multiple times a day, you can set a trigger on one of your tables that makes the stored procedure fire if something new is added or something like that.

Depending on how your pk's/fk's are made-- you can need to exclude them from the insert.

Related Questions

Sponsored Content

16 Answered Questions

[SOLVED] List of Stored Procedures/Functions Mysql Command Line

7 Answered Questions

19 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

40 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

27 Answered Questions

17 Answered Questions

[SOLVED] Select columns from result set of stored procedure

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

16 Answered Questions

[SOLVED] What is a stored procedure?

15 Answered Questions

[SOLVED] SQL Server - SELECT FROM stored procedure

Sponsored Content