SQL IDENTIFICATION COLUMN

Question

Hi guys, I have a sql table, which is basically an expression. Now, lest you say that the records that I have in my table have a date and an identifier column, which is automatically recorded and determines the order that transactions are displayed in the client interface. The problem is the insert, some of the data is missing, and some transactions between the two dates are missing.

I need to insert data into a table, but I need to insert it between dates, not at the end of the table. If I do a regular insertion, the data will appear at the end of the table, and not at the date indicated by i, because the identity information column is autoload and cannot be updated.

thank

Answer 1

Using SET IDENTITY_INSERT (table) ON, you force SQL Server to insert an arbitrary value into the IDENTITY column, but there is no way to update the IDENTITY column.

What is the big problem with multiple spaces? Yes, this may 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 set - even with spaces.

So again: what a big deal? IDENTITY columns are guaranteed to constantly grow — all that they guarantee. And for 99% of cases this is more than enough ....

Answer 2

, , ?

, ( , , , ...). , ( , , ON UPDATE CASCADE)

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

strong , .

Answer 3

, ID ? , , Identity , ?

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