插入的替代方法忽略为SQL Server

Question

i just switched over from a Mysql server to SQL server. But i just found out that INSERT INGORE INTO doesn't work with sql server. Original code:

INSERT IGNORE INTO DATA_EXACT_INVENTORY_LOCATIONS (ID, Code, Opslaglocatie, Omschrijving, OpVoorraad)
          VALUES ('$inventorylocationID','$inventorylocationsItemCode','$inventoryStorageLocationsCode','$inventorylocationsItemDescription','$inventorylocationsCurrenctStock')

I found out that i can use on duplicate key update, but the problem is that i have sql query's with upto 50 variables. So to use on duplicate key update would be alot of work. So what i was wondering is there a better alternative for INSERT IGNORE INTO that's is just plug and play so i don't have to write all variables again.

Answer 1

You can use not exists:

INSERT DATA_EXACT_INVENTORY_LOCATIONS (ID, Code, Opslaglocatie, Omschrijving, OpVoorraad)
    SELECT ID, Code, Opslaglocatie, Omschrijving, OpVoorraad
    FROM (VALUES ('$inventorylocationID', '$inventorylocationsItemCode', '$inventoryStorageLocationsCode', '$inventorylocationsItemDescription', '$inventorylocationsCurrenctStock')
         ) V(ID, Code, Opslaglocatie, Omschrijving, OpVoorraad)
    WHERE NOT EXISTS (SELECT 1
                      FROM DATA_EXACT_INVENTORY_LOCATIONS deil
                      WHERE deil.id = v.id -- or whatever column gets the duplicate key
                     );

Alternatively, you could rewrite the code to use MERGE. The SELECT should work in both databases.

Let me also add that you should learn to use parameters. Munging query strings with constant values exposes your code to SQL injection attacks and to hard-to-debug syntax errors.