January 15, 2019 at 10:26 am
I have a table with only 1 column "fldID". That column is a primary key (to ensure uniqueness), identity column with a seed of 9000. How do I insert a new record into this table so that I can return it's value for "fldID" ? The table is being used to generate new unique numbers.
I want to be able to insert a record and return the new ID that was generated starting at 9000, 9001, 9002 etc... however scripting the table as an INSERT statement produces code that errors. I assume because:
INSERT INTO [dbo].[tblOrderNumberLog]) VALUES)
I get the error Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
I thought about adding a second column and just inserting the number '1' or something but it seems to be a waste of space and I would like to be able to do it with a single column.
January 15, 2019 at 10:33 am
Not sure what this table is for, you could use Sequences
declare @i int
insert into [dbo].[tblOrderNumberLog] default values
set @i = SCOPE_IDENTITY()
select @i
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 15, 2019 at 10:35 am
r.gall - Tuesday, January 15, 2019 10:26 AMI have a table with only 1 column "fldID". That column is a primary key (to ensure uniqueness), identity column with a seed of 9000. How do I insert a new record into this table so that I can return it's value for "fldID" ? The table is being used to generate new unique numbers.I want to be able to insert a record and return the new ID that was generated starting at 9000, 9001, 9002 etc... however scripting the table as an INSERT statement produces code that errors. I assume because:
INSERT INTO [dbo].[tblOrderNumberLog]) VALUES)
I get the errorMsg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.I thought about adding a second column and just inserting the number '1' or something but it seems to be a waste of space and I would like to be able to do it with a single column.
Have you considered using a SEQUENCE for this?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 15, 2019 at 10:41 am
r.gall - Tuesday, January 15, 2019 10:26 AMI have a table with only 1 column "fldID". That column is a primary key (to ensure uniqueness), identity column with a seed of 9000. How do I insert a new record into this table so that I can return it's value for "fldID" ? The table is being used to generate new unique numbers.I want to be able to insert a record and return the new ID that was generated starting at 9000, 9001, 9002 etc... however scripting the table as an INSERT statement produces code that errors. I assume because:
INSERT INTO [dbo].[tblOrderNumberLog]) VALUES)
I get the errorMsg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.I thought about adding a second column and just inserting the number '1' or something but it seems to be a waste of space and I would like to be able to do it with a single column.
Not real sure about that script or what you were trying with the one parenthesis but you would need to set identity insert on and refer to the column. So something like: SET IDENTITY_INSERT tblOrderNumberLog ON;
INSERT INTO tblOrderNumberLog
(WhateverIsTheColumnName)
VALUES
(9001), (9002), (9003);
SET IDENTITY_INSERT tblOrderNumberLog OFF;
If you want to get the last identity value for a table, you can use IDENT_CURRENT, SELECT IDENT_CURRENT('tblOrderNumberLog');
Sue
January 15, 2019 at 10:43 am
You can use set identity_insert table_name on
e.g:set identity_insert [dbo].[tblOrderNumberLog] ON
INSERT INTO [dbo].[tblOrderNumberLog](ID)
select row_number() over (order by (select null))
from sys.columns
set identity_insert [dbo].[tblOrderNumberLog] OFF
January 16, 2019 at 11:36 am
INSERT INTO [dbo].[tblOrderNumberLog]) VALUES)I agree with everyone suggesting you replace this pattern with a Sequence object instead. You are setting yourself up for problems down the road.
That being said, this is the answer to your question:INSERT dbo.tblOrderNumberLog DEFAULT VALUES;
If you roll back the transaction that inserts the row, the identity value will still advance, but you won't use any storage (this is a hack that fakes a Sequence. You should still use a Sequence):set nocount on;
DECLARE @a int, @b-2 int, @C int;
CREATE TABLE #a(id int identity(9000, 1) NOT NULL PRIMARY KEY);
BEGIN TRANSACTION;
INSERT #a DEFAULT VALUES;
SELECT SCOPE_IDENTITY() AS NewIDValue;
ROLLBACK TRANSACTION;
BEGIN TRANSACTION;
INSERT #a DEFAULT VALUES;
SELECT SCOPE_IDENTITY() AS NewIDValue;
ROLLBACK TRANSACTION;
BEGIN TRANSACTION;
INSERT #a DEFAULT VALUES;
SELECT SCOPE_IDENTITY() AS NewIDValue;
ROLLBACK TRANSACTION;
SELECT count(*) AS [RowsInTable] FROM #a;
NewIDValue
---------------------------------------
9000
NewIDValue
---------------------------------------
9001
NewIDValue
---------------------------------------
9002
RowsInTable
-----------
0
Eddie Wuerch
MCM: SQL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply