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
Well i liked ur site . i will soon post my probs regarding sql server 2005
For those using ORACLE:
Oracle NEEDS a FROM in the SELECT statement, so just use a FROM DUAL to make it work with ORACLE.
Example:
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT ‘First’ ,1 FROM DUAL
UNION ALL
SELECT ‘Second’ ,2 FROM DUAL
UNION ALL
SELECT ‘Third’ ,3 FROM DUAL
UNION ALL
SELECT ‘Fourth’ ,4 FROM DUAL
UNION ALL
SELECT ‘Fifth’ ,5 FROM DUAL
;
If i use update command for particular table my condition statisfy for example three rows. I dont want update three rows, so i want update Row wise if one row updated means i want break.
Hello,
I need help please send to my mail
Table
year,Sname,FName,RollNo,Class,paidDate,paidAmount
here i want to get grand total with all columns in the table
it should be displayed under Paidamount
please reply as soon as possible
thanking you
Hello,
I need help please send to my mail
Table
year,Sname,FName,RollNo,Class,paidDate,paidAmount
here i want to get grand total with all columns in the table
it should be displayed under Paidamount
please reply as soon as possible
thanking you
Try
select year,Sname,FName,RollNo,Class,paidDate,sum(paidAmount) as grand_total from your_table
group by year,Sname,FName,RollNo,Class,paidDate
Hi Pinal,
The solution was very Useful….
Thanx
USE master
GO
INSERT INTO emp (ename, eid)
SELECT ‘First’ ,4
UNION ALL
SELECT ‘Second’ ,6
UNION ALL
SELECT ‘Third’ ,7
UNION ALL
SELECT ‘Fourth’ ,8
UNION ALL
SELECT ‘Fifth’ ,9
GO
Hi. Is there a way to do multiple insert statements while ignoring duplicates (varchar type) if the id is auto_increment?
Nice blog btw – thanks for sharing your knowledge.
Thanks!… this is an interesting solution
Hi mate.
First off, this is a fine solution to reduce network load if you have a lot to insert.
But suppose you never know how much needs to be inserted, eg somewhere between 1k and 25k records. You write a method to make a statement that inserts all the records at once (using your neat trick). Will it still work if you insert 25k records in one statement (assuming records of considerable size)? Does SQL Server impose a limit in this context? This should be documented, should it not?
Also, will SQL Server choke on making a ‘temporary table’ before inserting it into the target table if you have a lot of records to insert? I wonder what the slow down is in terms of records… Maybe SQL Server optimizes this and inserts the records into the target table at once?
Regards,
TT (BE)
sir,,,i have two different values but column name is same and where clause column name is same…so how can i update record with it….
ex…
update tblName set Same_clmnName=’var1′, same_clmnName=’var2′ where differ_clmnName = ‘001’ . while variable value ill be one at a time….means either var1 having any value or var2 having any other value Plz help me….
zaved warsi
query 1-
update tablename set clumName = ‘var1’ where id = ‘001’
query 2-
update tablename set clumName = ‘var2’ where id = ‘001’
i want to update record depending upon variable value if exits then update column name otherwise update var2 value…
like this…
UPDATE feedbacktbl SET `fullname` = CASE WHEN `sn` =2 THEN “za” WHEN `sn` =3 THEN “ma” END ..
but here is different sn number……i want same sn number and same coulumname ‘fullname’ but different variable name…i hope u got it…
haha, Good Blog for me, Fresh Uesers.
thank you!
SQL Integration Services package is the best way to import multiple rows (Provided the number is significant, otherwise the overhead of package execution can be an overkill) into a Sql Database.
Happy Coding
Nash Vyas
sir
i want to insert values entered by user into the textbox to the table into database sir please tell me the command.
plz check this code
string insertSql;
insertSql = “INSERT INTO dcfuture.FIRST(username,firstname,secondname,age,rollno,email)”;
insertSql +=”VALUES(‘” & lastnametextbox.Text& “‘,'” & agetextbox.Text & “‘,'” & rollnotextbox.Text & “‘,”;
insertSql +=”‘” & emailtextbox.Text & “‘)”;
You have missed to pass values for the column names username and firstname
Hi All,
I want match where condition record in select statement,
suppose where condion have 3 record and i want to match 1
record and find result,and vice versa
my query is below
Select max(groupid) as Groupid,title from incidenthistory where title in (select Title from incidenthistory where GroupId=’2′ Group by Title) and assigneeid is not null
group by title
In above query if title come in groupid=4 then
it should not come when using query groupid=2
Any Help Appreciated to me……..
Thanks In Advance
Hi,
I want to insert values in a table from 2 different tables which don’t have any common field
say all columns of table 1 and then a single date field from table2
can u help plzzzzz..
If you use versions starting from 2005,
Insert into target_talbe(column_list)
select t1.col,t2.col1 from
(
select *, row_number() over (order by (select 0)) as sno from table1
)
as t1
left join
(
select *, row_number() over (order by (select 0)) as sno from table2
)
as t2
on t1.sno=t2.sno
union all works great with up to 2500 records ..
if i use more the query returns -1 as number
of rows affected.
I am using sql 2005
Please help
Great web page:
I’m trying to display rows of data that have duplicate records side by side with the miles increasing.
SELECT “CapDer”.”cder_capcode”, “basetableversions”.”TV_PubDate”, “FutureResidual”.”fr_ID”, “FutureResidual”.”fr_mileage”, “FutureResidual”.”fr_6″, “FutureResidual”.”fr_12″, “FutureResidual”.”fr_18″, “FutureResidual”.”fr_24″, “FutureResidual”.”fr_30″, “FutureResidual”.”fr_36″, “FutureResidual”.”fr_42″, “FutureResidual”.”fr_48″, “FutureResidual”.”fr_54″, “FutureResidual”.”fr_60″, “FutureResidual”.”fr_66″, “FutureResidual”.”fr_72″, “FutureResidual”.”fr_78″, “FutureResidual”.”fr_84″, “NVDPrices”.”PR_Basic”, “NVDPrices”.”PR_ModifiedDate”, “basetableversions”.”TV_PubSeq”
FROM ((“PUB_CAR”.”dbo”.”CapDer” “CapDer” INNER JOIN “PUB_CAR”.”dbo”.”FutureResidual” “FutureResidual” ON “CapDer”.”cder_ID”=”FutureResidual”.”fr_ID”) INNER JOIN “PUB_CAR”.”dbo”.”NVDPrices” “NVDPrices” ON “FutureResidual”.”fr_ID”=”NVDPrices”.”PR_Id”) INNER JOIN “PUB_CAR”.”dbo”.”basetableversions” “basetableversions” ON (“FutureResidual”.”fr_pubdate”=”basetableversions”.”TV_PubDate”) AND (“FutureResidual”.”fr_pubseq”=”basetableversions”.”TV_PubSeq”)
WHERE (“basetableversions”.”TV_PubSeq”=1051) AND (“FutureResidual”.”fr_mileage”>5 AND “FutureResidual”.”fr_mileage”15 AND “FutureResidual”.”fr_mileage”<21)
ORDER BY “FutureResidual”.”fr_mileage”
is it possible,
Any help appreciated.
Steve.