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
i inserted multiple items in our SQL database but it is somewhat not appearing or even if I invoke it in our web service it is not appearing. can someone help me with this??
Hi,
I also inserted 66,000 items in our SQL database and it was inserted succesfully when I check the count of the items in the database, but when i am accessing it in our website it is not displaying. Can someone help me with this?
Did you get any errors during insertion?
Did you get any errors when you insert data to the table?
Did you get any error duing insertion of data?
how can i insert all rows one time into table in sql server2005
INSERT INTO tab_treeview1 values
(186, ‘vedioui.gif’, 151, ‘Linear Algebra Coordinates with Respect to a Basis.mp4’, ‘Linear Algebra Coordinates with Respect to a Basis.mp4’, ‘Coordinates wit’),
(187, ‘vedioui.gif’, 151, ‘Linear Algebra Coordinates with respect to orthonormal bases.mp4’, ‘Linear Algebra Coordinates with respect to orthonormal bases.mp4’, ‘Coordinates wit’),
(188, ‘vedioui.gif’, 151, ‘Linear Algebra Cross Product Introduction.mp4’, ‘Linear Algebra Cross Product Introduction.mp4’, ‘Cross Product ‘),
(189, ‘vedioui.gif’, 151, ‘Linear Algebra Deriving a method for determining inverses.mp4’, ‘Linear Algebra Deriving a method for determining inverses.mp4’, ‘Determining Inv’)
(190, ‘vedioui.gif’, 151, ‘Linear Algebra Determinant after row operations.mp4’, ‘Linear Algebra Determinant after row operations.mp4’, ‘Row operations’)
(191, ‘vedioui.gif’, 151, ‘Linear Algebra Determinant as Scaling Factor.mp4’, ‘Linear Algebra Determinant as Scaling Factor.mp4’, ‘Scaling Factor’),
(192, ‘vedioui.gif’, 151, ‘Linear Algebra Determinant when row is added.mp4’, ‘Linear Algebra Determinant when row is added.mp4’, ‘ when row is ad’),
(193, ‘vedioui.gif’, 151, ‘Linear Algebra Determinant when row multiplied by scalar.mp4’, ‘Linear Algebra Determinant when row multiplied by scalar.mp4’, ‘Multiplied by s’),
(194, ‘vedioui.gif’, 151, ‘Linear Algebra Determinants along other rowscols.mp4’, ‘Linear Algebra Determinants along other rowscols.mp4’, ‘ Rowscols’),
(195, ‘vedioui.gif’, 151, ‘Linear Algebra dim(V) + dim(orthogonoal complelent of V)=n.mp4’, ‘Linear Algebra dim(V) + dim(orthogonoal complelent of V)=n.mp4’, ‘Orthogonoal’),
(196, ‘vedioui.gif’, 151, ‘Linear Algebra Duplicate Row Determinant.mp4’, ‘Linear Algebra Duplicate Row Determinant.mp4’, ‘Duplicate Row D’),
(197, ‘vedioui.gif’, 151, ‘Linear Algebra Eigenvalues of a 3×3 matrix.mp4’, ‘Linear Algebra Eigenvalues of a 3×3 matrix.mp4’, ‘Eigenvalues of ‘)
Hello Mr. Martin,
First, I want to know what type of data you are trying to retrive, whether it is just retrieving 66000 rows with all the columns or is it based on some condition.
Please execute the same query that you are trying to execute from the front end in the T-SQL query analyzer and check whether it is retrieving the required data.
Kind Regards,
Pinal Dave
it is a search suggestions, some sort of intellisense, appears only when letters are typed in the search box.
There are no errors. actually insertion is succesful.
Hello Mr. Marlon,
Please let me know whether you are trying to fetch all the columns of the data or based on some condition.
As a first step, open the table in the SQL database and check whether all the data has been inserted into the table properly.
If so, execute the same query that you are trying to execute from the front end in T-SQL query analyzer and check whether the query fetches the desired data.
Kind Regards,
Pinal Dave
Hi Sir,
I’m new to SQL and I was browsing the web and happen to strolled to your site, I may say you have a very interesting site. I need your assistance regarding the below information because I’m not sure if I’m building this table correct with the included information. I ‘m using SQL Managing Studios 2008 for building/creating a table in DDL (I’m a first-time user). The table has 6 columns (FName, LName, SSN, Salary, SuperSsn…) and 6 rows contain the person information such as fname, lname, ssn…. Anyway is this the proper syntax and procedures for creating a table with the below information? Any assistance would be greatly appreciated, Thank you.
CREATE TABLE [EMPLOYEE]
([id][int]IDENTITY(1,1) NOT NULL,
[FName][varchar] (25) NOT NULL,
[LNAME][varchar] (25) NOT NULL,
[SSN][varchar] (11) NOT NULL,
[Salary][varchar](10) NOT NULL,
[SuperSsn][varchar](11) NOT NULL,
[DNo][varchar](6) NOT NULL;
INSERT INTO [EMPLOYEE]
([FName],[LName],[SSN],[Salary],[SuperSsn],[DNo])
VALUES(‘Eilen”MarcAdoo”12345987”65000”12345987”1004’),
(‘Nora”Watkins”45123987”35500”12345987”1001’),
(‘Mary Anne”Lazarro”32145878”60000”12345987”1003’),
(‘Clara”Thomson”03412344”53000”12345987”1003’),
(‘Raymond”Thomson”02932455”22200”12345987”1002’),
(‘Ziggy”Gravellese”45698755”35000”12345987”1002’);
Why did you use varchar datatype to store salary? Use proper datatypes such as numeric
varchar datatype accept both integer & String..If U r using Varchar Datatype in Data You may to store String Values & integer Values….Then Why are Suggested To Rick “You are Must Use to store the salary using Integer Datatype”….Sir Explain This Confusion.
When you store numbers in varchar datatype, you cannot do any arithmetic calculations until you convert to number
This is Pradeep, working as a Application Developer in Chennai, Your Blog is Nice….
hello mr.Pinal Dave
I have a program with some client(win app) and I want to integrate their data into one database. I mean I have a central databse. how can do that?
You need to import data to the respective tables
Hi,
I want to copy records from one database to another database which already contains some records.
So while inserting records if record already exists then update the existing to new one else insert it.
How to do this in SQL server 2008 express/professional.
General logic
update t
set col=s.col
from source as s inner join target as t
on s.keycol=t.keycol
insert into target(col_list)
select col_list from source as s
where not exists(select * from target where keycol=s.keycol)
I think this is even easier ;-)
INSERT INTO MyTable
(FirstCol, SecondCol)
values
(‘First’ ,1)
,(‘Second’ ,2)
,(‘Third’ ,3)
,(‘Fourth’ ,4)
,(‘Fifth’ ,5)
GO
Yes provided that the version is 2008 or more
Hi Dave,
I would like to insert the following query around 50,000 times with different MDN, how to write the PL/SQL query, please?
Here MDNs starting value is – ‘8000000000’ and ends with ‘8000050000’.
INSERT INTO “ABC”.”MDN_MAP” (PART_KEY, VERSION, MDN, CARRIER_ID, EFFECTIVE_DATE) VALUES (‘20101116′, TO_DATE(’16-NOV-10’, ‘DD-MON-RR’), ‘8000000000’, ‘322222’, TO_DATE(’16-NOV-10′, ‘DD-MON-RR’));
Please do the needful asap.
Thanks,
-Vishwa
This is for MS SQL server. For Oracle questions, post at oracle sites
I wanted to insert n number of data in table
please could any one let me know the query/script
Where is the source? If it is in a text file use bcp or bulk insert
Thanksssssssssssssss!!!!
Hi Pinal,
i have got a list say list skills to be entered into a table say ‘skills’. Each skill should be added as a new record in the table.
The number of skills can vary.
I want to use stored procedure for this task. The list will be passed as parameter in the Stored Procedure and the Stored Procedure should retrieve each skill and insert a new record.
I repeat again the number of skills can vary and thus number of records to be inserted can vary.
Please can you help me out with this.
hi every one thanx to all who helped :-)
Hi Pinal,
When i Write this query in SQL server 2005 am getting error like “incorrect syntax near ‘ALL’
Please help .
Post the exact code you used
One little contribution do Dave’s post about efficiency (July 1, 2008).
(I am using VB .NET 2008, Excel 2007, MSSQL 2005)
My program has to insert data from excel (23271 rows x 29 columns) into database. I was experimenting with both of UNION ALL and INSERT INTO with different fragment size and here’s what I found out:
INSERT INTO statement:
size of fragments time(min)
1 3:55
25 3:20
50 3:10
100 3:05
200 3:05
400 3:05
800 3:05
UNION ALL statement:
size of fragments time(min)
5 2:40
25 2:30
50 2:15
100 2:15
150 2:15
200 2:20
300 2:40
500 3:15
1000 >5
First statement experiment was expected(?) because there’s n INSERT INTO statements group in one so as fragment size increases timing tends to about 3 minutes (3:05).
Second statement experiment shows that most efficient fragment size is about 100+/-50 for the same data size as in experiment.
So, for most data sizes UNION ALL statement with specific fragment size should be more efficient than INSERT INTO.
Anyone?
Hello Pinal Dave,
I am a s/w engg in Mohali.. and this site is really helpful.
thanks for writing
Regatds.
This post is very useful. I’m wondering though how can I use a CASE statement to determine which records will be inserted in my one INSERT statement? I’m trying to insert multiple values into a table variable using a CASE statement and then want to be able to select all the values that were inserted. I included a snippet:
DECLARE @YrTbl TABLE
(Yr VARCHAR(7))
SET @TodayMn = MONTH(getdate())
SET @TodayYr = YEAR(getdate())
SET @2Years = YEAR(getdate())-2
SET @LastYr = YEAR(getdate())-1
SET @Yr1 = @2Years + ‘-‘ + RIGHT(@LastYr,2) — ‘2009-10’
SET @Yr2 = @LastYr + ‘-‘ + RIGHT(@TodayYr,2) — ‘2010-11’
— I know the syntax is not correct, but this is what I want to be able to do:
INSERT INTO @YrTbl SELECT CASE WHEN @TodayMn in (1) THEN SELECT @Yr1 UNION ALL SELECT @Yr2 end
SELECT Yr from @YrTbl
What I’m trying to get from my SELECT:
2009-10
2010-11
How can I do this?
INSERT INTO @YrTbl SELECT CASE WHEN @TodayMn in (1) THEN @Yr1 END UNION ALL SELECT @Yr2 end
SELECT Yr from @YrTbl
Thanks madhivanan. Sorry I didn’t mention it earlier, but the problem is, I need to keep it within the CASE so that I can express what to do if the @TodayMn is not in 1. So in addition to the
WHEN @TodayMn IN (1) THEN…
I also need
WHEN @TodayMn IN (2) THEN…
(3), (4), etc
You cannot have multile values in case expression. However you can have it inside a single quote