代码之家 › 专栏 › 技术社区 › izb

当主ID设置为序列时,如何将数据批量插入表中

sqlbulkcopy bulkinsert sql-server sql c#

1

izb · 技术社区 · 7 年前

我想用 SqlBulkCopy 在Id列设置为序列的表中插入多行。序列和表格如下所示:

CREATE SEQUENCE [dbo].[MyTableId] 
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE -2147483648
 MAXVALUE 2147483647
 CACHE  10 
GO

CREATE TABLE [dbo].[MyTable](
    [Id] [int] NOT NULL,
    [SomeColumn] [int] NOT NULL,
    [AnotherColumn] [nvarchar](100) NOT NULL
  CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
  (
    [Id] ASC
  )
GO

ALTER TABLE [dbo].[MyTable] ADD
  CONSTRAINT [DF_MyTable_Id]
  DEFAULT (NEXT VALUE FOR [MyTableId]) FOR [Id]
GO

导入行的代码如下所示:

var table = new DataTable();

using (var adapter = new SqlDataAdapter($"SELECT TOP 0 * FROM dbo.MyTable", conn))
{
    adapter.Fill(table);
}

foreach (Data d in data)
{
    var row = table.NewRow();

    row["SomeColumn"] = d.someColumnValue;
    row["AnotherColumn"] = d.anotherColumnValue;

    table.Rows.Add(row);
}

using (var bulk = new SqlBulkCopy(conn))
{
    bulk.DestinationTableName = "dbo.MyTable";
    bulk.WriteToServer(table);
}

将数据写入服务器时失败,因为

System.InvalidOperationException: 'Column 'Id' does not allow DBNull.Value.'

我曾尝试从表定义中删除Id列,但这只会使列序号减少一个。我已尝试将Id行设置为:

table.Columns["Id"].AutoIncrement = true;

但这会忽略序列,并且在重复运行导入时,会从0重新启动自动增量值。

如何指示服务器使用其序列生成新的Id值?或者,在表中创建新行之前,是否可以为序列单独生成多个值?

0 回复 | 直到 7 年前

1

0

David Martinez C. 5 年前

通过 SqlCommand.ExecuteScalar() 。使用此SQL语句作为命令的输入:

SELECT current_value
FROM sys.sequences
WHERE OBJECT_ID = OBJECT_ID(N'dbo.MyTableId');

然后设置列属性 AutoIncrementSeed 将上一个值加上一:

// ... code to get current sequence value
string sqlText = <above sql goes here>;
SqlCommand getSeqValue = new(sqlText, your_connection);
long currentSequenceValue = (long)getSeqValue.ExecuteScalar();

// Construct DataTable structure
// maybe replacing adapter.Fill(table);  
// with 
//adapter.FillSchema(table,SchemaType.Source);

// tell table to start ID on current sequence value + 1 (or AutoIncrementStep)
table.Columns["Id"].AutoIncrement = true;
table.Columns["Id"].AutoIncrementSeed = currentSequenceValue + 1;

// prepare things and bulk insert

只是个想法,还没有测试过/