CODEWITHSUNDEEP
sqlsql-server
siddhartha sharma
siddhartha sharma

Reputation: 19

how to insert more than 50000 row in a table in ms-sql at one time...?

its a query page,which consist of 50000 queries.All the queries are insert query. example:-

CREATE TABLE states (
  id int primary key NOT NULL,
  name varchar(30) NOT NULL,
  country_id int NOT NULL ,  
) 

INSERT INTO states (id, name, country_id) VALUES
(1, 'Andaman and Nicobar Islands', 101),
(2, 'Andhra Pradesh', 101),
(3, 'Arunachal Pradesh', 101),
(4, 'Assam', 101),
(5, 'Bihar', 101),
(6, 'Chandigarh', 101),
(7, 'Chhattisgarh', 101),
so - on .....

Upvotes: 0

Views: 3220

Answers (1)

Shahar Gvirtz
Shahar Gvirtz

Reputation: 2448

When inserting large amount of rows (actually, 1000+ rows) its usually better to use BULK INSERT.

BULK INSERT can be used as SQL Statement (https://msdn.microsoft.com/en-us/library/ms188365.aspx) or when working with code, for example from .NET application, using SqlBulkCopy object (https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx).

Using BULK INSERT reduce the amount of ping-pong between the client and the server, allow using minimal-logging (in specific condition, the most important is that the DB will be in Bulk-Logged recovery model which have implications on point-in-time restore) and generally offer better performance.

BULK INSERT also allows you to easily import data from CSV for example. You can use "Import and Export Data" wizzard which installed as part of SQL Server to import data from various data sourced. behind the scenes, it uses bcp which use BULK INSERT.

Upvotes: 2

Related Questions