SQL Server Skip insert (and continue) on duplicate key [T-SQL]

bsxbgnwa 于 2023-04-04 发布在其他

关注(0)|答案(4)|浏览(175)

I'm trying to get a working solution for a arguably pretty simple task:

I have a simple query like this:

INSERT INTO [INTRANET].[dbo].[SOME_STATUS]
   ([AUS_STATUS_VID]
  ,[AUS_STATUS_STATUS]
  ,[AUS_STATUS_JAHR]
  ,[AUS_STAUTS_END]
 )

SELECT DISTINCT CD_CLVID, '1', '2017', '0'
FROM [SNTRANET].[dbo].[SOME_DETAILS]
WHERE CD_JAHR = 2017

Long story short: Filling up a table with results from another table. Now it seems like, that I have inconsistent data given to me. I was trying to find a solution similar to replace into or on duplicate key update (which I was used to from MySQL). However, T-SQL doesn't seem to have this.

Is there an easy solution which just skips the dupe key and continues with the rest of the resultset?

I'm using SQLServer 2008 R2

Edit 1

As requested, the error-message:
Violation of PRIMARY KEY constraint 'PK_T_KB_AUSZAHLUNG_STATUS'. Cannot insert duplicate key in object 'dbo.T_KB_AUSZAHLUNG_STATUS'. The duplicate key value is (7463).

sql-server

来源：https://stackoverflow.com/questions/48263838/skip-insert-and-continue-on-duplicate-key-t-sql

4条答案

按热度按时间

y0u0uwnf1#

if the duplicate key is the [AUS_STATUS_VID] column then try using Alias on the tables and NOT EXISTS

INSERT INTO [INTRANET].[dbo].[SOME_STATUS]
   ([AUS_STATUS_VID]
  ,[AUS_STATUS_STATUS]
  ,[AUS_STATUS_JAHR]
  ,[AUS_STAUTS_END]
 )
SELECT DISTINCT S.CD_CLVID, '1', '2017', '0'
FROM [SNTRANET].[dbo].[SOME_DETAILS] S
WHERE S.CD_JAHR = 2017
AND NOT EXISTS
(
  SELECT SS.AUS_STATUS_VID FROM [INTRANET].[dbo].[SOME_STATUS] SS
  WHERE SS.AUS_STATUS_VID = S.CD_CLVID
) ;

赞(0）回复(0）举报 2023-04-04

hts6caw32#

use the ROW_NUMBER() to ensure you incoming result does not have duplicate and then the WHERE clause to ensure it does not exists in the destination table

SELECT col1, col2, col3, ...
FROM
(
    SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY PK_COL ORDER BY some_col)
    FROM   SOME_TABLE
) d
WHERE d.RN = 1
AND   NOT EXISTS
      (
           SELECT *
           FROM   DEST_TABLE x
           WHERE  x.PK_COL = d.PK_COL 
      )

赞(0）回复(0）举报 2023-04-04

jyztefdp3#

You can use the SQL Server Merge Statement feature. It will Insert a New Row if no match was found or Else Update the existing records for the Matches:

Example :

MERGE BookInventory bi
USING BookOrder bo
ON bi.TitleID = bo.TitleID
WHEN MATCHED THEN
  UPDATE
  SET bi.Quantity = bi.Quantity + bo.Quantity
WHEN NOT MATCHED BY TARGET THEN
  INSERT (TitleID, Title, Quantity)
  VALUES (bo.TitleID, bo.Title,bo.Quantity);

Here if a match is found in table BookInventory for a BookOrder .TitleId then the record get inserted and otherwise, the Matched record will Be updates.

You can also achieve the same using multiple statements. Like this

IF EXISTS(SELECT 1 FROM YourTable WHERE <Condition>
BEGIN
    <Update Statements>
END
ELSE
BEGIN
     <Insert Statement>
END

Or in the Direct Insert, Just check the existence to skip the records that are already in the Target table

INSERT INTO TargetTable
SELECT * FROM SourceTable ST
WHERE NOT EXISTS
(
    SELECT 1 FROM TargetTable WHERE TargetId = ST.TargetId 
)

赞(0）回复(0）举报 2023-04-04

aemubtdh4#

INSERT INTO [INTRANET].[dbo].[SOME_STATUS]
   ( [AUS_STATUS_VID]
    ,[AUS_STATUS_STATUS]
    ,[AUS_STATUS_JAHR]
    ,[AUS_STAUTS_END]
   )

SELECT DISTINCT D.CD_CLVID, '1', '2017', '0'
FROM [SNTRANET].[dbo].[SOME_DETAILS] D
LEFT JOIN [INTRANET].[dbo].[SOME_STATUS] S 
ON S.CD_CLVID = D.CD_CLVID 
WHERE D.CD_JAHR = 2017   
AND S.CD_CLVID is null

If CD_CLVID is the PK in [SNTRANET].[dbo].[SOME_DETAILS] then you would not need the distinct

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server Skip insert (and continue) on duplicate key [T-SQL]

4条答案

相关问题

热门标签

最新问答