Date: Fri, 21 Feb 2003 13:47:45 -0500
Reply-To: Katie Weaver <kweaver@U.WASHINGTON.EDU>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Katie Weaver <kweaver@U.WASHINGTON.EDU>
Subject: Identity Field problem in SQL Server
I'm having a problem inserting rows from a SAS dataset into a SQL Server
table. The SQL Server table contains an identity field which I want to be
automatically updated by SQL Server when the SAS records are inserted.
Here's the code I'm using:
libname b odbc dsn=SQLDB;
insert into b.SQLTABLE (Var1, Var2, Var3)
select * from a.SASData;
When I run the above code, I get the following error message:
ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]
Cannot insert explicit value for identity column in
table 'SQLTABLE' when IDENTITY_INSERT is set to OFF.
ERROR: ROLLBACK issued due to errors for data set B.SQLTABLE.DATA
I searched SAS Technical Support and found that SN-002627 addresses this
problem. It suggests three workaround solutions, one of which is:
'Use proc sql pass-through with execute insert statements. Here the
identity variable is automatically updated by SQL Server.'
Can someone explain to me what 'execute insert statements' are and how I
can modify the code above to use execute insert statements?