duanre1891
duanre1891
2018-12-04 11:25

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

已采纳

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • doufei8250 doufei8250 3年前

    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.

    点赞 评论 复制链接分享