CODEWITHSUNDEEP
sqlsql-serverdatabase
Ctrl_Alt_Defeat
Ctrl_Alt_Defeat

Reputation: 4009

Copy data from SQL Server table to same table

I have a database called Customer which has a table called CustDetails. The data in the table is similar to below (I have removed some of it for brevity). ID is the PK.

CustDetails:

ID  CustNo  Year  StatusId
--------------------------
1   1231    2015    1
2   1232    2015    2
3   1233    2015    2
4   1234    2014    1
5   1235    2014    2

I have another database called Claim on the same server, which has a table called ClaimDetails (don't ask me why the tables aren't in the same DB but the design decision was made before I came on board and isn't something I can change.)

The data is similar to below (again some of it removed for brevity)

ClaimDetails:

ID  ClaimNumber CustNo
----------------------
1   1           1231
2   2           1232
3   3           1236
4   4           1237

I have a few things to accomplish:

  1. Copy all rows from CustDetails where year is 2015 and change the year to 2016
  2. If status in the result from part 1 is 2 set it to 1 for 2016
  3. Create a negative list from to find CustNo's which are in the ClaimDetails table but are not in the CustDetails table - for these create a new row in the CustDetails table and set the status to 3 and the year to 2016.

So for the data I have listed above the end result I want to achieve is:

CustDetails:

    ID  CustNo  Year  StatusId
    --------------------------
    1   1231    2015    1
    2   1232    2015    2
    3   1233    2015    2
    4   1234    2014    1
    5   1235    2014    2
    6   1231    2016    1
    7   1232    2016    1
    8   1233    2016    1
    9   1236    2016    3
    10  1237    2016    3

So far I have this query:

insert into CustDetails (CustNo, Year, StatusId)
    select 
        CustNo, 2016, StatusId
    from 
        CustDetails
    where 
        Year = 2015

This covers part 1. I don't have the logic in place for part 2 in the query above and I guess I could just do: 

UPDATE CustDetails 
SET StatusId = 1  
WHERE Year = 2016 AND StatusId = 2

Again I don't have the query done for part 3 but I am thinking I could get the CustNo's that are in ClaimDetails but not in CustDetails by doing something like:

SELECT CustNo 
FROM ClaimDetails
WHERE CustNo NOT IN (SELECT CustNo FROM CustDetails)

And then do a create on the IDs for each CustNo individually.

Question is this the best way to do this or should I do something like copy to a temp table first. And will the query for the 3rd part work ok even though they are two separate DB's - I guess if they are on the same server as long as I fully qualify the DB's it should work ok.

Upvotes: 2

Views: 1360

Answers (1)

DVJex
DVJex

Reputation: 314

Actually with your 2nd query, you will also be updating the old data which already existed before the execution of the 1st query. So I suggest using CASE WHEN like this:

INSERT INTO CustDetails (CustNo, Year, StatusId)
SELECT CustNo, 2016, CASE WHEN StatusId = 2 THEN 1 ELSE StatusId END
FROM CustDetails
WHERE Year = 2015

The 3rd query can also be done similarly using an INSERT INTO SELECT :

INSERT INTO CustDetails (CustNo, Year, StatusId)
SELECT CustNo, 2016, 3
FROM ClaimDetails
WHERE CustNo NOT IN (SELECT CustNo FROM CustDetails)

You will have to fully qualify the DBs including the schema name as DatabaseName.SchemaName.TableName for a multi database query.

Upvotes: 3

Related Questions