如何将数据表插入到SQL Server数据库表中? [英] How to insert a data table into SQL Server database table?
问题描述
我已经从某些Excel文件导入数据,并将其保存到 datatable
中。现在我想将这些信息保存在我的 SQL Server
数据库中。
我看到很多有关网络但我不明白:
- 有人说插入一行又一个建议批量更新...等等:更好? / li>
- 我应该使用
OLE
或SQL Server
对象(如dataAdapter
或连接
)?
我的需要是从他的Excel文件中读取员工每周工作时间报告,并将其保存到数据库表中,其中保存所有报告(每周更新数据库以获取新记录)。
Excel文件仅包含本周的报告。
创建一个您的数据库中定义的TableType
:
CREATE TYPE [dbo]。[MyTableType] AS TABLE b $ b [Id] int NOT NULL,
[名称] [nvarchar](128)NULL
)
并在 Stored Ptocedure中定义一个参数
:
CREATE PROCEDURE [dbo ]。[InsertTable]
@myTableType MyTableType readonly
AS
BEGIN
insert into [dbo] .Records select * from @myTableType
END
并将您的 DataTable
直接发送到sql server:
using(var command = new SqlCommand(InsertTable){CommandType = CommandType.StoredProcedure})
{
var dt = new DataTable(); //创建自己的数据表
command.Parameters.Add(new SqlParameter(@ myTableType,dt));
SqlHelper.Exec(command);
}
要编辑存储过程中的值,可以声明一个局部变量,相同的类型并插入输入表:
DECLARE @modifiableTableType MyTableType
INSERT INTO @modifiableTableType SELECT * FROM @ myTableType
然后,您可以编辑 @modifiableTableType
:
更新@modifiableTableType SET [Name] ='new value'
pre>
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 mySQL Server
database.I saw a lot of information on the web but I cannot understand it:
- Someone said insert line by line another suggested bulk update... etc: what it better?
- Should I use
OLE
orSQL Server
objects (likedataAdapter
orconnection
)?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).
The Excel file contains reports only for the current week.
解决方案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 Ptocedure
:CREATE PROCEDURE [dbo].[InsertTable] @myTableType MyTableType readonly AS BEGIN insert into [dbo].Records select * from @myTableType END
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
Then, you can edit
@modifiableTableType
:UPDATE @modifiableTableType SET [Name] = 'new value'
这篇关于如何将数据表插入到SQL Server数据库表中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!