Hello!

CREATE TABLE [dbo].[MSG]( [MSG_ID] [int] NOT NULL, [NAME] [int] NOT NULL, [USER_ID] [int] NOT NULL, CONSTRAINT [PK_MSG] PRIMARY KEY CLUSTERED ( [MSG_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Also it is required to insert values ​​into the table through select, but does not quit

I do this, but instead of MSG_ID I don’t know what to insert:

 INSERT INTO [dbo].[MSG] ([MSG_ID], [NAME], [USER_ID]) SELECT [ЧТО СЮДА ?], [NAME], [USER_ID] FROM [dbo].[USERS] WHERE [dbo].[USERS].[DELETED] <> 1
  • Comments are not intended for extended discussion; conversation moved to chat . - Nick Volynkin ♦

2 answers 2

Due to the fact that a separate table is used to store the last ID (as it was found out in the correspondence to the question), we can offer only this solution:

 begin transaction; update LAST_IDS set LAST_ID=LAST_ID where NAME = 'MSG'; -- Блокируем запись INSERT INTO [dbo].[MSG] ([MSG_ID], [NAME], [USER_ID]) SELECT L.LAST_ID + row_number() over(order by (select NULL)), -- Получаем ID U.NAME, U.USER_ID FROM [dbo].[USERS] U, LAST_IDS L WHERE [dbo].[USERS].[DELETED] <> 1 AND L.NAME = 'MSG'; update LAST_IDS set LAST_ID=LAST_ID+@@ROWCOUNT where NAME = 'MSG'; -- Меняем последний использованный ID commit;
  • 2
    Instead of the first update you can pull LAST_ID into a variable with simultaneous locking -> select @lastID = LAST_ID from LAST_IDS WITH (UPDLOCK, HOLDLOCK) where NAME = 'MSG'; , and in insert do not re-apply to LAST_IDS . - i-one
  • another amendment: over(order by NULL) -> over(order by (SELECT NULL)) - i-one
  • @ i-one Corrected. perverts then MS with banning constants in over ... - Mike
  • Yes, a strange ban, it would be possible to allow, while variables are allowed - i-one

If you have Sql Server 2012 version and above, you can use the sequence (SEQUENCE) - this is a database object that represents a monotonous series of numbers. It has an initial value and a pitch.

To use it, you need to create it by specifying the name, scheme and a set of parameters. A more detailed description is available at the link below:

https://docs.microsoft.com/ru-ru/sql/t-sql/statements/create-sequence-transact-sql

To apply it in an INSERT construct, use the following example.

 INSERT Test.TestTable (CounterColumn,Name) VALUES (NEXT VALUE FOR %Название вашего сиквенса%, 'Syed') ;