Insert Multiple Rows into Table in SQL Server

Insert Multiple Rows into Table in SQL Server by Single Statement

-- Check and Drop Existing Temp Table
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tempTable'))
          DROP TABLE #tempTable

--Create temp table
CREATE TABLE #tempTable (ID INT, UserName NVARCHAR(50));

-- Insert Multiple Values into SQL Server by Single Statement
INSERT INTO #tempTable (ID, UserName) VALUES (1, 'User1');
INSERT INTO #tempTable (ID, UserName) VALUES (2, 'User2');
INSERT INTO #tempTable (ID, UserName) VALUES (3, 'User3');

--Select inserted values from temp table 
Select * From #tempTable

Insert Multiple Rows into Table with Union ALL

-- Check and Drop Existing Temp Table
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tempTable'))
          DROP TABLE #tempTable

--Create temp table
CREATE TABLE #tempTable (ID INT, UserName NVARCHAR(50));

-- Insert Multiple Values into SQL Server using UNION ALL
INSERT INTO #tempTable (ID, UserName) 
Select 1, 'User1'
UNION ALL
Select 2, 'User2'
UNION ALL
Select 2, 'User2'
UNION ALL
Select 3, 'User3'

--Select inserted values from temp table 
Select * From #tempTable

Note: When you insert multiple rows using UNION ALL, it just combines the all the rows. It will not remove duplicate rows. So if you want to remove duplicate rows from multiple insert you need to use just UNION instead of  UNION ALL

Insert Multiple Rows into Table in SQL Server using Union by Single Statement

-- Check and Drop Existing Temp Table
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tempTable'))
          DROP TABLE #tempTable

--Create temp table
CREATE TABLE #tempTable (ID INT, UserName NVARCHAR(50));

-- Insert Multiple Values into SQL Server using UNION
INSERT INTO #tempTable (ID, UserName) 
Select 1, 'User1'
UNION 
Select 2, 'User2'
UNION 
Select 2, 'User2'
UNION 
Select 3, 'User3'

--Select inserted values from temp table 
Select * From #tempTable

Note: When you insert multiple rows using UNION, it removes the duplicate rows. So if you don’t want to remove duplicate rows from multiple insert you need to use UNION ALL instead of  UNION.

Insert Multiple Rows into Table by select Rows from another Table

-- Check and Drop Existing Temp Table
IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tempTable'))
          DROP TABLE #tempTable

--Create temp table
CREATE TABLE #tempTable (ID INT, UserName NVARCHAR(50));

-- Insert Multiple Values into table in SQL Server using UNION
INSERT INTO #tempTable (ID, UserName) 
Select 1, 'User1'
UNION 
Select 2, 'User2'
UNION 
Select 3, 'User3'

IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tempTable2')) 
           DROP TABLE #tempTable2

--Create second temp table
CREATE TABLE #tempTable2 (ID INT, UserName NVARCHAR(50));

-- Insert Multiple Values into table by select rows from another table 
INSERT INTO #tempTable2 (ID, UserName) Select ID,UserName From #tempTable


--Select inserted values from second temp table 
Select * From #tempTable2

Insert Multiple Rows into Table using SqlBulkCopy in C# with DataTable

Create SQL Table

CREATE TABLE [dbo].[ProductSalesData](
 [SaleDate] [smalldatetime] NOT NULL,
 [ProductName] [nvarchar(1000)] NOT NULL,
 [TotalSales] [int] NOT NULL)

Bulk Insert into MS SQL Server using SqlBulkCopy in C# with DataTable

using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlBulkInsertExample
{
    class Program
    {
      static void Main(string[] args)
        {
            DataTable prodSalesData = new DataTable("ProductSalesData");

            // Create Column 1: SaleDate
            DataColumn dateColumn = new DataColumn();
            dateColumn.DataType = Type.GetType("System.DateTime");
            dateColumn.ColumnName = "SaleDate";

            // Create Column 2: ProductName
            DataColumn productNameColumn = new DataColumn();
            productNameColumn.ColumnName = "ProductName";

            // Create Column 3: TotalSales
            DataColumn totalSalesColumn = new DataColumn();
            totalSalesColumn.DataType = Type.GetType("System.Int32");
            totalSalesColumn.ColumnName = "TotalSales";

            // Add the columns to the ProductSalesData DataTable
            prodSalesData.Columns.Add(dateColumn);
            prodSalesData.Columns.Add(productNameColumn);
            prodSalesData.Columns.Add(totalSalesColumn);

            // Let's populate the datatable with our stats.
            // You can add as many rows as you want here!

            // Create a new row
            DataRow dailyProductSalesRow = prodSalesData.NewRow();
            dailyProductSalesRow["SaleDate"] = DateTime.Now.Date;
            dailyProductSalesRow["ProductName"] = "Nike";
            dailyProductSalesRow["TotalSales"] = 10;

            // Add the row to the ProductSalesData DataTable
            prodSalesData.Rows.Add(dailyProductSalesRow);

            // Copy the DataTable to SQL Server using SqlBulkCopy
            using (SqlConnection dbConnection = new SqlConnection("Data Source=ProductHost;Initial Catalog=dbProduct;Integrated Security=SSPI;Connection Timeout=60;Min Pool Size=2;Max Pool Size=20;"))
            {
                dbConnection.Open();
                using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                {
                    s.DestinationTableName = prodSalesData.TableName;

                    foreach (var column in prodSalesData.Columns)
                        s.ColumnMappings.Add(column.ToString(), column.ToString());

                    s.WriteToServer(prodSalesData);
                }
            }
        }
    }
}

The Output is

select * from dbo.ProductSalesData

SaleDate               ProductName  TotalSales
04/12/2013 00:00:00    Nike         10