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 = ''
)
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.
Replies