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 want to know on how i can insert data to sql row from a textarea??the text area contains multiple names and i would like to insert it in each row with the details of it…help me..thank you..
Mr. Dave,
This is related to using Distinct clause in a Subquery.
I am converting over 100 attributes from Access Database table to SQL Server 2000, using Asp.Net 1.1 and Vb.Net.
The Access table has duplicates and I am trying to eliminate duplicates. The key is Alien_Number.
The query looks like this:
SELECT *
FROM tblClients T1
WHERE (ALIEN_NUMBER IN
(SELECT DISTINCT RTrim(Alien_Number)
FROM tblClients T2
)
ORDER BY ALIEN_NUMBER
I am still getting duplicate Alien Numbers.
I would appreciate if you have any suggestions.
Thank you for your time.
Regards
Lakshman
hi lakshman,
it is very appropriate if you remove the duplicate data from the source and then run the insert into statement.the same problem is resolved using the above said statements.
regards,
Zaim Raza.
Hi sir,
I have Two tables one is temprory table #temp .
and i want to update those records which are in both table and insert those from # temp table which are not in main table
i have tryed but .it only upades
if exists (select PId from payoutdetails where PayoutNo=@PayoutNo and PId in(select SponserId from #p1 ))
update payoutdetails set ClubIncome=@CalAmountForPlan1 where PId in ( select SponserId from #p1 )
else
insert into payoutdetails (PId, SmallClubIncome) select SponserId,@CalAmountForPlan1 from #p1
but it doesnot insert any record
thanks
any help
@Santosh
If you are using 2008, you might want to look at the MERGE statement.
Anyway, no reason to use IF:
update payoutdetails set ClubIncome=@CalAmountForPlan1 where exists(select PId from payoutdetails where PayoutNo=@PayoutNo)
and PId in(select SponserId from #p1)
insert into payoutdetails (PId, SmallClubIncome) select SponserId,@CalAmountForPlan1 from #p1
where NOT exists(select PId from payoutdetails where PayoutNo=@PayoutNo)
and PId NOT in(select SponserId from #p1)
Hi,
I have used the above query to insert multiple rows in single statement. But now the question is how do it get @@identity of each row inserted back. the OUTPUT clause is for SQL 2005. I am using SQL 2000.
Please help me.
Thanks!!
Perfect example thanks, my SQL INSERT disease got remedied.
Hi,
Can anyone please tell me when we use ‘ (single quotes) in an insert query to add the new values to the table and when we should not use any quotes (like inserting NULL)?
What does quotes do exactly? pls explain..
Thanks!
@Deb
single-quotes are used for literals. If there are to single-quotes, it want to insert a zero-length string. NULL means there is no value there at all.
Hi Pinal Dave
I am using access database but i am try to search a table where i should pick the firs t record and insert it into another table and be able to update the record with the next 4 records in the first table.
I can insert but my update is giving an error. this a a bit of my code. Pls help i am using a for loop to get the next 4 records, but i also though of rowcount but do not know how to use it .
gcon.OpenConnection()
cmd.CommandText = “select * from english where centcode like ‘” & centre & “%’ and subjcode like ‘” & Subjcode & “%'”
cmd.Connection = gcon.con
dr = cmd.ExecuteReader
Dim Subjcode1 As String
While dr.Read()
i += 1
For i = 1 To CandCount
CentCode = “”
If i = 1 Then
CentCode = (dr(“centcode”))
CandNo = (dr(“candno”))
Subjcode1 = (dr(“subjcode”))
‘Abs = (dr(“abs”))
Yr2 = (dr(“y2mrk”))
Yr3 = (dr(“y3mrk”))
‘mQuery = “Insert into casstbl(centcode,subjcode,candno1,y2mrk1,y3mrk1,candno2,y2mrk2,y3mrk2,candno3,y2mrk3,y3mrk3,candno4,y2mrk4,y3mrk4,candno5,y2mrk5,y3mrk5)” _
‘ & ” values” _
‘ & “(‘” & CentCode & “‘,'” & Subjcode1 & “‘,'” & CandNo & “‘,'” & Yr2 & “‘,'” & Yr3 & “‘,'” & Subjcode1 & “‘,'” & Yr2 & “‘,'” & Yr3 & “‘,'” & Subjcode1 & “‘,'” & Yr2 & “‘,'” & Yr3 & “‘,'” & Subjcode1 & “‘,'” & Yr2 & “‘,'” & Yr3 & “‘,'” & Subjcode1 & “‘,'” & Yr2 & “‘,'” & Yr3 & “‘)”
mQuery = “Insert into casstbl(centcode,subjcode,candno1,y2mrk1,y3mrk1)” _
& ” values” _
& “(‘” & CentCode & “‘,'” & Subjcode1 & “‘,'” & CandNo & “‘,'” & Yr2 & “‘,'” & Yr3 & “‘)”
cmd = New OleDb.OleDbCommand(mQuery, gcon.con)
cmd.ExecuteNonQuery()
End If
If i = 2 Then
CentCode = (dr(“centcode”))
CandNo = (dr(“candno”))
‘Subjcode1 = (dr(“subjcode”))
‘Abs = (dr(“abs”))
Yr2 = (dr(“y2mrk”))
Yr3 = (dr(“y3mrk”))
mQuery = “Update casstbl set=(candno2,y2mrk2,y3mrk2)where centcode like ‘” & centre & “‘” _
& ” values” _
& “(‘” & CandNo & “‘,'” & Yr2 & “‘,'” & Yr3 & “‘ )”
cmd = New OleDb.OleDbCommand(mQuery, gcon.con)
cmd.ExecuteNonQuery()
End If
gilsygirl
Hi Pinal,
First of all, Excellent work!!!
My query is related to which is based for performance:
i am tryign to insert NEW rows into a table- ard 500 rows per sec
AA) using a dataset appproach- call a ds.update/insert
or
BB)directly giving values as abv proc of insert using union all.
or
CC) passing an array to a stored proc with values i wish to insert.
thnx!
Hi sir,
i am facing one proble plz give me solution
I am using mysql database sir,
i have one table that table one column is there delevey_email in that column i am insert one row like subbu@gmail.com,madhu@gmail.com
but these mail ids are insert two rows another table
this is my problem give me solution sir
This is very nice solution for inserting more than on row in a table …………………….
this is quite amusing! congrats!
i’m wondering if you can use a counter into the sentence cause i have a field that’s not auto_increment and i need it to be inserted in a fashion autoincremental.
I know I can just edit the column and put auto_increment on the atributes but I could use this trick also to insert products names in varchar unique fields. (product1,product2…product9999)…
hope I was clear on my question!!!
Thanks a lot, amazing contribution!
Uhm, if I’m just about to get it right, if the query you provided would be equal to $sql, you’re telling me to copy and paste that as many times i want it to be executed?
That’s not cool. I don’t know if there’s another way, maybe writting even a little iterator in any language would be better in terms of redundance.
How about…
INSERT INTO ”table_dance”
(”id”, “name”, “brasize”)
VALUES
(”0”, “striperella”, “36dd”),
(”1”, “demi moore”, “32d”), …
This will work from version 2008 onwards
hi all ,
i was lacking in syntax , n i got that from here, thank u so much for all
Hi All
I’m programmer from and about to insert 10Lac Records at a time depending on some condition but 2,000 to 4,000 records are getting skipped even though the condition is getting satisfied what could be the reason.
I stored 200 values in an array and that 200 values represent one record.
It is easy to insert ‘n’ record, that i did N no. of times with MAX of 20 values but this is first time im supposed to insert this large no. of values.
So donno how to do…
HELP!!!
how to get the date between two date through a query.
For Example: i need the date between 1/3/2009 and 30/3/2009. Please mail the answer if anybody knows to
SELECT columns FROM your_table
WHERE date_col>=’2009-03-01′ and date_col<dateadd(day,0,'2009-03-30')