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.
| 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] ASSET IDENTITY_INSERT Addresses ONwhere I vary the tablename.The SP I'm TRYING to make is like:CREATE PROCEDURE [dbo].[rvsSetIdentityInsertByTable_JUNK] @Tablename varchar(50)ASSET IDENTITY_INSERT @Tablename ONGObut 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. |
 |
|
|
|
|
|
|
|
Passing parameter to a SET IDENTITY_INSERT