Stored procedure copying from one to another

Question

I have a table called Sales and another SalesHistory. SalesHistory is the replica of Sales table.

Now the Sales table can be dropped anytime and recreated again with new columns being added and old columns being renamed to something different. I had written a stored procedure which copies data from the sales table to saleshistory table depending upon a condition if it needs insert or update

Now I am bit lost: how do I fix the issue that once sales table is dropped and recreated, how can I amend those changes to the saleshistory table?

Any idea or or same code, I can share my code off stored procedure if need but that is pretty simple

Here is the code

Insert into SalesHistory (Cusip, KeyFeatures1, KeyFeatures2, KeyFeatures3, KeyFeatures4, KeyFeatures5, KeyFeatures6, KeyFeatures7, KeyRisks1, KeyRisks2, KeyRisks3, Comments1, Comments2, Comments3)
    select 
        Cusip, KeyFeatures1, KeyFeatures2, KeyFeatures3, KeyFeatures4, 
        KeyFeatures5, KeyFeatures6, KeyFeatures7, KeyRisks1, KeyRisks2, 
        KeyRisks3, Comments1, Comments2, Comments3 
    from 
        Sales 
    where 
        not exists (SELECT 1 FROM SalesHistory WHERE cusip  = Sales.cusip)

UPDATE Hist 
SET Cusip  = A.Cusip,
    KeyFeatures1 = A.KeyFeatures1,
    KeyFeatures2 = A.KeyFeatures2,
    KeyFeatures3 = A.KeyFeatures3,
    KeyFeatures4 = A.KeyFeatures4,
    KeyFeatures5 = A.KeyFeatures5,
    KeyFeatures6 = A.KeyFeatures6,
    KeyFeatures7 = A.KeyFeatures7,
    KeyRisks1 = A.KeyRisks1,
    KeyRisks2 = A.KeyRisks2,
    KeyRisks3 = A.KeyRisks3,
    Comments1 = A.Comments1,
    Comments2 = A.Comments2,
    Comments3  = A.Comments3
FROM  
    SalesHistory Hist 
INNER JOIN 
    Sales A ON A.cusip  = Hist.cusip

I have already explained in my question what I am trying to do

Please share your code and exactly what you are having trouble with — Dijkgraaf, Oct 04 '16 at 03:04
For starters you need a list of columns that have changed names. Then you can use `sp_rename` as per this example: http://stackoverflow.com/questions/16296622/rename-column-sql-server-2008. How do you currently generate the script to make changes to the `Sales` table? — Nick.Mc, Oct 04 '16 at 03:15
So you're saying you change a column name in the `Sales` table, now your SP doesn't work because the column doesn't exist? There's no complex answer here - you just have to change your stored procedure and/or your `SalesHistory` table to allow for it. So whatever process you are currently using to capture and migrate changes to `Sales` needs to also include a change script for your stored procedure and/or `SalesHistory` table. You didn't mention whether or not your `SalesHistory` table is allowed to change. If not, what do you want to do about new columns added? Leave the values NULL? — Nick.Mc, Oct 04 '16 at 05:03
Can you please clarify the statement: "Now I am bit lost: how do I fix the issue that once sales table is dropped and recreated, how can I amend those changes to the saleshistory table?" Do you mean the data changes, or the schema column changes — Stefan Zvonar, Oct 04 '16 at 05:13

score 0 · Answer 1 · answered Oct 04 '16 at 05:25

Assuming you are after the data when the table is dropped ...?

Unfortunately you can not create an instead of trigger for DDL statements such as drop table. So you can't simply copy the data before the table is dropped. However, you could create an After Insert, Update trigger on the Sales table that inserts the record straight away to SalesHistory. That way, when the Sales table is randomly dropped, you will have the data already in the SalesHistory table.

Note: Please be careful with triggers as they can produce unwanted results depending on your application. Also, if you have no control over the schema of the Sales table, then you are going to find it difficult to copy the table data for all columns to the SalesHistory table and ensure that it works for the lifetime of the application.

However, if there is a predefined list of columns in Sales table that will never change, then you can perform what you are after and simply just copy those columns that never change.

forogt to mention one thing, when i drop a sales table, i stored names in salesmapping table too. so is that something we can fix — , Oct 04 '16 at 14:13

Stored procedure copying from one to another

1 Answers1