SQL IDENTITY COLUMN

Question

Hey guys i am have an sql table which is basically a statement. Now lest say the records i have in my table have a date and an identity column which is autonumbered and defines the order which the transactions are displayed in the front end to the client. The issue is during an insert some of the data have gone missing and some transactions between two dates are missing.

I need to insert the data into the table but i need to insert them between the dates and not at the end of the table.If i do a a normal insert now the data will appear at the end of the table and not at the date i specify because the identity column is autonumbered and cannot be updated.

Thanks

Answer 1

Why not just display the records in the user interface sorted by the date, rather than by the primary key?

OK, if you really want to do this (personally, I think changing the sort date in the UI is going to be easier than updating the primary key values in the database, but anyway...). This should work, assuming you're not using the primary key values in any foreign key constraints (if you are, then you'll need to make sure those constraints have ON UPDATE CASCADE set)

SET IDENTITY_INSERT tablename ON

UPDATE tablename SET
    primary_key = primay_key + 1
WHERE
    primary_key >= <the primary key where you want to insert the new date>

INSERT INTO tablename
    (primary_key, date, ...)
VALUES
    (<the primary key to insert>, <the date to insert>, ...)

SET IDENTITY_INSERT tablename OFF

However, I strongly, strongly suggest you backup your database before attempting this.

Answer 2

Using SET IDENTITY_INSERT (table) ON, you force SQL Server to let you insert any arbitrary value into an IDENTITY column - but there's no way to update an IDENTITY column.

What's the big problem with a few gaps anyway?? Yes, it might be a bit of a "cosmetic" problem - but how much hassle and effort do you really want to spend on cosmetic problems?? The order of the entries is still a given - even with gaps.

So again: what's the big deal?? IDENTITY columns are guaranteed to be ever increasing - that's all they guarantee. And for 99% of the cases, that's more than good enough....

Answer 3

Just out of curiosity, is it one ID per date? Your answers imply this a little, so if so, replace the Identity column with a computed column that is defined as the date difference in days from an arbitrary starting point?

DECLARE @EXAMPLE TABLE
(
    [Date] DATE,
    ID AS DATEDIFF(Day, '1 Jan 2010', [Date])
)

INSERT INTO @EXAMPLE([Date])
VALUES (GETDATE()), (GETDATE()+1), (GETDATE()+2)

SELECT * FROM @EXAMPLE