生成包含标识列的插入语句 [英] Generating insert statement that includes identity column

问题描述

我正在尝试使用CommandBuilder生成包含Identity列的插入语句。为了生成测试数据，我希望能够在表上启用身份插入后将一个值插入到身份列中

I am trying to use CommandBuilder to generate an insert statement that includes an Identity column. For test data generation purposes I want to be able to insert a value into the identity column after Identity Insert has been enabled on the table

var sql = "Select * from Table";
var dataAdapter = new SqlDataAdapter(sql, conn);
dataAdapter.Fill(currentTable);
var cb = new SqlCommandBuilder(dataAdapter);

命令生成器确实生成了插入语句，但是我无法使其包含身份列。我正在为许多表创建通用解决方案，因此我想避免手动生成语句。有没有办法欺骗它包括所有列？

The command builder does generate the insert statement, but I can't get it to include the identity column. I am creating a generic solution for many tables, so I want to avoid generating the statements manually. Is there a way to trick it to include all columns?

我也对不包含CommandBuilder的其他解决方案持开放态度

I am also open to other solutions that doesn't include CommandBuilder

推荐答案

使用纯SQL执行此操作：

Use pure SQL to do this:

set IDENTITY_INSERT dbo.MyTable ON;

insert into dbo.MyTable (id, ...) values (1, ...)
...

set IDENTITY_INSERT dbo.MyTable OFF;

这篇关于生成包含标识列的插入语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！