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
Hi Pinal,
Is it possible to insert multiple records by passing parameters?
if so, I requesting you to write me the example and also post mail to my email id ASAP.
eg. same above example to pass parameter values through a function. is it possible? please write in detail,
To understand in detail:
let’s say i’m reading inputs values from a column and saving into a DB table.(VBA perspective) i want to utilize effectively the SQL Server Database rather frontend functionality code.
Regards & thanks
Kameswararao
I get the foll error :
INSERT INTO [mxmc_db].[dbo].[AuditTrail] (Refid,jobNumber)
Select 01,01,01
Union ALL
Select 01,01,01,01,01,01,01,01,01,01
Union ALL
Select 01,01,01
Union ALL
Select 01,01,01,01,01,01,01,01,01,01,01,01,01
go
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
It is because you were trying to insert data to two columns from three values. You need to include third column in the INSERT Statement
hi,
how can I find the username that starts with minimum 3 chars.Actually,I tried with like,but like is showing the rows with one character only,but how can I get username that starts with minimum charcters.
select columns from your_table
where username like ‘[a-z][a-z][a-z]%’
I just want to thank pinaldave for the original tip above, then thank Sumeet for his improvement, and I will also thank MrSnipey for his suggestion regarding how to insert data from a text file via the bulk method, although I haven’t tried that one out yet.
Anything that can reduce the tedium of copy & paste is highly welcome. I was also positive that there must be a better way, as I could not imagine that experienced coders would suffer the infinite repetitiveness of the alternative of copy & paste. Most sites I found when I Googled the problem dealt with other issues than the solution here, which is what I was of course looking for. Thanks again all around!!!
Yours,
makaroo
Hi Guys,
I want to combine something like 6O excel templates of the same structure into one template. I ‘ve tried ”SELECT…UNION ALL SELECT… ” with 3 templates which worked fine. But with 60 templates do I need to write 60 or so SELECT…UNION ALL SELECT… statements? What is the easy way out?
Help out please.
Regards,
Felix
hi ,
I get error
insert into my_dept (dept_no , dname , location)
select( 1,’research’ , ‘blore’)
union all
select ( 2 , ‘ad’ , ‘mysore’)
union all
select ( 3 ,’marketing’ , ‘hyderabad’)
union all
select ( 4 , ‘ hr’ , ‘mumbai’)
go
;
please help me , i want to know this command
Advance thx ,
rani
insert into my_dept(dept_no , dname , location)
select 1,’research’,’blore’
UNION ALL
select 2,’ad’,’mysore’
UNION ALL
select 3,’marketing’,’hyderabad’
UNION ALL
select 4,’hr’,’mumbai’
go
rani, I believe your error is occuring because of your last semi-coln ‘;’
no ‘;’ is required after the ‘go’ statement
Cheers.
Simmo
Hello,
I need send all SELECT clause for parameter, because my INSERT is into store procedure. But i have problem with ” ‘ ” in varchar values. How to send this sentece as parameter?
Or
how to fix this code? if i want use variable:
declare @teste varchar(255);
declare @teste2 varchar(255);
set @teste = ‘INSERT INTO MyTable (FirstCol, SecondCol) ‘;
set @teste2 = ‘SELECT ‘First’ ,1
UNION ALL
SELECT ‘Second’ ,2
UNION ALL
SELECT ‘Third’ ,3
UNION ALL
SELECT ‘Fourth’ ,4
UNION ALL
SELECT ‘Fifth’ ,5 “;
exec (@teste + @teste2);
thanks
You need to double each single quotes
Refer this to understand how single quotes work in SQL Server
please help
how do i insert e records records at a time suppose if i dont know no of records to be inserted. it depends on the selections of records.i mean inserting no of records changes each time.how do i insert by passing the variables.if the values is not hard coded.
thank you,
Spoorthi
insert into company (‘EMPNO’, ‘ENAME’, ‘JOBS’, ‘MGR’, ‘HIREDATE’, ‘SAL’, ‘COMM’, ‘DEPTNO’)
SELECT ( 4587, ‘STANLEY’, ‘TEAM MEMBER’, 8745, ’28-JAN-08′, 5000, 2100, 10)
UNION ALL
SELECT ( 4787, ‘THOMPSON’, ‘TEAM MEMBER’, 5825, ’18-JULY-05′, 2500, 1100, 30)
UNION ALL
SELECT ( 2584, ‘ROGER T’, ‘TEAM MEMBER’, 8745, ’28-JAN-08′, 5000, 2100, 10)
GO
sir i have made this statement the issue is that it says that the select statement is missing . it is unable to insert the muliple rows.pls tell me how to work on this statement .
You dont need braces in SELECT statement. Remove them and try
Hello
I have created three dimension table and one staging table want to load data into fact table .Getting an error
My Insert statement is
Insert into dbo.facthurricane (DIM1, DIM2,Nameid,WIND,PR)
select dim1 from dbo.locationD
SELECT DIM2 from dbo.DIMTIME
SELECT NameID from dbo.NameD
SELECT WIND from dbo.StagingHurrincane
SELECT PR from dbo.StagingHurrincane
MY select statements are fine but some INSERT is not working
Any help
-Praveen
I am very new to SQL world..
It should be
Insert into dbo.facthurricane (DIM1, DIM2,Nameid,WIND,PR)
select dim1 from dbo.locationD
union all
SELECT DIM2 from dbo.DIMTIME
union all
SELECT NameID from dbo.NameD
union all
SELECT WIND from dbo.StagingHurrincane
union all
SELECT PR from dbo.StagingHurrincane
I realise that you want to insert to different columns by taking values from different tables. My previous solution is not correct
You need to join all the tables based on some common columns and take correspoding columns like
Insert into dbo.facthurricane (DIM1, DIM2,Nameid,WIND,PR)
select t1.dim1,t2.DIM2 , t3.NameID , t4.WIND ,t4.PR from dbo.locationD as t1
inner join dbo.DIMTIME as t2 on t1.key_col=t2.key_col
inner join dbo.NameD as t3 on t2.key_col=t3.key_col
inner join dbo.StagingHurrincane as t4 on t3.key_col=t4.key_col
hai dave how r u ……
i visit your web site it s really i a good enclopedia of SQL server.
i am facing one problem in sql 2000 server actually in my database iwant to see Roll
No and Degree No in order by Rollno .
it is not showing my query is like
select * from student where Uid=’A’ order by RollNo.
select * from student where Uid=’A’ order by DegreeNo.
some time it is working in sql query analyzer .but not working in jsp Script result.
Waiting for your Reply
ASAP
Manoj
HI , i have a problem
could ‘u help me please :)
y have 2 “select” one of those with IDENTITY and the SLQ7 do not allowed the UNION… for example
select name, age, ID = IDENTITY (int)
Into #tempTable
from Table1
where …….
UNION
Select name, age, 0
from Table2
why i want do that? … becouse …i have to UPDATE th TempTable but i don’t know whay don’t bellow to me do that
Try
select name, age, ID = IDENTITY (int)
Into #tempTable
from (
select name, age from Table1
where …….
UNION
Select name, age from Table2
) as t
Hi Pinal,
Is there a way that you can insert values into multiple tables at one go. I am a beginner and I was writing a procedure, so got this question. Your input is greatly appreciated.
Thanks
Is there any Examples to INSERT or (UPDATE) values in two different tables by writing only one statement in SQL Server 2000.
Case example: Say I have a THREE values. I want to INSERT two of the values in TABLE_ONE and the one value on TABLE_TWO.
INSERT is more important to me but as well UPDATE
Hello Can you please tell me how I can INSERT multiple rows using checkboxes?
Your blog is very usefull!
Can u explain the difference between Union and Union All?
Sumeet Bhasker stated that:
INSERT INTO YourTable (FirstCol, SecondCol)
VALUES (’First’ , 1) , (’Second’ , 2) , (’Third’ , ‘3′), (’Fourth’ , ‘4′) (’and so on’) ;
this is used when number of row is defined. What if the number of row is dynamic? Can someone help me on this? Thank You!
hi pinal,
i have tried the option u have provided as below-
INSERT INTO mytable(id,name)
SELECT 1, ‘pizza’
UNION all
SELECT 2, ‘donuts’
UNION all
SELECT 3, ‘milk’;
but every time i get the error like-
ORA-00923: FROM keyword not found where expected
i also tried a another option-
INSERT INTO mytable(id, name) values(1, ‘pizza’),(2, ‘donuts’),(3, ‘milk’);
but i get the error like-
ORA-00933: SQL command not properly ended
Please, help me, tell me what wrong i m doing.
In ORACLE you need to use DUAL
INSERT INTO mytable(id,name)
SELECT 1, 'pizza' FROM DUAL
UNION all
SELECT 2, 'donuts' FROM DUAL
UNION all
SELECT 3, 'milk' FROM DUAL;
Hi, i have a one proble it is make me made and it’s happen so many time
i am using TDBGRID 7. OLDB and XArrayDB
i am using loop like that
For M = 0 To Myarray.UpperBound(1)
If Myarray(M, 1) “” Then
‘Insert Command
End If
Next
it is inserting twise a same record, Why ? I don’t have any idea about that if any budy know exact solution for that pls. reply on my email id
i have a one solution that is Primary Key but i can’t keep Primary key For Item Code . it can be dublicate saling same item.
now a am going to do ID Of Record No and ItemCode (ID + ItemCode) as a Primary key but
still question is hungup why it is happening like that
i have a dought some time network slow that time happning
but i am not sure about that
Pls. any budy know reply me.