Hi guys!
I've tried insert data to sql server table. If the primary keys are not duplicated, I want it to just insert that row to the table. If the primary keys are duplicated, I want it just keep the first row and skip the latter.
the data; table named test_utf8_output
valuedate and TDR_Flag are the primary keys
What I expect
My code:
/* create new table in sql server) */
PROC SQL;
connect using mylib;
execute(
CREATE TABLE PK_TEST (
string nvarchar(255)
, ValueDate nvarchar(255)
, TDR_Flag tinyint
, CONSTRAINT PK_col PRIMARY KEY(ValueDate,TDR_Flag)
)
) by mylib;
quit;
/* insert data*/
proc sql;INSERT INTO mylib.pk_test (string, ValueDate, TDR_Flag)SELECT string, V_Date,TDR_Flag FROM test_utf8_output;quit;
error I got:
ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Violation of PRIMARY KEY constraint
'PK_col'. Cannot insert duplicate key in object 'dbo.PK_TEST'. The duplicate key value is (20231030, 0). : [SAS][ODBC SQL
Server Wire Protocol driver][Microsoft SQL Server]The statement has been terminated.
ERROR: ROLLBACK issued due to errors for data set MYLIB.pk_test.DATA.
I've tried WITH (IGNORE_DUP_KEY = ON)
:
PROC SQL;
connect using mylib;
execute(
CREATE TABLE PK_TEST (
string nvarchar(255)
, ValueDate nvarchar(255)
, TDR_Flag tinyint
, CONSTRAINT PK_col PRIMARY KEY(ValueDate,TDR_Flag)
WITH (IGNORE_DUP_KEY = ON))
) by mylib;
quit;
/* insert data*/
proc sql;
INSERT INTO mylib.pk_test (string, ValueDate, TDR_Flag)
SELECT string, V_Date,TDR_Flag FROM test_utf8_output;
quit;
still error
ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Duplicate key was ignored.
ERROR: ROLLBACK issued due to errors for data set MYLIB.pk_test.DATA.
Most simple method: sort with NODUPKEY before inserting.
If the current order is important, and you need to avoid any unwanted change (e.g. because of the way the data is stored - SPDS), use a hash object:
data want;
set have;
if _n_ = 1
then do;
declare hash dup ();
dup.definekey("valuedate","tdr_flag");
dup.definedone();
end;
if dup.check() ne 0;
dup.add();
run;
Untested, posted from my tablet. For tested code, provide usable example data in a DATA step with DATALINES. No pictures, no Excel.
If you took your time and effort to create a table in SQL Server with PK, I assume there will be subsequesnt inserts.
Then you need to do more than just removing duplicates in the input.
The update startegy is usually called "upsert", matching rows are updated (or ignored based on your requirements, new records are inserted.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.