如何将数据表插入到 SQL Server 数据库表中? [英] How to insert a data table into SQL Server database table?

问题描述

我从某个 Excel 文件中导入了数据，并将其保存到 datatable 中.现在我想将此信息保存在我的 SQL Server 数据库中.

I have imported data from some Excel file and I have saved it into a datatable. Now I'd like to save this information in my SQL Server database.

我在网上看到了很多信息，但我看不懂:

I saw a lot of information on the web but I cannot understand it:

1. 有人说逐行插入另一个建议的批量更新......等等:它更好吗?
2. 我应该使用 OLE 还是 SQL Server 对象(例如 dataAdapter 或 connection)?

1. Someone said insert line by line another suggested bulk update... etc: what it better?
2. Should I use OLE or SQL Server objects (like dataAdapter or connection)?

我需要从他的 Excel 文件中读取员工每周工作时间报告并将其保存到保存所有报告的数据库表中(每周用新记录更新数据库).

My need is to read the employee weekly hours report, from his Excel file and save it to a database table where all the reports are saved (updating the db with new records every week).

Excel 文件仅包含当周的报告.

The Excel file contains reports only for the current week.

推荐答案

在你的数据库中创建一个User-Defined TableType:

Create a User-Defined TableType in your database:

CREATE TYPE [dbo].[MyTableType] AS TABLE(
    [Id] int NOT NULL,
    [Name] [nvarchar](128) NULL
)

并在您的存储过程中定义一个参数:

and define a parameter in your Stored Procedure:

CREATE PROCEDURE [dbo].[InsertTable]
    @myTableType MyTableType readonly
AS
BEGIN
    insert into [dbo].Records select * from @myTableType 
END

并将您的 DataTable 直接发送到 sql server:

and send your DataTable directly to sql server:

using (var command = new SqlCommand("InsertTable") {CommandType = CommandType.StoredProcedure})
{
    var dt = new DataTable(); //create your own data table
    command.Parameters.Add(new SqlParameter("@myTableType", dt));
    SqlHelper.Exec(command);
}

要编辑存储过程中的值，可以声明一个相同类型的局部变量并将输入表插入其中:

To edit the values inside stored-procedure, you can declare a local variable with the same type and insert input table into it:

DECLARE @modifiableTableType MyTableType 
INSERT INTO @modifiableTableType SELECT * FROM @myTableType

然后，您可以编辑@modifiableTableType:

UPDATE @modifiableTableType SET [Name] = 'new value'

这篇关于如何将数据表插入到 SQL Server 数据库表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！