Identity Column Fix

Hello, I am new to SQL and inhereted a system with a corrupt table. About 1000 records have an invalid barcode field which is an identity column and does not allow me to update once set. I need to fix these 1000 records by changing the field to 14 digits instead of the incorrect 13. What are some of my options? I was thinking about making a copy of the DB using the Wizzard, then turning the identity feature OFF and doing the fix using command SQL, then turning it back on and copying the DB back to production.

Any suggestions ? Thx

Copying the entire database seems like overkill for just 1000 rows. I've briefly put together an example below that shows how you can copy the data to a staging table, modify the data, then copy it back into your original table. Hope you find this useful.

Be aware that Foreign Key constraints could prevent you deleting rows from the main table. You'd have to drop the constraints then cascade the changes through to any referencing tables before re-applying the constraints. You'll have to do this regardless of the correction method chosen.

Chris

IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL

DROP TABLE #MyTable

IF OBJECT_ID('tempdb..#StagingTable') IS NOT NULL

DROP TABLE #StagingTable

CREATE TABLE #MyTable

(ID INT IDENTITY PRIMARY KEY NOT NULL, MyColumn VARCHAR(10))

--Create a copy of the main table - note absence of the IDENTITY property

CREATE TABLE #StagingTable

(ID INT PRIMARY KEY NOT NULL, MyColumn VARCHAR(10))

--Set up test data

INSERT INTO #MyTable(MyColumn) VALUES('Test')

SELECT [ID], MyColumn

FROM #MyTable

--Copy the rogue data to the staging table

INSERT INTO #StagingTable([ID], MyColumn)

SELECT [ID], MyColumn

FROM #MyTable

WHERE [ID] = 1

--Correct the data in the staging table

UPDATE #StagingTable

SET [ID] = 2

--Delete the rogue data from the main table

DELETE FROM #MyTable

WHERE [ID] = 1

--Copy the corrected data to the production table

SET IDENTITY_INSERT #MyTable ON

INSERT INTO #MyTable([ID], MyColumn)

SELECT [ID], MyColumn

FROM #StagingTable

SET IDENTITY_INSERT #MyTable OFF

SELECT [ID], MyColumn

FROM #MyTable

|||

Identity Column Fix

Hello, I am new to SQL and inhereted a system with a corrupt table. About 1000 records have an invalid barcode field which is an identity column and does not allow me to update once set. I need to fix these 1000 records by changing the field to 14 digits instead of the incorrect 13. What are some of my options? I was thinking about making a copy of the DB using the Wizzard, then turning the identity feature OFF and doing the fix using command SQL, then turning it back on and copying the DB back to production.

Any suggestions ? Thx

Copying the entire database seems like overkill for just 1000 rows. I've briefly put together an example below that shows how you can copy the data to a staging table, modify the data, then copy it back into your original table. Hope you find this useful.

Be aware that Foreign Key constraints could prevent you deleting rows from the main table. You'd have to drop the constraints then cascade the changes through to any referencing tables before re-applying the constraints. You'll have to do this regardless of the correction method chosen.

Chris

IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL

DROP TABLE #MyTable

IF OBJECT_ID('tempdb..#StagingTable') IS NOT NULL

DROP TABLE #StagingTable

CREATE TABLE #MyTable

(ID INT IDENTITY PRIMARY KEY NOT NULL, MyColumn VARCHAR(10))

--Create a copy of the main table - note absence of the IDENTITY property

CREATE TABLE #StagingTable

(ID INT PRIMARY KEY NOT NULL, MyColumn VARCHAR(10))

--Set up test data

INSERT INTO #MyTable(MyColumn) VALUES('Test')

SELECT [ID], MyColumn

FROM #MyTable

--Copy the rogue data to the staging table

INSERT INTO #StagingTable([ID], MyColumn)

SELECT [ID], MyColumn

FROM #MyTable

WHERE [ID] = 1

--Correct the data in the staging table

UPDATE #StagingTable

SET [ID] = 2

--Delete the rogue data from the main table

DELETE FROM #MyTable

WHERE [ID] = 1

--Copy the corrected data to the production table

SET IDENTITY_INSERT #MyTable ON

INSERT INTO #MyTable([ID], MyColumn)

SELECT [ID], MyColumn

FROM #StagingTable

SET IDENTITY_INSERT #MyTable OFF

SELECT [ID], MyColumn

FROM #MyTable

|||

identifying the invalid procedure

Is there any option to identify the invalid procedure objects. Say for example if procedure P1 uses a table T1 and the table T1 is now deleted then P1 procedure will not be executed. I need to identify these procedures.
Any guidance?
sql2k suppors 'defered name resolution' thus it's perfectly legal to have a stored procedure that references a non-existent object. However, it's important to note here is that if the referenced object exists prior to the stored procedure creation, then an entry is made in sysdepends for the sproc and the object. Hence, you can use sp_depends to find dependencies.
e.g.
--a valid proc
create proc bogus
as
select *
from nonexistencetable
go