CODEWITHSUNDEEP
sqlsql-server
Kiran Kumar
Kiran Kumar

Reputation: 3

Copy data from one table to another in sql server

Consider the below tables.

Table 1:
ID Name Address DateOfBirth DateOfJoin CurrentProject


Table 2:
ID Name DateOfJoin CurrentProject

How do i write a sql server db script to copy data from Table 2 to Table 1 such that, values of Table 2 should overwrite values of Table 1 while merging except for when Table 2 value is null. In case, the value of table 2 is null, the value of table 1 will take precedence.

Example in the above tables, the values of DataofJoin and CurrentProject should become values in the table 1 for a specific ID. When DateOfJoin and CurrentProject values are null in table 2, then table 1 value will remain as it is. Also, all the IDs that are present in Table 2 but not in Table 1 should be copied to Table 1 after running the script.

Upvotes: 0

Views: 88

Answers (1)

Vidyadhar
Vidyadhar

Reputation: 24

    BEGIN

    CREATE TABLE #Table1(
    ID INT, 
    Name VARCHAR(50), 
    Address VARCHAR(50),
    DateOfBirth DATE,
    DateOfJoin DATE,
    CurrentProject VARCHAR(50)
    )

    CREATE TABLE #Table2(
    ID INT, 
    Name VARCHAR(50), 
    DateOfBirth DATE,
    DateOfJoin DATE,
    CurrentProject VARCHAR(50)
    );

    INSERT INTO #Table1 VALUES
    (1,'NAME 1','ADDRESS 1','01/01/1990','01/01/2017','PROJECT 1'),
    (2,'NAME 1','ADDRESS 2','01/01/1991','01/01/2017','PROJECT 2'),
    (3,'NAME 1','ADDRESS 3','01/01/1992','01/01/2017','PROJECT 3'),
    (4,'NAME 1','ADDRESS 4','01/01/1993','01/01/2017','PROJECT 4');


    INSERT INTO #Table2 VALUES
    (1,'NAME 1','01/01/1990','01/01/1988',NULL),
    (3,'NAME 3','01/01/1991',NULL,'PROJECT 33'),
    (5,'NAME 5','01/01/1986','01/01/2017','PROJECT 5'),
    (6,'NAME 6','01/01/1985','01/01/2017','PROJECT 6');


    SELECT * FROM #Table1;
    SELECT * FROM #Table2;

    --  Insert records which exists in Table but not in table 1
    INSERT INTO #Table1(ID,Name,DateOfBirth,DateOfJoin,CurrentProject) SELECT * FROM #Table2 WHERE ID not in (SELECT ID FROM #table1) 

    -- Update matching id records from table 1 with table 2
    UPDATE #Table1 SET 
    Name = CASE WHEN T2.Name='' or T2.Name IS NULL THEN #Table1.Name ELSE T2.Name END, 
    DateOfBirth = CASE WHEN T2.DateOfBirth='' or T2.DateOfBirth IS NULL THEN #Table1.DateOfBirth ELSE T2.DateOfBirth END, 
    DateOfJoin = CASE WHEN T2.DateOfJoin='' or T2.DateOfJoin IS NULL THEN #Table1.DateOfJoin ELSE T2.DateOfJoin END, 
    CurrentProject = CASE WHEN T2.CurrentProject='' or T2.CurrentProject IS NULL THEN #Table1.CurrentProject ELSE T2.CurrentProject END
    FROM #Table2 T2 WHERE #Table1.ID= T2.ID 

    select * from #Table1

    drop table #Table1;
    drop table #Table2;


    END

Upvotes: 1

Related Questions