Insert from temporary table - creating too many rows

Question

I load the CSV file into a temporary table and then insert the rows into the target table if they do not already exist.

When I start the process, my table has the following values

VECH01 AAA 111
VECH01 BBB 222
VECH01 CCC 333
VECH02 AAA 111
VECH02 BBB 222
VECH02 CCC 333

When I import a CSV file with these values:

VECH01 | DDD | 444
VECH01 | DDD | 555
VECH02 | CCC | XXX

The first row is imported. VECH01 DDD is not in the database.
OK

The second line is imported, but the DDD file VECH01 is already in the database, it was imported into the previous insert.
NOT OK

The third row is not imported because VCC02 CCC already exists in the database.
OK

CREATE TABLE #csv 
(
 CarRedbookCode nvarchar(50) COLLATE Latin1_General_CI_AS,
 AccessoryCode nvarchar(50) COLLATE Latin1_General_CI_AS,
 AccessoryCodeAutoGeneral nvarchar(50) COLLATE Latin1_General_CI_AS
)

DECLARE @SqlStatement nvarchar(4000)
SET @SqlStatement =
'
BULK INSERT #csv
 FROM ''' + @FileName + '''
    WITH 
    ( 
        FIELDTERMINATOR = ''|'', 
        ROWTERMINATOR = ''\n'' 
    )
'
EXEC sp_executesql @SqlStatement

INSERT INTO  MapRedbookAccessory (CarRedbookCodeAccessoryCode, CarRedbookCode,     AccessoryCode, AccessoryCodeAutoGeneral) 
 select 
  src.CarRedbookCode + src.AccessoryCode
 , src.CarRedbookCode
 , src.AccessoryCode
 , src.AccessoryCodeAutoGeneral 
 from
  #csv src
 left join
  MapRedbookAccessory dst on dst.CarRedbookCodeAccessoryCode = src.CarRedbookCode + src.AccessoryCode
 where
  dst.CarRedbookCodeAccessoryCode is null

Answer 1

This is because the INSERT statement does not process the rows separately. This may be the best way to do this, but you can use the ROW_NUMBER function to insert only the first line for each code / accessory:

INSERT INTO  MapRedbookAccessory (CarRedbookCodeAccessoryCode, CarRedbookCode,     AccessoryCode, AccessoryCodeAutoGeneral) 
 select 
  src.CarRedbookCode + src.AccessoryCode
 , src.CarRedbookCode
 , src.AccessoryCode
 , src.AccessoryCodeAutoGeneral 
 from
 (select *, ROW_NUMBER() OVER (PARTITION BY CarRedbookCode, AccessoryCode ORDER BY AccessoryCodeAutoGeneral) AS row
    from #csv) src
 left join
  MapRedbookAccessory dst on dst.CarRedbookCodeAccessoryCode = src.CarRedbookCode + src.AccessoryCode
 where
  dst.CarRedbookCodeAccessoryCode is null
  and src.row = 1

You can change ORDER BYdepending on which AccessoryCodeAutoGeneral you want to insert if there are multiple rows.

Answer 2

CSV DDD , . VECH01 | DDD . ?