Missing Row and ID can it be inserted again?
-
I have a table that has a missing row. This table has an Identity seed and I need to recreate this row. How can I do this?
Can I just insert into the table with that missing ID specified as the value for that column? Or do I need to create a new table?
-
Firstly you'll need to make sure that no changes are currently being made to your table. You could then copy the do the following:
--Copy data to temp
SELECT (idCol, col1, col2, col3)
INTO #tmpBackup
FROM table
WHERE ID > missingID
DELETE FROM table
WHERE ID > missingID
GO
--RESEED the ID column
DBCC CHECKIDENT (table, RESEED, missingID -1)
GO
--Insert missing values
INSERT INTO table (col1, col2, col3)
VALUES (val1, val2, val3)
-- Copy Data back to table
INSERT INTO table(col1, col2, col3)
SELECT col1, col2, col3
FROM #tmpBackup
ORDER BY ID
I think that this should work
. There are probably better ways of doing this.....Ade
-
Well I fixed it but not the optimal way. I found the ID that was missing. Renamed the table, disabled ID seed. Inserted the value in the ID column, Renabled the ID seed and set the Seed Start value to the highest current ID and then renamed the table the original name. Any one have a better way?
-
what about SET IDENTITY_INSERT? Sorry about length of this post, but BOL says it pretty well
Description from SQL 2000 Books Online
Allows explicit values to be inserted into the identity column of a table.
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.
The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.
Example from SQL 2000 Books Online
This example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.
-- Create products table.CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))GO-- Inserting values into products table.INSERT INTO products (product) VALUES ('screwdriver')INSERT INTO products (product) VALUES ('hammer')INSERT INTO products (product) VALUES ('saw')INSERT INTO products (product) VALUES ('shovel')GO-- Create a gap in the identity values.DELETE products WHERE product = 'saw'GOSELECT * FROM productsGO-- Attempt to insert an explicit ID value of 3;-- should return a warning.INSERT INTO products (id, product) VALUES(3, 'garden shovel')GO-- SET IDENTITY_INSERT to ON.SET IDENTITY_INSERT products ONGO-- Attempt to insert an explicit ID value of 3INSERT INTO products (id, product) VALUES(3, 'garden shovel').GOSELECT * FROM productsGO-- Drop products table.DROP TABLE productsGO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply