SQL 将超过 1000 行从 Excel 插入到 SQLServer
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24328730/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting more than 1000 rows from Excel into SQLServer
提问by user3545217
I'm new to Sql but what is the best way to insert more than 1000 rows from an excel document into my database(Sql server 2008.)
我是 Sql 的新手，但是将 1000 多行从 excel 文档插入到我的数据库中的最佳方法是什么（Sql server 2008。）
For example I'm using the below query:
例如，我正在使用以下查询：
INSERT INTO mytable(companyid, category, sub, catalogueref)
VALUES
('10197', 'cat', 'sub', '123'),
('10197', 'cat2', 'sub2', '124')
This is working fine but there is a limit of inserting 1000 records and I have 19000 records and I don't really want to do 19 separate insert statements and another question, is that the company id is always the same is there a better way then writing it 19000 times?
这工作正常，但插入 1000 条记录有限制，我有 19000 条记录，我真的不想做 19 个单独的插入语句，另一个问题是，公司 ID 始终相同有没有更好的方法写了 19000 次？
采纳答案by Erik Gillespie
Microsoft provides an import wizardwith SQL Server. I've used it to migrate data from other databases and from spreadsheets. It is pretty robust and easy to use.
Microsoft 为SQL Server提供了一个导入向导。我用它从其他数据库和电子表格迁移数据。它非常强大且易于使用。
回答by aborg88
Just edit the data in Excel or another program to create N amount of insert statements with a single insert for each statement, you'll have an unlimited number of inserts. For example...
只需在 Excel 或其他程序中编辑数据以创建 N 条插入语句，每个语句只插入一次，您将拥有无限数量的插入。例如...
INSERT INTO table1 VALUES (6696480,'McMurdo Station',-77.846,166.676,'Antarctica','McMurdo')
INSERT INTO table1 VALUES (3833367,'Ushuaia',-54.8,-68.3,'America','Argentina')
...19,000 later
INSERT INTO table1 VALUES (3838854,'Rio Grande',-53.78769,-67.70946,'America','Argentina')
回答by Dave Cullum
There are several options, the Import Wizard which Erik suggests, or SSIS is another good one.
有几个选项，Erik 建议的导入向导，或者 SSIS 是另一个不错的选择。
Read here: Import Excel spreadsheet columns into SQL Server database
阅读此处：将 Excel 电子表格列导入 SQL Server 数据库
回答by Praveen Lobo
You should be able to insert using multiple transactions -
您应该能够使用多个事务插入 -
BEGIN TRANSACTION
Insert into mytable(companyid,category,sub,catalogueref)
values
('10197','cat', sub','123'),
('10197','cat2', sub2','124')
...998 more rows...
COMMIT TRANSACTION
go
BEGIN TRANSACTION
Insert into mytable(companyid,category,sub,catalogueref)
values
('10197','cat', sub','123'),
('10197','cat2', sub2','124')
...998 more rows...
COMMIT TRANSACTION
