从Excel中插入超过1000行到SQLServer

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的新手，但是从excel文档中插入1000多行到我的数据库（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次？

4 个解决方案

#1

Microsoft provides an import wizard with 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的导入向导。我用它来从其他数据库和电子表格迁移数据。它非常强大且易于使用。

#2

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')

#3

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数据库

#4

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

#1