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);
INSERT INTO Products  (Product_NAME, Product_DESCRIPTION, UPDATED_DATE, CREATED_DATE, CREATED_BY, UPDATED_BY)
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

2 comments

@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

29 Answered Questions

41 Answered Questions

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

17 Answered Questions

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

33 Answered Questions

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

7 Answered Questions

19 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

17 Answered Questions

[SOLVED] Select columns from result set of stored procedure

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

16 Answered Questions

[SOLVED] What is a stored procedure?

15 Answered Questions

[SOLVED] SQL Server - SELECT FROM stored procedure

Sponsored Content