Update: In SQL Server 2008 there is an even better method of Row Construction for inserting multiple records, please read it here: SQL SERVER Insert Multiple Records Using One Insert Statement – Use of Row Constructor
This is a very interesting question I have received from new development. How can I insert multiple values in a table using only one insert? Now this is an interesting question. When there are multiple records are to be inserted in the table following is the common way using T-SQL.
USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Second',2);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Third',3);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fourth',4);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fifth',5);
GO
The clause INSERT INTO is repeated multiple times. Many times DBA copy and paste it to save time. There is another alternative to this, which I use frequently. I use UNION ALL and INSERT INTO … SELECT… Clauses. Regarding performance there is not much difference. If there is performance difference it does not matter as I use this for one time insert script. I enjoy writing this way, as it keeps my focus on the task, instead of copy paste. I have explained following script to new developers. He was quite pleased.
USE YourDB GO INSERT INTO MyTable (FirstCol, SecondCol) SELECT 'First' ,1 UNION ALL SELECT 'Second' ,2 UNION ALL SELECT 'Third' ,3 UNION ALL SELECT 'Fourth' ,4 UNION ALL SELECT 'Fifth' ,5 GO
The effective result is same.
Reference : Pinal Dave (https://blog.sqlauthority.com) , SQL SERVER – Union vs. Union All – Which is better for performance?
876 Comments. Leave new
can we insert records from excel or text in sql server2000?
Hai ,,,,
I have to insert the values based on three conditions .first i have to insert based on the id values and secondly based on the names and so ..what kind of stored procedure i have to write using sqlserver
Hi,
Its a nice site dave. How to insert values into a table, where 1st column in static value and other columns are from select Query which will come from another table..
Thanks in Advance
Example
Insert into target_table(column_list)
select column_list,static_value from another_table
Hi Dave,
This query really helped me a lot both in time and performance.
Keep the spirit going on
Thank alot! This really helped…
Perfect! Saved me lots of time. I just inserted 4,358 records with one statement! “UNION ALL”
G
Hello pinaldave,
Myself Faisal Qureshi working as Soft. Developer in Mumbai.
I have read ur blog its really helpful.
Wish u very good luck.
Hi Dave,
This z a very good site and a beautiful and useful article. It really helped.. Wish u all the best and expecting much more from you.
Hi there…If there are say 10,000 records in a table..How do I select the second 500 records from it…i.e. the records from number 500 to 1000
You need pagination
Refer point 4
Hey srikant,
Assuming you are using SQL Server 2005, you can do that using the ROW_NUMBER function with the OVER clause.
Here is an example from msdn:
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS ‘RowNumber’
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
you can find more info at: https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017
Can we apply the same for multiple update statements
How to build a unconditional “INSERT ALL” with can statement as Oracle
ej
insert all
into sal_history values(…..)
into sal_history2 values(…..)
select col1,caol2,…. from tableX
into sal_history values(…..)
select col1,caol2,…. from tableX
into sal_history2 values(…..)
select col1,caol2,…. from tableX
hi there,
plz help me to find the solution
I have a table with 3 fields name,price and department.
now i want to
Insert the table with exactly 100,000 records. name field is to be made up of random length A-Z characters but never the letter M. However there MUST always be atleast 1 record of all possible field lengths i.e. 0 to 20.
The price number is a random between 0-10000
The departments are random from the following list
sales,computers,hr.
hi ,u can easily move the excel sheet data to the Sql server using DTS if using SQL 2000 and using SSIS for SQL 2005
I wonder how to do insert query with more than one MS Access database in ADO?
eg:
database1, field1 (password=111)
database2, field2 (password=222)
How do insert field1 from database1 into database2 field2 with one ADO connection?
Thanks in advance
regards,
Martin
I have a source table, T1, with three columns col1, col2 and col3.
I have a destination table, T2, with the same three columns.
I want to move all the data from T1 to T2, deleting all those records in T2 where there exists in T1 a record with the same col1 AND col2 values.
In other words, a record is uniquely defined by the combination of col1 and col2.
What’s the best way to achieve the above?
Thanks
Its Really good and Interesting stuff.
gud stuff
Hi Pinal Dave
i am facing problem in updating more than one nodes in a single xml document.
is there any way to replace more than one node in a sinle xml document
If not then what is the best way to achieve this?
Many thanks…..
Tejal
Hi thanks a lot It is very much useful.
I want to know is there any thing to replace cursor in sql server.
Yes you can use set based approach
Provide more informations on what you are trying to do