Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Passing parameter to a SET IDENTITY_INSERT

Author  Topic 

sindle@ensco.com
Starting Member

5 Posts

Posted - 2007-02-02 : 15:50:04
Rob writes "I'm trying to do a bulk insert (from a number of tables stored in Access) into tables that have an identity column as PK. So in SQL I have PK's of 1 through 10,000 let's say. I need to delete PK's of 7,000 to 8,000 in the SQL table and import from a table in Access that has corresponding PK's of 7,000 to 8,000.

If I set Identity_insert ON for the table in SQL, then the data comes in fine. THe problem is that I have a number of tables that I have to do this on, and the IDENTITY_INSERT is only valid on one table at a time.

So I thought I'd create an sp that received a tablename as a parameter. Over in Access in vba, I'd iterate through the tables that I need to do this on, and immediately before the INSERT, I'd execute the SP, and pass it the proper tablename and hopefully, everything would fly.

the sp I need to run is:

CREATE PROCEDURE [dbo].[rvsSetIdentityInsert] AS
SET IDENTITY_INSERT Addresses ON

where I vary the tablename.
The SP I'm TRYING to make is like:

CREATE PROCEDURE [dbo].[rvsSetIdentityInsertByTable_JUNK]
@Tablename varchar(50)
AS
SET IDENTITY_INSERT @Tablename ON
GO

but this syntax fails on the second @tablename.

ANY help would be appreciated as I am a novice at writing SP's, but very fluent in Access and VBA.

(Also, if this is a stupid way of handling this problem, please let me know)
Thanks in advance,
Rob"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-02 : 20:49:33
exec ('SET IDENTITY_INSERT ' + @Tablename + ' ON')

Have you seen the keepidentity flag in bulk insert?


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -