用sql查询替换recordset.addnew [英] Sql query to replace recordset.addnew

问题描述

我很好奇在sql中你如何添加一个类似于使用recordset.addnew的新行的查询。



到目前为止我还没有我需要在我的表中手动插入一个新行，但我意识到我不确定如何将一个新行插入到一个表中，该表的每个新行都会增加一个标识列。



VB6程序中的代码首先运行如下所示的查询：

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

然后有一个名为rs的记录集。

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)
);

推荐答案

你会使用一个INSERT语句。



类似

You would use an INSERT statement.

Something like

INSERT INTO TableName (Column1, Column2, ...)
VALUES (value1, value2, ...)

欲了解更多信息，请查看INSERT [ ^ ]



关于身份，如果在表创建中定义了身份，你只需在INSERT语句中省略该列。

For more information, have a look at INSERT[^]

Concerning the identity, if the identity is defined in the table creation, you just omit that column in the INSERT statement.

这篇关于用sql查询替换recordset.addnew的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！