Description
In this article, I am going to write T SQL Script to Insert Multiple Rows into Table in single statement with different ways in SQL Server. You can choose best way as per your wish. But If you have 1000 of records I would suggest you to go with SqlBulkCopy in C#.
Summary
- Insert Multiple Rows into Table in SQL Server
- Insert Multiple Rows into Table with Union ALL
- Insert Multiple Rows into Table with Union
- Insert Multiple Rows into Table by select Rows from another Table
- Insert Multiple Rows into Table using SqlBulkCopy in C# with DataTable
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
Thanks,
Morgan
Software Developer