CODEWITHSUNDEEP
sqlsql-serversql-server-2008insert
user3545217
user3545217

Reputation: 303

Inserting more than 1000 rows from Excel into 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.)

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?

Upvotes: 18

Views: 62737

Answers (6)

Dami
Dami

Reputation: 309

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

Upvotes: 3

Nur.B
Nur.B

Reputation: 397

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)

Upvotes: 4

aborg88
aborg88

Reputation: 319

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

Upvotes: 28

Dave C
Dave C

Reputation: 7392

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

Upvotes: 2

Praveen Lobo
Praveen Lobo

Reputation: 7187

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

Upvotes: 1

Erik Gillespie
Erik Gillespie

Reputation: 3959

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.

Upvotes: 6

Related Questions