mathematical statistician
=========================================================================
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;
proc sql;
insert into b.SQLTABLE (Var1, Var2, Var3)
select * from a.SASData;
quit;

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?