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 pavanich,
Surround group with []s, like so:
select * from table where name like ‘a%’ and [group]=’g’
i want to insert in one table and update in another table with one query how
Hello,
Please I need advice.
SQL Server 2005. I have a table consisting of 200 fields with a primary key(auto-increment). I need to be able to duplicate via a stored procedure a specific row in the table identified by primary key value i.e. insert a record form that table into the SAME table …. the primary key of the inserted record so be incremented and I would need to change to of the field values in the inserted record (i.e. duplicating a row in a table with a primary key(auto increment). What is the best way to do this… one suggested writing to temp table then re-insert to original table. I have had some issues with this is there another method? Please not that other users may try and access the orginal record during this insert process. Can you give me example syntax and comments.
Thank You!
hi all,
i wanted to know whether a insert statement can contain where condition in it.
Why do you need this?
Can you give us more informations on what you want to do?
Hi Pinaldave,
This site is awesome. I am a beginner and got stuck at some point.
My query is i have 2 tables (tblbuilding & tbltype) with columns as building, buildingid, type, typeid.
I have one main table tbltechen2 with columns group,seats,room,buildingid, typeid.
My question is when i submit the survey page with building and type specified, i should get the buildingid and typeid to be entered into tbltechen2 getting the buildingid and typeid from tblbuilding and tbltype respectively..
Can you tel me the query for inserting into tbltechen2.
Thanks a tonnnnn,
karthik
hi i am trying to insert data for 5 lines its giving following error,pls suggest me
INSERT INTO c2 (type, invtype)
SELECT ‘First’ ,’a’
UNION ALL
SELECT ‘Second’,’b’
UNION ALL
SELECT ‘Third’,’c’
UNION ALL
SELECT ‘Fourth’,’d’
UNION ALL
SELECT ‘Fifth’,’e’
ERROR: FROM Keyword not found where expected
please suggest me for this response
Becuase you teied this code at ORACLE where you need to use FROM DUAL like
SELECT 5 FROM DUAL
Chiranjeevi, did you try giving semicolon (;) at the end?
Hi Dave,
I have sql server 2000. It runs very slow. I checked in SQL Server – Current Log and it shows that database is backed up after every 2/3/4 hours. However, I checked the Database maintenance plan. There is no single plan wherein backup is scheduled on hourly basis. Is there any place else where backup could have been scheduled and I’m not able to track? If not then could you please let me know why am I able to see the messages in SQL Current Log?
Regards
Shreyas
Sir
I have the following procedure
————————————————-
declare @xmlData xml
set @xmlData = ‘
select …..
from FROM @xmlData.nodes (‘/NewDataSet/Table’) AS p(nref)
————————————————-
it takes 6 seconds
but when I write
————————————————-
declare @xmlData xml
set @xmlData = ‘
Insert into tempTable
select …..
from FROM @xmlData.nodes (‘/NewDataSet/Table’) AS p(nref)
————————————————-
it takes 10 minutes ?????
this is a pretty neat trick. espcially in my java program where executing each insert equals one call to the executeUpdate method.
Is Union All works with Oracle
Yes it will work
Have you tried it?
I need to find out ,No of row inserted into the table like
SQL:> insert into table where some condition
Supppose if i fire this Query i need to know no of records inserted into table. Using java
Immediately after running the query, run this
SELECT @@ROWCOUNT
Hi All,
Using following code I am getting recods month wise
but the months are not coming in ascending order.
Select DATENAME(month, sportendt) as eventdt from sportsnews where sport_type=’Cricket’ and sportendt>=getdate()-1
GROUP BY DATENAME(month, sportendt)
HAVING count(*) >= 1
Help me
Dnyanesh
Use
ORDER BY cast(eventdt +’ 2000 as datetime)
Hi Shreyas,
First check the indexes status whether the indexes are fragmented or not and what is the scan density?
rgds
hi Pinal…I am trying this UNION ALL trick to insert more than one row in the table on informix…But it does not seem to be working…. :(
Hi Rama Krishna,
Thanks for your reply.
Did you mean that the scanning density or index fragmentation could be the cause of why it is showing as “Database backed up” in sql current log?
Thanks again.
Awaiting reply
Thanks rama krisha.
Is that relevant to displaying “database backed up” every 3 hrs. in sql current log?
shreyas
Is there anyway to do multiple inserts as in mysql?
e.g.
INSERT INTO sometable VALUES (
(a,b,c),
(c,d,e),
(e,f,g)
)
thanks!
This is possible only from the version 2008 of SQL Server
i want sum thing different
insert into table1 ( c1,c2) values ( ‘1’,’2′)
insert into table2 ( c1,c2, c4) values ( ‘1’,’2′,’4′)
tell me i want to to excute abouve in same query and also once ne query fail they both roll back/
plz tell me how to do dat
Try
begin transaction
insert into table1 ( c1,c2) values ( ’1′,’2′)
insert into table2 ( c1,c2, c4) values ( ’1′,’2′,’4′)
If @@error0
rollback
else
commit
I am trying to insert multiple rows in a database using the “select” and “union all” statements. This is working great.
I am also using the @@identity + (variable – 1.toString) as my primary key.
I am getting a “primary key constraint error” in SQL Server. “Duplicate key not allowed”
I have tried to set Identit_Insert = On and then Off at the end of the query but am still getting this error. Does anyone know how I can fix this problem?
(I am doing this in order to migrate from mySQL to SQLServer) and was using last_Identity() instead of @@identity previously. I thought if I changed to the sql server naming convention to get the last id entered and set the Identity_insert to on/off it would work but it’s not working.