How do I insert a sequence into a Identity column
-
10 октября 2011 г. 16:14I have a column that contains null values and some numbers, but I want to start inserting an increment into that table, how will I start from select count (*) from table value. Thanks
Все ответы
-
10 октября 2011 г. 16:21
Easiest is probably to create a new column as an identity.
You could create a trigger but the issue there is catering for multi-row updates and that you can't use max on the column due to the existing value. What would you do if an existing value need to be used for a new row?
-
10 октября 2011 г. 16:30There will Multi_row updates in the millions, also the column has to have unique incremental values for every insertion
-
10 октября 2011 г. 16:33МодераторYou need to give the initial value (seed value) to the identity field which will be equal the number of records in a table + 1.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
10 октября 2011 г. 17:52
There's no good way to guarantee that the incremental values will be maintained in time when records are deleted in time. If you really need incremental values you're better off selecting a Row_Number with the rows when you need that sequence than to try to maintain it in data. If you just need unique values that are mostly sequential then an INT IDENTITY column would best work for that.
Row_Number sample:
SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;
INT IDENTY sample:
CREATE TABLE myTable
(ID as INT IDENTITY PRIMARY KEY,
MyFieldName VARCHAR(50)
)
INSERT INTO myTable
(myFieldName)
SELECT
'Test 1'
UNION ALL SELECT
'Test 2'
UNION ALL SELECT
'Test 3'
UNION ALL SELECT
'Test 40'
UNION ALL SELECT
'Test 30'
--now delete a row
DELETE FROM MyTable Where MyFieldName = 'Test 3'
--Select everything and add in a row number too...
SELECT *, ROW_NUMBER() OVER(ORDER BY ID) AS 'Row Number'
FROM MyTable
Eric Isaacs
- Изменено Eric Isaacs 10 октября 2011 г. 17:57
-
10 октября 2011 г. 19:28I cant modify existing table, Unless I have to drop and recreate a temp table , then move datata from old table to new one that has an Identity Column
-
10 октября 2011 г. 19:39МодераторThat may be a solution for your case as well.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
10 октября 2011 г. 19:50But the point is how do I maintain previous entries already in the Column , some are NULL Values, and I want to continue inserting lets say from the 50th row, 51... for new entries
-
10 октября 2011 г. 20:05МодераторI don't think IDENTITY column can accept NULLs. However, you can insert old values if you turn off the identity insert.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
11 октября 2011 г. 18:31
Please how do you think the trigger will work?
I hope it can solve the issue
:= ( while select count (*) from [table] = (value),
start inserting data into the column starting from value + 1 )
-
9 июня 2012 г. 13:00
Won Jondee,
I am not aware which version of SQL Server you are having, But in SQL Server 2012 we do have a new features called Sequences.
Thanks
Manish
Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
.png)
