CODEWITHSUNDEEP
sqlsql-server
SLDem
SLDem

Reputation: 2182

How to get the MAX Id value from a table in MS SQL Server

I'm trying to update the table data with some row that should have defined a MAX Id + 1 value for the Movement Id field which is the PK in my table.

The Movement Id field is the INT PK, the Added At field is a date and the IncOutc is the INT field that can store either 1 or 2(Income or Outcome).

query.sql

DECLARE @max_id INT;
SET @max_id = SELECT MAX([Movement Id]) FROM Movement

INSERT INTO Movement([Movement Id], [Added At], IncOutc) 
VALUES (max_id, GETDATE(), 1)

I tried the query above but got an error: Incorrect syntax near the keyword 'SELECT'. (on line 1).

I already tried inserting the values like this:

VALUES (SELECT MAX([Movement Id]) FROM Movement, max_id, GETDATE(), 1)

But got an error saying: Incorrect syntax near '1'. (on line 4)

Upvotes: 1

Views: 2207

Answers (2)

SMor
SMor

Reputation: 2862

To answer your actual question, you use:

SET @max_id = (SELECT MAX([Movement Id]) FROM Movement);

Notice the statement terminator. That is a good habit to develop. You might wonder what happens when your table has no rows. I suggest you try it and see for yourself.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You don't. You use an IDENTITY column or SEQUENCE when you create the table. So:

CREATE TABLE Movements (
    Movement_Id INT IDENTITY(1, 1) PRIMARY KEY,
    Added_At DATETIME DEFAULT GETDATE(),
    IncOutc Int
);

Then you insert to it as:

INSERT INTO Movements (IncOutc)
     VALUES (1);

Movement_Id and Added_At are given appropriate default values on the insert.

Attempting to do this outside the database is very problematic. In particular, two inserts at the same time might generate the same id in the table -- presumably not what you want. Preventing that requires locking the table, which is very expensive. Especially considering that SQL Server has built-in functionality to do this.

Upvotes: 4

Related Questions