Update a IDENTITY Field - SQL Server Forums

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

SQL Server Development (2000)

Update a IDENTITY Field

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-02-18 : 16:46:04

I need to temporarly alter a table so I can alter a ID in an Identity field but I have the Syntax all wrong.

Alter Table dbo.PalletCount_Sub
ALTER COLUMN Pallet_ID
int NOT NULL PRIMARY KEY

UPDATE dbo.PalletCount_Sub
SET Pallet_ID = @NewID
WHERE (Pallet_ID = @OldID)

Alter Table dbo.PalletCount_Sub
ALTER COLUMN Pallet_ID
int NOT Null
IDENTITY (1 , 1) NOT FOR REPLICATION PRIMARY KEY

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-19 : 10:40:23

[code]
USE Northwind
GO

CREATE TABLE myTable99(Col1 int IDENTITY(1,1) NOT NULL PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E'
GO

SELECT * FROM myTable99
GO

DELETE FROM myTable99 WHERE Col1 = 3
GO

SELECT * FROM myTable99
GO

DECLARE @Col1 int
SELECT @Col1 = 3

SET IDENTITY_INSERT myTable99 ON

-- The Following would fail
-- UPDATE myTable99 SET Col1 = @Col1 WHERE Col1 = 5

-- You Need to do...

INSERT INTO myTable99 (Col1, Col2)
SELECT @Col1, Col2 FROM myTable99 WHERE Col1 = 5

SELECT * FROM myTable99

DELETE FROM myTable99 WHERE Col1 = 5
GO

SELECT * FROM myTable99
GO

SET IDENTITY_INSERT myTable99 OFF

INSERT INTO myTable99(Col2)
SELECT 'X'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

[/code]

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-02-19 : 11:08:10

Thanks Brett I knew you would come through

Set IDENTITY_INSERT MAsterPCS.dbo.PalletCount_Sub on

Insert Into MasterPCS.dbo.PalletCount_Sub
(Pallet_Id)
Select 00001

Set IDENTITY_INSERT MAsterPCS.dbo.PalletCount_Sub off

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources