IDENTITY INSERT

[Dianne Watson]

I need to insert into a table that has an identity insert set to on.
I need to know how to turn it off while I insert data into the table.

Here is the senerio

column 1
1
2

[eroscoe]

The easiest way to do this is using Enterprise Manager. Go into EM, find your table and right click on it. Select 'Design Table'. Highlight the column you want to alter. On the bottom pane of your screen, you will see a Colums tab with several options listed. Click on the value in the Identity field (should be Yes) and choose No. Then save your changes.

I hope this helps...

[cs_troyk]

Dianne;

Here's a sample script that may shed some light on your problem:

-------- Begin Script --------
CREATE TABLE Test
(i int IDENTITY(1,1) NOT NULL)
GO

INSERT INTO Test DEFAULT VALUES
GO

SELECT i FROM Test
GO

SET IDENTITY_INSERT Test ON
INSERT INTO Test(i) VALUES(1)
SET IDENTITY_INSERT Test OFF
INSERT INTO Test(i) VALUES(1) --Error
GO

SELECT i FROM Test
GO

DROP TABLE Test
GO
---------- End Script ----------

HTH,
TroyK, MCSD