Insert into from temp table - creating too many rows

I am bulk importing CSV file into a temporary table and then inserting the rows into the target table if they do not already exists.

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 1st row is imported VECH01 DDD is not in the database.

OK

The 2nd row is imported but VECH01 DDD is already in the database, it was imported in the previous insert.

NOT OK

The 3rd row is not imported because VECH02 CCC already exists in the database.

OK

CREAT开发者_JAVA百科E 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

---

This is happening because the INSERT statement does not process the rows individually. There might be a better way to do this, but you can use the ROW_NUMBER function to insert only the first row 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 the ORDER BY depending on which AccessoryCodeAutoGeneral you want to insert if there are multiple rows.

---

In your sample CSV import data, the two DDD rows have different numeric values after them which would make them different rows. So VECH01|DDD should be inserted twice. Is that correct, or is the sample data incorrect?