SQL Server – How to Insert and Update Bulk Data from One DB to Another

sql serversql-server-2008-r2

There are two DBs on different servers. I want to run a procedure once a day so that I can get updated records. If it already exists then it should update, else insert a new row.

So far I did it like this:

if not exists (Select emp_id  From DB1.dbo.Emp_Master where emp_code = (Select pk_emp_code From DB2)
    Begin
    INSERT INTO DB1.dbo.Emp_Master(emp_code,emp_name,emp_dept ,emp_desg ,emp_mob ,email_id,emp_dob,emp_doj)
    Select pk_emp_code,first_name+ '' +last_name,dept.dept_desc,
     desg.desig_desc,office_phone,email,REPLACE(CONVERT(NVARCHAR,CAST(dob AS DATETIME), 6), ' ', '-') ,
    REPLACE(CONVERT(NVARCHAR,CAST(doj AS DATETIME), 6), ' ', '-')
    From DB2
        Select 1
    END
    ELSE
    BEGIN


    UPDATE DB1.dbo.Emp_Master SET emp_name=first_name+ '' + last_name,emp_dept=dept.dept_desc,emp_desg=desg.desig_desc,
    email_id=email,emp_mob=office_phone,emp_dob=REPLACE(CONVERT(NVARCHAR,CAST(dob AS DATETIME), 6), ' ', '-'),
    emp_doj=REPLACE(CONVERT(NVARCHAR,CAST(doj AS DATETIME), 6), ' ', '-') 
    FROM [DB2]
    where emp_code =pk_emp_code
    Select 2
    END

I am getting error

SubQuery return more than one value

What's gone wrong?

Best Answer

It's this part that returns the error (inside your IF NOT EXISTS):

Select emp_id  From DB1.dbo.Emp_Master where emp_code = (Select pk_emp_code From DB2)

There is no correlation between the inner and outer query. A possible rewrite is along these lines:

Select emp_id  From DB1.dbo.Emp_Master where emp_code IN (Select pk_emp_code From DB2)

Moreover, the check you're doing at the very beginning of your code will skip inserting all the rows if any of them matches.

I suppose that what you want to do here is an upsert (if matched updated, if not matched insert). You can do it with the MERGE statement or with something like this:

IF OBJECT_ID('tempdb..#updatedRows') IS NOT NULL
    DROP TABLE #updatedRows;

CREATE TABLE #updatedRows (
    emp_code int PRIMARY KEY CLUSTERED
);

UPDATE DB1.dbo.Emp_Master 
SET emp_name = first_name+ '' + last_name,
    emp_dept = dept.dept_desc,
    emp_desg = desg.desig_desc,
    email_id = email,
    emp_mob = office_phone,
    emp_dob = REPLACE(CONVERT(NVARCHAR,CAST(dob AS DATETIME), 6), ' ', '-'),
    emp_doj = REPLACE(CONVERT(NVARCHAR,CAST(doj AS DATETIME), 6), ' ', '-') 
OUTPUT INSERTED.emp_code INTO #updatedRows
FROM [DB2]
WHERE emp_code = pk_emp_code;


INSERT INTO DB1.dbo.Emp_Master(
    emp_code,
    emp_name,
    emp_dept,
    emp_desg,
    emp_mob,
    email_id,
    emp_dob,
    emp_doj
)
SELECT 
    pk_emp_code,
    first_name + '' + last_name,
    dept.dept_desc,
    desg.desig_desc,
    office_phone,
    email,
    REPLACE(CONVERT(NVARCHAR,CAST(dob AS DATETIME), 6), ' ', '-') ,
    REPLACE(CONVERT(NVARCHAR,CAST(doj AS DATETIME), 6), ' ', '-')
FROM DB2
WHERE pk_emp_code NOT IN (
    SELECT emp_code
    FROM #updatedRows
);
Related Question