SQL Server – Simultaneous Data Insertion

sql server

I have a small label printing software that print sequential numbers. It selects the last serialnumber on sql db's and add one.

The problem I have is that I need now two users to print sequential serial numbers.

SQL Select statement:

sql_query.CommandText = "SELECT TOP 1 cast (serialnumber as nvarchar) as serialnumber,
                                indexnumber, versao
                         FROM  etiquetas
                         where partnumber Like '" & partnumber & "'
                         ORDER BY serialnumber DESC"

Sql Insert Statement:

 "if not exists (select serialnumber
                 from etiquetasklc.dbo.etiquetas
                 where partnumber like '" & partnumber & "'
                 and serialnumber like '" & serialnumber & "')
  begin
      INSERT INTO etiquetasklc.dbo.etiquetas(Nome_Posto, data, Lote, serialnumber, partnumber,
                                             Versao, indexnumber, Semana, QRGerado, operador)
      VALUES( '" & posto & "','" & data & "','" & ordemfabrico & "','" & serialnumber & "','" & partnumber & "',
              '" & partnumber2 & "','" & indexnumber & "','" & weekyear & "','" & codigo & "','" & operador & "')
  end "

The problem occurs when they hit the print button simultaneous.

Can anyone help me

I'm no coder and the SQL is wrote based on my limited skills

Best Answer

A lot of the support code for this recommended answer depends on how the serial numbers are formatted. For this case, I will assume a simple incrementing number, but adding in other information into the serial number is just a matter of concatenating the extra information onto the start or the end of the incrementing number.

If you are using SQL Server 2012 or later (and, really, you should be if you can), then implementing a SEQUENCE is your best bet. A sequence in SQL Server is an object that will return the next number in sequence as defined by your CREATE SEQUENCE statement. Say, for example, that your serial number's variable portion is a nine digit number starting at 100000000, allowing for about 900 million sequence numbers. To create the sequence, your code would be:

CREATE SEQUENCE dbo.SerialNumber  
    START WITH 100000000
    INCREMENT BY 1;

Now, when you want a new sequence / serial number, you can retrieve it with a command like:

DECLARE @NextSerialNumber bigint    -- Default data type for a sequence
SET @NextSerialNumber = NEXT VALUE FOR SerialNumber

If you need two people to generate separate sequential sequences (so, each sequence should have the same numbers), you can create two different sequences to draw from. If the two people should be creating serial numbers for the same sequential sequence (say, the same part twice, or a sequence that needs to be non-identical between two different tables, so that table A gets sequence entries 1, 2, and 4, while table B gets sequence entries 3, 5, and 6), use one sequence only.

Related Question