April 9, 2013 at 4:31 pm
i have a table as follows
ColA PK autoincrement
Colb nchar
ColC ncahr
ColD nchar
and i have to insert values into this table but ensure that the first column is always inserted with a value that is one more than the primary key that already exists. is there an inbuilt SQL function that does this on sql server.
thanks for the help
April 9, 2013 at 4:39 pm
Please forgive my dumbA** the insert will automatically add teh new seed if the identity specification is assigned to increment by 1. but i would still like to know how to insert into a table by adding 1 to the previous value so that if the last record was something as
2 this that here there
the next record could be added as
3 now again here we
and ID field added as 3 with a function rather than with a manual insert.
April 10, 2013 at 1:27 am
Something like this?
CREATE TABLE myTable
(
ColA int NOT NULL,
ColB varchar ( 10 ) NOT NULL
);
GO
INSERT INTO myTable ( ColA, ColB )
SELECT COALESCE ( MAX ( ColA ) + 1, 1 ), 'Hello1' FROM myTable
INSERT INTO myTable ( ColA, ColB )
SELECT COALESCE ( MAX ( ColA ) + 1, 1 ), 'Hello2' FROM myTable
SELECT ColA, ColB FROM myTable ORDER BY ColA
April 10, 2013 at 1:44 am
Have a look at IDENTITY_INSERT in Books Online.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 10, 2013 at 4:51 pm
SQLTestUser (4/9/2013)
and i have to insert values into this table but ensure that the first column is always inserted with a value that is one more than the primary key that already exists. is there an inbuilt SQL function that does this on sql server.
No, there is nothing built in. Why do you need an unbroken sequence of numbers in a key column? Just curious, what is the use-case?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 11, 2013 at 8:15 am
For reasons I won't go into here, for a system I am developing I can't use IDENTITY so I have a table to hold the next row ID value to assign.
CREATE TABLE NextRowId(
NidTableName varchar(255) NULL,
NidCurrentId int NULL
) ON PRIMARY
Then assign a new value in a stored procedure in a transaction so (theoretically) there will not be duplicate values.
CREATE PROCEDURE [dbo].[GetNextRowId]
@Table VarChar(255)
AS
BEGIN
Declare @result int
BEGIN TRY
BEGIN TRANSACTION
Update NextRowId Set @result = NidCurrentId = NidCurrentId + 1 Where NidTableName = @Table
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
RETURN @result
END
I have not done much testing on this or gotten any feedback on whether this is totally loony or what...
April 11, 2013 at 8:47 am
What happens if after you get the next number from [dbo].[GetNextRowId] you try to do the insert into the base table and it fails? That's the same problem an IDENTITY column has. It burns that number because it doesn't want to hold up the system because an unbroken sequence just isn't that important for a surrogate key to function properly.
If you need an unbroken sequence you'll basically be creating a bottleneck on that number being issued such that the getting of the number and the inserting of the row that uses it must occur in a transaction. If the insert fails, the insert and the issuing of the number is also rolled back maintaining the integrity of the unbroken sequence.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 11, 2013 at 9:05 am
opc.three (4/11/2013)
If you need an unbroken sequence you'll basically be creating a bottleneck on that number being issued such that the getting of the number and the inserting of the row that uses it must occur in a transaction. If the insert fails, the insert and the issuing of the number is also rolled back maintaining the integrity of the unbroken sequence.
Good point. In my case I don't need the unbroken sequence but do need it to be unique...
I am doing some testing to see if there is a problem with duplicates when there are multiple instances hitting the SP at the same time.
April 11, 2013 at 9:37 am
If you do not need unbroken sequence don't reinvent the wheel, use an IDENTITY column. It hamdles concurrency for you and when relying on auto-generation will guarantee uniqueness.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply