SQL Insert Multiple Rows

Question

I want to insert multiple rows in a single table. How can I do this using single insert statement?

Answer 1

Wrap each row of values to be inserted in brackets/parenthesis (value1, value2, value3) and separate the brackets/parenthesis by comma for as many as you wish to insert into the table.

INSERT INTO example
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

Answer 2

You can use SQL Bulk Insert Statement

BULK INSERT TableName
FROM 'filePath'
WITH
(
  FIELDTERMINATOR = '','',
  ROWTERMINATOR = ''\n'',
  ROWS_PER_BATCH = 10000, 
  FIRSTROW = 2,
  TABLOCK
)

for more reference check

https://www.google.co.in/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=sql%20bulk%20insert

You Can Also Bulk Insert Your data from Code as well

for that Please check below Link:

http://www.codeproject.com/Articles/439843/Handling-BULK-Data-insert-from-CSV-to-SQL-Server

Answer 3

1--> {Simple Insertion when table column sequence is known}
    Insert into Table1
    values(1,2,...)

2--> {Simple insertion mention column}  
    Insert into Table1(col2,col4)
    values(1,2)

3--> {bulk insertion when num of selected collumns of a table(#table2) are equal to Insertion table(Table1) }   
    Insert into Table1 {Column sequence}
    Select * -- column sequence should be same.
       from #table2

4--> {bulk insertion when you want to insert only into desired column of a table(table1)}
    Insert into Table1 (Column1,Column2 ....Desired Column from Table1)  
    Select Column1,Column2..desired column from #table2

Answer 4

You can use UNION All clause to perform multiple insert in a table.

ex:

INSERT INTO dbo.MyTable (ID, Name)
SELECT 123, 'Timmy'
UNION ALL
SELECT 124, 'Jonny'
UNION ALL
SELECT 125, 'Sally'

Check here

Answer 5

For MSSQL, there are two ways:(Consider you have a 'users' table,below both examples are using this table for example)

1) In case, you need to insert different values in users table. Then you can write statement like:

    INSERT INTO USERS VALUES
(2, 'Michael', 'Blythe'),
(3, 'Linda', 'Mitchell'),
(4, 'Jillian', 'Carson'),
(5, 'Garrett', 'Vargas');

2) Another case, if you need to insert same value for all rows(for example, 10 rows you need to insert here). Then you can use below sample statement:

    INSERT INTO USERS VALUES
(2, 'Michael', 'Blythe')
GO 10

Hope this helps.

Answer 6

You can use the UNION ALL function http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

Answer 7

We will import the CSV file into the destination table in the simplest form. I placed my sample CSV file on the C: drive and now we will create a table which we will import data from the CSV file.

DROP TABLE IF EXISTS Sales 

CREATE TABLE [dbo].[Sales](
    [Region] [varchar](50) ,
    [Country] [varchar](50) ,
    [ItemType] [varchar](50) NULL,
    [SalesChannel] [varchar](50) NULL,
    [OrderPriority] [varchar](50) NULL,
    [OrderDate]  datetime,
    [OrderID] bigint NULL,
    [ShipDate] datetime,
    [UnitsSold]  float,
    [UnitPrice] float,
    [UnitCost] float,
    [TotalRevenue] float,
    [TotalCost]  float,
    [TotalProfit] float
)

The following BULK INSERT statement imports the CSV file to the Sales table.

BULK INSERT Sales
FROM 'C:\1500000 Sales Records.csv'
WITH (FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR='\n' );