SQL Update or Insert from another table

I am trying to bulk insert a csv file into a SQL table. However, if the record already exists i want it to update, not add another one, and if it doesnt exist then i want it to insert.

I have so far got it to bulk insert into a table called TAPELOAD.

Then i want it to update current records in the table TAPES, or insert if it doesnt exist. This is where i am having trouble.

Here is my current code.

Code Snippet:

      
        
          1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

        
        
          BULK INSERT TapeLoad 
FROM 'C:\\Tapes\\TAPES.txt' 
WITH( FIELDTERMINATOR = ',', 
ROWTERMINATOR = '\n' )

IF (EXISTS (SELECT * FROM Tapes 
  WHERE tape_code = tapeload.dbo.tape_code))
begin
  UPDATE Tapes
  SET tape_code = tapeload.dbo.tape_code, location = tapeload.dbo.location, date = tapeload.dbo.date
  WHERE tape_code = tapeload.dbo.tape_code
end
else
begin
INSERT INTO tapes(tape_code,location,date) SELECT (select tape_code from tapeload where ),(select location from tapeload), (select date from tapeload)
end

        
      

Toggle Highlighting

Open in New Window

Select All

Solution: SQL Update or Insert from another table

BULK INSERT TapeLoad
FROM 'C:\\Tapes\\TAPES.txt'
WITH( FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n' )

---------------1st Method Start----------------------------
-----For Updating the Values-------
Declare @start int,
                        @TapeCode as nVarchar(50),
                        @Location as nVarchar(50),
                        @Date as Datetime,
                        @Row as int
Set @start=@min
Set @TapeCode=''
Set @Location=''
Set @Date =''
Set @Row =0

Select @min=Min(RowNumber),@max=Max(RowNumber) from (Select tape_code,location,date,Row_Number() OVER(ORDER BY tape_code Asc) as 'RowNumber' from TapeLoad where tape_code in (Select tape_code from Tapes ))t

      While (@start<=@max)
                  Begin

                                          Select @TapeCode=tape_code,@Location=location,@Date=date,@Row=RowNumber from (Select tape_code,location,date,Row_Number() OVER(ORDER BY tape_code Asc) as 'RowNumber' from TapeLoad where tape_code in (Select tape_code from Tapes ))t
                                          Where t.RowNumber=@start

                                          Update Tapes Set Location=@location,Date=@date Where @TapeCode=tape_code;

                                          Set @Start=@Start+1;

                  End

-----For inserting the Values-------
Insert into Tapes (tape_code,location,date)
Select tape_code,location,date from TapeLoad where tape_code not in (Select tape_code from Tapes )

---------------1st Method End----------------------------

---------------2nd Method Start----------------------------

Delete from Tapes Where tape_code in (Select tape_code from TapeLoad );

Insert into Tapes (tape_code,location,date)
Select tape_code,location,date from TapeLoad where tape_code not in (Select tape_code from Tapes )

---------------2nd Method End----------------------------