CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Patrick Winchester
Patrick Winchester

Reputation: 31

Inserting multiple data into database

I am learning to use SQL Server 2008 R2, and I'm a little stuck at the INSERT option. I want to insert multiple data at once and I'm using this :

INSERT INTO Members (column1, column2, column3, column4, column5)

VALUES ('value1', 'value2', 'value3', 'value4', 'value5'),

       ('value1', 'value2', 'value3', 'value4', 'value5'),

       ('value1', 'value2', 'value3', 'value4', 'value5')

which ofc works. But let's say I have like a 1000 records that I want to insert (from another file, for e.g. Excel), how can I do that ? Any reference to where I can learn this ?

Upvotes: 3

Views: 1405

Answers (5)

gotqn
gotqn

Reputation: 43626

From the documentation:

The maximum number of rows that can be constructed by inserting rows directly in the VALUES list is 1000. Error 10738 is returned if the number of rows exceeds 1000 in that case. To insert more than 1000 rows, use one of the following methods:

  • Create multiple INSERT statements
  • Use a derived table
  • Bulk import the data by using the bcp utility or the BULK INSERT statement

If you want to use the bcp utility this is a nice answer of how it is used : SQL Server Management Studio 2012 - Export/Import data from/to table

Upvotes: 0

Fabrice Mainguené
Fabrice Mainguené

Reputation: 506

If you want insert values from an excel file, use the import wizard of sql server, it's the easiest. In sql server, right-click your database, point to Tasks, and then click Import Data or Export data. (look http://msdn.microsoft.com/en-us/library/ms140052.aspx for more explaination)

Upvotes: 0

beaver
beaver

Reputation: 1

  1. you may save your data file to CSV format file, eg C:\myData.csv
  2. Execute the statement " LOAD DATA INFILE 'C:/myData.csv' INTO TABLE Members"

Hope this can help you:)

Upvotes: 0

Praveen Nambiar
Praveen Nambiar

Reputation: 4892

You can simply run a Export Wizard on the database. Just follow the below steps.

Step 1: Right Click on the Database
Step 2: Select Task
Step 3: Select Export Data
Step 4: Enter Data Source details ( here you will select your Excel file )

  • Select Excel File as DataSource
  • And give the Excel file path

Step 5: Enter Destination details ( your table name )
Step 6: Select the tables you want to recreate.

Thats it.

Upvotes: 0

SMS
SMS

Reputation: 462

Use SELECT statement to insert multiple data like:

INSERT INTO Members (column1, column2, column3, column4, column5)
SELECT 'value1', 'value2', 'value3', 'value4', 'value5'
UNION
SELECT 'value1', 'value2', 'value3', 'value4', 'value5'
UNION
SELECT 'value1', 'value2', 'value3', 'value4', 'value5'

or

INSERT INTO Members (column1, column2, column3, column4, column5)
SELECT Column1, Column2, Column3, Column4, Column5 FROM Table1

Upvotes: 3

Related Questions