SQL 2000 IDENTITY ON/OFF

Question

SQL 2000 IDENTITY ON/OFF

GlenParker

SSC Eights!

Points: 971
More actions
May 14, 2010 at 8:16 am

#74291

I am copying data from an inner join condition to select only applicable data for an external customer.
My problem is that I need to deselect the column attribute ie. ID int identity (1,1) not null in order for the transaction to complete.
I have tried the following with no success, option 1 wont run and option 2 runs but has no effect
ALTER TABLE tbl_765_associated_ap
ALTER COLUMN ID int identity (1,1) not null
ALTER TABLE tbl_765_associated_ap
ALTER COLUMN ID int not null
So far I am altering the tables manually, any ideas?
Glen Parker 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

GlenParker (5/14/2010)
I am copying data from an inner join condition to select only applicable data for an external customer.
My problem is that I need to deselect the column attribute ie. ID int identity (1,1) not null in order for the transaction to complete.
I have tried the following with no success, option 1 wont run and option 2 runs but has no effect
ALTER TABLE tbl_765_associated_ap
ALTER COLUMN ID int identity (1,1) not null
ALTER TABLE tbl_765_associated_ap
ALTER COLUMN ID int not null
So far I am altering the tables manually, any ideas?

Glen your post wasn't clear..you said you were copying....so if that means you are inserting into a table, and want to avoid using the identity() property of the table, that's what i toook this to mean. If not, be more specific, and show us what you are doing, please.

If my guess was right then in that case you want to do this:

SET IDENTITY_INSERT [tbl_765_associated_ap] ON

INSERT INTO [tbl_765_associated_ap](ID,OtherColumns)

SELECT SOMEID,SomeOtherColumns FROM SomeotherTable

SET IDENTITY_INSERT [tbl_765_associated_ap] OFF

{edit: fixed missing underscores of identity_insert}

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GlenParker SSC Eights! Points: 971 More actions · Answer 2

Hi Lowell, thanks for your input, should have been clearer in the first instance, script as follows;

INSERT INTO [tbl_765_documents_temp](ID, [765_id], [765_Document], Document_title, filename, filesize, contenttype, filedata, date_attached)

SELECT ID, [765_id], [765_Document], Document_title, filename, filesize, contenttype, filedata, date_attached

FROMtbl_765_documents INNER JOIN tbl_765_register ON [765_id] = id_765

WHEREap_id LIKE '101B-0701%' OR ap_id LIKE '101B-0703%'

I've inherited the table 'tbl_765_register' from the original administrator and wary of incurring anomalies by manually adding the Identity attribute to the production database. It does work on a shadow DB but would prefer to switch the attribute off/on for the transaction.

PS tried the SET [IDENTITY] INSERT [tbl_765_documents_temp] ON

but keep getting the following;

Line 1: Incorrect syntax near 'IDENTITY'.

I'm running SS8.00.760 (SP 3)

Glen Parker 🙂

Lowell SSC Guru Points: 323482 More actions · Answer 3

ahh that's just syntax; the SET switch is for IDENTITY_INSERT, all one word with an underscore,no brackets around identity; i fat fingered my example and forgot the underscore.

--you had SET [IDENTITY] INSERT [tbl_765_documents_temp] ON

SET IDENTITY_INSERT [tbl_765_documents_temp] ON

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GlenParker SSC Eights! Points: 971 More actions · Answer 4

Excellent, works as advertised!

Thanks for your time and effort.

May all your hangovers be swiftly cured...

Glen Parker 🙂