Inserting more than 1000 rows from Excel into SQLServer

Question

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

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?

Where are you getting the data from? Another table? A file? Your noggin? — Dave C, Jun 20 '14 at 13:43
read about [bcp utility](http://msdn.microsoft.com/en-us/library/ms162802.aspx) — Remus Rusanu, Jun 20 '14 at 13:43
Here's workaround with using INSERT INTO ... SELECT ... UNION ALL https://stackoverflow.com/a/63719211/1831734 — mr R, Sep 03 '20 at 09:05

score 24 · Answer 1 · answered Jul 17 '15 at 12:14

24

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...

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

answered Jul 17 '15 at 12:14

aborg88

279
4
8

4

This turns out to be a lot faster to achieve than splitting the `INSERT` statement into batches of 1000 value tuples. Thanks for bringing this to my attention. – Oliver Oct 26 '16 at 20:06
This also takes a lot longer for Sql Server to parse by X seconds where performance matters (CI Testing) – RiverHeart Jun 19 '23 at 14:36

score 6 · Accepted Answer · answered Jun 20 '14 at 13:50

6

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.

answered Jun 20 '14 at 13:50

Erik Gillespie

3,929
2
31
48

This is a good solution. But what I would like to know is what orientation is your original data in? Is it Excel? Access? You could just export a csv file if it is and use that. If the data's configured some other way there could be other solutions. – jollarvia Jun 20 '14 at 14:01
The asker has the data in an Excel spreadsheet so exporting to CSV and then using another tool such as `bcp` would also be an option. The user interface of the import wizard is pretty handy when import issues start coming up though. – Erik Gillespie Jun 20 '14 at 14:04
Yeah my moneys on the import wizard – jollarvia Jun 20 '14 at 14:05
Thanks Erik, the interface for this is really easy to use! – user3545217 Jun 20 '14 at 14:42

score 2 · Answer 3 · answered Jun 20 '14 at 14:04

2

There are several options, the Import Wizard which Erik suggests, or SSIS is another good one.

Read here: Import Excel spreadsheet columns into SQL Server database

answered Jun 20 '14 at 14:04

Dave C

7,272
1
19
30

score 2 · Answer 4 · edited Sep 03 '20 at 19:15

2

INSERT INTO mytable(companyid, category, sub, catalogueref)
SELECT companyid, category, sub, catalogueref from (VALUES
('10197', 'cat', 'sub', '123'),
('10197', 'cat2', 'sub2', '124')
//more 1000 rows...
as sub (companyid, category, sub, catalogueref)

edited Sep 03 '20 at 19:15

Caconde

4,177
7
35
32

answered Sep 03 '20 at 17:34

Nur.B

319
2
4

1

to improve the answer, please give some explanation as to the reason that this works. – Kirby Sep 03 '20 at 23:54
1

follow the link: https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15 – Nur.B Sep 11 '20 at 10:29

score 1 · Answer 5 · answered Jun 20 '14 at 13:56

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

Dami · Answer 6 · 2022-02-04T14:55:58.203

Ok, it's a late answer but I ran into this very same problem and found a solution that worked twice as fast as @Nur.B's solution for a 7K-row insertion.

Note: whenever possible prefer to use TRANSACTIONS when dealing with large amounts of data.

INSERT INTO mytable(companyid, category, sub, catalogueref)
SELECT '10197', 'cat', 'sub', '123' UNION ALL
SELECT '10197', 'cat2', 'sub2', '124' UNION ALL
-- ... other N-thousand rows
SELECT '10197', 'catN-1', 'subN-1', '12312' UNION ALL
SELECT '10197', 'catN', 'subN', '12313'; -- don't add "UNION ALL" statement on the last line

Inserting more than 1000 rows from Excel into SQLServer

6 Answers6

Linked