Geek Answers Handbook

Is it possible to implement manual increment with a simple SQL INSERT?

I have a primary key that I do not want to automatically increase (for various reasons), and therefore I am looking for a way to simply increase this field when I am INSERT. I just mean without stored procedures and without triggers, so just a sequence of SQL commands (preferably one command).

Here is what I have tried so far:

BEGIN TRAN

INSERT INTO Table1(id, data_field)
VALUES ( (SELECT (MAX(id) + 1) FROM Table1), '[blob of data]');

COMMIT TRAN;

* Data abstracted to use generic names and identifiers

However, when executing the command, errors saying that

"Subqueries are not allowed in this context. Only scalar expressions are allowed."

So how can I do this / what am I doing wrong?

EDIT: Since it has been indicated as a consideration, the table in which it will be inserted will have at least at least 1 row.

+5
10

, ?

- , , ,

DECLARE @id int
BEGIN TRAN

    SELECT @id = MAX(id) + 1 FROM Table1 WITH (UPDLOCK, HOLDLOCK)
    INSERT INTO Table1(id, data_field)
    VALUES (@id ,'[blob of data]')
COMMIT TRAN

,

CREATE TABLE Table1(id int primary key not null, data_field char(100))
GO
Insert Table1 values(1,'[blob of data]')
Go


declare @i int
set @i =1
while @i < 10000
begin
BEGIN TRAN

INSERT INTO Table1(id, data_field)
SELECT MAX(id) + 1, '[blob of data]' FROM Table1

COMMIT TRAN;
set @i =@i + 1
end

: Msg 2627, 14, 1, 7 PRIMARY KEY "PK__Table1__3213E83F2962141D". "dbo.Table1". .

+9

:

INSERT INTO Table1 (id, data_field)
SELECT id, '[blob of data]' FROM (SELECT MAX(id) + 1 as id FROM Table1) tbl

- (, ..)

+2

, , NULL... try

INSERT INTO tblTest(RecordID, Text) 
VALUES ((SELECT ISNULL(MAX(RecordID), 0) + 1 FROM tblTest), 'asdf')
+1

, - , , , , :

-- Preparation: execute only once
    CREATE TABLE Test (Value int)

CREATE TABLE Lock (LockID uniqueidentifier)
INSERT INTO Lock SELECT NEWID()

-- Real insert

    BEGIN TRAN LockTran

    -- Lock an object to block simultaneous calls.
    UPDATE  Lock WITH(TABLOCK)
    SET     LockID = LockID

    INSERT INTO Test
    SELECT ISNULL(MAX(T.Value), 0) + 1
    FROM Test T

    COMMIT TRAN LockTran
+1

, . ( , , , .)

0.

.

- dbo.NumberTable set number = number + 1

- , @number = number dbo.NumberTable

- dbo.MyTable, @number

, , NumberTable . , . , , NumberTable , .

, .

- SQL. , , .

+1

, , "INSTEAD OF" :

DECLARE @next INT
SET @next = (SELECT (MAX(id) + 1) FROM Table1)

INSERT INTO Table1
VALUES (@next, inserted.datablob)

, , concurrency - , @next, .

, ?

0

:

INSERT INTO Table1 (id, data_field)
SELECT (SELECT (MAX(id) + 1) FROM Table1), '[blob of data]';

( LIMIT ):

INSERT INTO Table1 (id, data_field)
SELECT TOP 1
    MAX(id) + 1, '[blob of data]'
FROM
   Table1
ORDER BY
   [id] DESC;
0
declare @nextId int
set @nextId = (select MAX(id)+1 from Table1)

insert into Table1(id, data_field) values (@nextId, '[blob of data]')

commit;

, , getNextId ('table1')

0

IDENTITY (auto-increment), . , , IDENTITY. , . =)

, :

  • INSTEAD OF . , INSERT ( INSERT ). . WITH (UPDLOCK, HOLDLOCK), , ( ) "" "" " (IIRC).
  • , , , . , , SELECT MAX (ID) . . , ( ). , , INSERT.
  • GUID . , GUID ( ). , GUID. , .
  • ( - ). ( , ). sprocs SQL ( ) .

. , .

0

Any criticisms? It works for me.

DECLARE @m_NewRequestID INT
        , @m_IsError BIT = 1
        , @m_CatchEndless INT = 0

WHILE @m_IsError = 1
    BEGIN TRY
        SELECT  @m_NewRequestID = (SELECT ISNULL(MAX(RequestID), 0) + 1 FROM Requests)

        INSERT INTO Requests (  RequestID
                                , RequestName
                                , Customer
                                , Comment
                                , CreatedFromApplication)
            SELECT  RequestID = @m_NewRequestID
                    , RequestName = dbo.ufGetNextAvailableRequestName(PatternName)
                    , Customer = @Customer
                    , Comment = [Description]
                    , CreatedFromApplication = @CreatedFromApplication
                FROM    RequestPatterns
                WHERE   PatternID = @PatternID

        SET @m_IsError = 0
    END TRY
    BEGIN CATCH
        SET @m_IsError = 1
        SET @m_CatchEndless = @m_CatchEndless + 1
        IF @m_CatchEndless > 1000
            THROW 51000, '[upCreateRequestFromPattern]: Unable to get new RequestID', 1
    END CATCH
0
source

More articles:


All Articles