topic Re: how to insert data to sql server table with no duplicate primary keys? in SAS Programming

how to insert data to sql server table with no duplicate primary keys?

Mayt — Wed, 01 Nov 2023 08:37:31 GMT

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.

Re: how to insert data to sql server table with no duplicate primary keys?

Kurt_Bremser — Wed, 01 Nov 2023 09:08:45 GMT

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.

Re: how to insert data to sql server table with no duplicate primary keys?

LinusH — Wed, 01 Nov 2023 12:37:54 GMT

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.