Sql query to replace recordset.addnew

See more:

SQL

I'm curious how a query would look like in sql where you add a new row similar to using recordset.addnew.

So far I haven't had the need to manually insert a new row in my table but I realized that I'm not really sure how to insert a new row in to a table with an identity column that's incremented for each new row.

The code in an VB6 program first runs a query that looks like this:

select top 1 from table

and then there is a recordset called rs.

rs.addnew
rs!attribute = somevalue
rs!attribute1 = somevalue
...
rs.update

What's a good way to replicate this in a single query if I manually would like to add a new row and get the correct value in the identity column?


Follow up after trying the posted answer and it worked on one table but not on this that I created recently.

CREATE TABLE [dbo].[HyllaDirekt] (
    [Id]      INT          NOT NULL,
    [Temponr] NUMERIC (18) NOT NULL,
    [Platsid] BIGINT       NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Id', table 'synpro.dbo.HyllaDirekt'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Is generated when inserting into this column using

insert into hylladirekt(temponr,platsid)

Changing the definition to this fixed it:

CREATE TABLE [dbo].[HyllaDirekt] (
    [Id]      NUMERIC (18)   IDENTITY (1, 1) NOT NULL,
    [Temponr] NUMERIC (18) NOT NULL,
    [Platsid] BIGINT       NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);