Update existing record else insert record if no record exists

My tables are:

CREATE TABLE USERS (
users_id NVARCHAR(10) PRIMARY KEY,
name NVARCHAR(10),
surname NVARCHAR(10),
username NVARCHAR(10)
)

CREATE TABLE USERINFO (
id NVARCHAR(10) PRIMARY KEY,
users_id NVARCHAR(10),
data NVARCHAR(10)
)

ALTER TABLE USERINFO
ADD FOREIGN KEY (users_id)
REFERENCES users (id)

I have a .csv file with data that needs to be added to the USERINFO
table. It has 5 columns (status, statusreason, statusdate, data,
externalusername) and I first import the data into a temporary table
in the database this way:

IF OBJECT_ID('#ImportIDs') IS NOT NULL
DROP TABLE #ImportIDs
GO
CREATE TABLE #ImportIDs
(
status NVARCHAR(50),
statusreason NVARCHAR(50),
statusdate NVARCHAR(50),
partyid NVARCHAR(50),
externalusername NVARCHAR(50)
)
GO

BULK INSERT #ImportIDs
FROM 'C:\temp\datafile.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

In order to get the value of the 'partyid' column in the temporary
table into the 'data' column on the userinfo table I do an INSERT like
this:

INSERT INTO userinfo (id,userid,data)
SELECT users.id, userinfo.data, #ImportIDs.partyid
FROM users
INNER JOIN userinfo ON users.id = userinfo.userid
INNER JOIN #ImportIDs ON users.username = #ImportIDs.externalusername

But how do I first check if a record already exists in the userinfo
table and UPDATE it instead of doing an insert, which would create
"duplicate" records in userinfo?

Thanks.

Andyza (andyzaatwebmaildotco.za) writes:
> In order to get the value of the 'partyid' column in the temporary
> table into the 'data' column on the userinfo table I do an INSERT like
> this:
>
> INSERT INTO userinfo (id,userid,data)
> SELECT users.id, userinfo.data, #ImportIDs.partyid
> FROM users
> INNER JOIN userinfo ON users.id = userinfo.userid
> INNER JOIN #ImportIDs ON users.username = #ImportIDs.externalusername

That query does not match the table schema you posted. There is no
column id in the users table. Nor is there a column userid in userinfo.

It also look strange userinfo appears in the source for the INSERT
statement. I don't anything about the business rules, but this looks
funny.

It's difficult to answer a question when I'm not sure what your real
query is. (The above query does not compile, so that is not the guy.)

> But how do I first check if a record already exists in the userinfo
> table and UPDATE it instead of doing an insert, which would create
> "duplicate" records in userinfo?

Which version of SQL Server you are you on? If you are on SQL 2008,
you can do it all in one statement with MERGE. Here is an example:

MERGE USERINFO trg
USING (SELECT U.users_id, UI.data, t.partyid
FROM USERS U
JOIN USERINFO UI ON UI.id = U.users_id
JOIN #ImportIDs t ON U.username = t.externalusername) AS src
ON trg.users_id = src.users_id
WHEN MATCHED THEN
UPDATE SET data = src.partyid
WHEN NOT MATCHED BY TARGET THEN
INSERT (id, users_id, data)
VALUES(src.users_id, src.data, src.partyid)
;

Although given the caveats above, I'm not sure whether this is correct.
I had to guess about the table names.

On SQL 2005 you need to use UPDATE and INSERT WHERE NOT EXISTS.

--
Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Update existing record else insert record if no record exists

Update existing record else insert record if no record exists

Re: Update existing record else insert record if no record exists