Pregunta Posible implementar un incremento manual con solo SQL INSERT?


Tengo una clave principal que no deseo que se incremente automáticamente (por varias razones) y, por lo tanto, estoy buscando una forma de incrementar ese campo simplemente cuando INSERTO. Simplemente, quiero decir, sin procedimientos almacenados y sin activadores, así que solo una serie de comandos SQL (preferiblemente un comando).

Esto es lo que he intentado hasta ahora:

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

Sin embargo, cuando se ejecuta, el comando falla, diciendo que

"Las subconsultas no están permitidas en este   contexto. solo las expresiones escalares son   permitido"

Entonces, ¿cómo puedo hacer esto / qué estoy haciendo mal?


EDITAR: Dado que se señaló como una consideración, se garantiza que la tabla que se insertará ya tiene al menos 1 fila.


5
2018-04-27 18:04


origen


Respuestas:


Usted comprende que tendrá colisiones, ¿verdad?

necesita hacer algo como esto y esto podría causar interbloqueos, así que esté muy seguro de lo que está intentando lograr aquí

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

Para explicar la colisión, he proporcionado algunos códigos.

Primero crea esta tabla e inserta una fila

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

Ahora abre dos ventanas de consulta y ejecuta esto al mismo tiempo

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

Verás un montón de estos

Servidor: Msg 2627, nivel 14, estado 1, línea 7 Violación de la restricción PRIMARY KEY 'PK__Table1__3213E83F2962141D'. No se puede insertar una clave duplicada en el objeto 'dbo.Table1'. La instrucción se ha terminado.


9
2018-04-27 18:16



Pruebe esto en su lugar:

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

No recomendaría hacerlo de esa manera por una serie de razones (rendimiento, seguridad de las transacciones, etc.)


2
2018-04-27 18:08



No sé si alguien todavía está buscando una respuesta, pero aquí hay una solución que parece funcionar:

-- 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
2018-03-13 15:24



Tenemos una situación similar en la que necesitábamos incrementar y no podíamos tener lagunas en los números. (Si usa un valor de identidad y se revierte una transacción, ese número no se insertará y tendrá espacios vacíos porque el valor de identidad no se revierte).

Creamos una tabla separada para el último número utilizado y lo sembramos con 0.

Nuestra inserción requiere algunos pasos.

--incrementar el número Actualizar dbo.NumberTable establecer número = número + 1

--decide cuál es el número incrementado seleccione @number = numero de dbo.NumberTable

--utiliza el número inserta en dbo.MyTable usando el número @

confirmar o revertir

Esto hace que las transacciones simultáneas se procesen en una sola línea, ya que cada transacción concurrente esperará porque la tabla numérica está bloqueada. Tan pronto como la transacción en espera obtiene el bloqueo, incrementa el valor actual y lo bloquea de los demás. Ese valor actual es el último número utilizado y si una transacción se retrotrae, la actualización de NumberTable también se retrotrae para que no haya espacios.

Espero que ayude.

Otra forma de provocar la ejecución de un solo archivo es utilizar un bloqueo de aplicación SQL. Hemos utilizado ese enfoque para procesos de ejecución más prolongada, como la sincronización de datos entre sistemas, por lo que solo se puede ejecutar un proceso de sincronización a la vez.


1
2018-04-03 19:48



Podría ser porque no hay registros, por lo que la consulta secundaria devuelve NULL ... intente

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

1
2018-04-27 18:13



Si lo está haciendo en un activador, puede asegurarse de que sea un activador "INSTEAD OF" y hacerlo en un par de declaraciones:

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

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

Lo único sobre lo que tendrías que tener cuidado es la concurrencia: si se insertan dos filas al mismo tiempo, podrían intentar usar el mismo valor para @next, causando un conflicto.

¿Esto logra lo que quieres?


0
2018-04-27 18:08



Esto debería funcionar:

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

O esto (LÍMITE sustituto para otras plataformas):

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

0
2018-04-27 18:09



declare @nextId int
set @nextId = (select MAX(id)+1 from Table1)

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

commit;

Pero quizás un mejor enfoque sería usar una función escalar getNextId ('table1')


0
2018-04-27 18:10



Parece muy extraño hacer este tipo de cosas sin una columna de IDENTIDAD (incremento automático), lo que me hace cuestionar la arquitectura en sí. Quiero decir, en serio, esta es la situación perfecta para una columna de IDENTIDAD. Podría ayudarnos a responder su pregunta si explicara el razonamiento detrás de esta decisión. =)

Dicho esto, algunas opciones son:

  • usando un gatillo INSTEAD OF para este propósito. Por lo tanto, harías tu INSERT (la instrucción INSERT no tendría que pasar una identificación). El código de activación manejaría la inserción de la identificación apropiada. Necesitaría usar la sintaxis WITH (UPDLOCK, HOLDLOCK) utilizada por otro usuario para mantener el bloqueo durante la duración del desencadenante (que está implícitamente envuelto en una transacción) y para elevar el tipo de bloqueo de "compartido" a "actualización" "bloqueo (IIRC).
  • puede utilizar la idea anterior, pero tiene una tabla cuyo propósito es almacenar el último valor máximo insertado en la tabla. Por lo tanto, una vez que se configura la tabla, ya no tendrá que hacer un SELECT MAX (ID) todas las veces. Simplemente incrementaría el valor en la tabla. Esto es seguro siempre que use el bloqueo apropiado (como se explica). De nuevo, eso evita repetitivos escaneos de tabla cada vez que INSERTES.
  • Usa GUIDs en lugar de IDs. Es mucho más fácil combinar tablas en las bases de datos, ya que los GUID siempre serán únicos (mientras que los registros en las bases de datos tendrán ID enteros en conflicto). Para evitar la división de páginas, se pueden usar GUID secuenciales. Esto solo es beneficioso si necesita fusionar la base de datos.
  • Use un procedimiento almacenado en lugar del enfoque de activación (ya que, por alguna razón, se deben evitar las activaciones). Aún tendría el problema de bloqueo (y los problemas de rendimiento que pueden surgir). Pero los sprocs se prefieren al SQL dinámico (en el contexto de las aplicaciones) y, a menudo, tienen un rendimiento mucho mayor.

Lo siento por divagar. Espero que ayude.


0
2018-04-27 18:45



¿Alguna crítica de esto? Funciona para mi.

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
2017-10-03 12:24