2

I am currently using an SQL job to import the contents of a CSV file (BULK INSERT). I use a LEFT JOIN statement to check if there are duplicates and to not insert them if any are found. However, I would like to instead UPDATE a column if their are duplicates.
Here are the fileds of my CSV file:
|UserType|Lname|Fname|Mname|ContactNo|Email|Password|Balance|Status

Here is my LEFT JOIN statement.

INSERT INTO tblAccount
SELECT CSVTable.AccountID,
CSVTable.UserType,
CSVTable.Lname,
CSVTable.Fname,
CSVTable.Mname,
CSVTable.ContactNo,
CSVTable.Email,
CSVTable.Password,
CSVTable.EPurseBalance,
CSVTable.AccountStatus
FROM CSVTable
       LEFT JOIN tblAccount 
         ON CSVTable.AccountID = tblAccount.AccountID
WHERE tblAccount.AccountID  IS NULL



I would like to UPDATE existing records if duplicates are found. Let's say a user's AccountStatus. If a user is already on the Accounts table BUT his account was deactivated, I would like to update not the entire row but only the AccountStatus column.

My current statement discards duplicate entries regardless if some columns are different. How can I insert if data is non existent but update a column if its already existing?

Any efforts to answer my question will be greatly appreciated!

EDIT I tried the MERGE command. Here is my statement:

MERGE 
   tblAccount AS target
USING 
   CSVTable AS source
ON 
   target.AccountID = source.AccountID 
WHEN MATCHED THEN 
INSERT INTO tblAccount
SELECT CSVTable.AccountID,
        CSVTable.UserType,
        CSVTable.Lname,
        CSVTable.Fname,
        CSVTable.Mname,
        CSVTable.Address,
        CSVTable.Gender,
        CSVTable.Birthdate,
        CSVTable.ContactNo,
        CSVTable.Email,
        CSVTable.Password,
        CSVTable.EPurseBalance,
        CSVTable.AccountStatus
FROM CSVTable
       LEFT JOIN tblAccount 
         ON CSVTable.AccountID = tblAccount.AccountID
WHERE tblAccount.AccountID  IS NULL



WHEN NOT MATCHED THEN 
   UPDATE SET tblAccount.AccountStatus = CSVTable.AccountStatus
; 



This doesn't work and SQL server throws me errors:

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'INTO'.
Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'WHEN'.


I tried inserting my LEFT JOIN statement in my MERGE but to no avail. Any ideas? Thanks!

3
  • Have you checked Merge instruction? Commented Feb 22, 2014 at 4:10
  • I'm using SQL Server 2008. Yes, I tried the merge command but I get errors. Commented Feb 22, 2014 at 4:36
  • Can you post what you've tried so far? Commented Feb 22, 2014 at 4:38

1 Answer 1

1

I think below link will help you

http://technet.microsoft.com/en-us/library/bb522522%28v=sql.105%29.aspx

Sign up to request clarification or add additional context in comments.

1 Comment

This answer will become useless if that link ever goes dead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.