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
Sir,
I am in a situation where i have to fill multiple records against a single number for example if you purchase many things on a single bill in a mall. i am using two table for that first one is taking the master table where we store bill specific details like bill no., customer name,address , mobile number etc. in second table i am storing all the material purchased against that bill no. Now, i am taking all the records in array and then sending it to the table. Is there any other way to send it to the table?
@Smit,
If you give some sample input and expected output, that would help us understand what your requirement is.
Data speaks better than explanation.
~IM.
Dear All,
Hi,
i want to ask u alls please tell me how i make insert and update query whit inner join so please tell me
thanx
pop show
Update t1
set col1=t2.col1,
.
.
.
from table1 as t1 inner join table2 as t2
on t1.keycol=t2.keycol
i have 3 tables:
Measurement (MeasureID,Name)
Customer(CustID,Name)
CustMeasureLink(CustID,MeasureID,Value)
I want that whenever new measurement is added..
then in CustMeasureLink new records with that MeasureID should be added paired with existing CustID’s..
eg. if there are 3 records in customer table…now if MeasurementID 2 is added in measurement table than….
CustMeasureLink would be…
1,2,SomeValue
2,2,SomeValue
3,2,SomeValue
Please help me how to do that in single insert statement…..
Thanx….
i have 3 tables:
Measurement (MeasureID,Name)
Customer(CustID,Name)
CustMeasureLink(CustID,MeasureID,Value)
I want that whenever new measurement is added..
then in CustMeasureLink new records with that MeasureID should be added paired with existing CustID’s..
eg. if there are 3 records in customer table…now if MeasurementID 2 is added in measurement table than….
CustMeasureLink would be…
1,2,SomeValue
2,2,SomeValue
3,2,SomeValue
Please help me how to do that in single insert statement…..
Thanx….
insert into CustMeasureLink(CustID,MeasureID,Value)
select CustId,2,somevalue from Customer
table1 contains one column that is date
i want to alter the table and add column month
& extract the month for the date column & update the table
date is in this format : 02/18/2010
the new column month must contains 02
or if possible ‘FEB’
You dont need a seperate column
Use derived column in the SELECT statement
SELECT date_col, month(cast(date_col as datetime)) as [month] from your_table
Also, you should always use proper DATETIME datatype to store dates. Also make sure to read this blogpost
but i need another column for further requirement…
i had return below query but the inner query retuens multiple values….
update test set test.month1 =(select MONTH(b.osdate) from test b where test.osdate=b.osdate)
update test set month1 =MONTH(cast(b.osdate as datetime))
I want to create a record from existing record but want to change the two filed value.
insert into table(columns)
select col1,col2,2,’test’,…. from table
I have a query,
I try your query with both UNION ALL and UNION and data inserted properly. Is there any difference between them with insert clause?
There are lot of differences between the two. Try with duplicate values and see the result. Also make sure to read this blog post
Thanks for the tip – and to think, only almost EVERY OTHER platform has been doing it ALL WRONG.
aka –
INSERT INTO TABLE (COL1, COL2, COL3) VALUES
(1, 2, 3),(4, 5, 6)…etc
Thanks Micro$oft for providing a longer more tedious method!
Note that this will work from version 2008 only
Hi.. Pinal,
I need to insert data in two tables with 1 to many relations ship from front end. What I am doing is In Table1 single row is inserting but in Table2 multiple records are inserting with single Stored procedure.
Here is the code for stored procedure:
BEGIN TRAN
BEGIN try
Insert into Table1 ()
values ()
END try
BEGIN CATCH
ROLLBACK TRAN
END CATCH
Declare @Identity numeric
Select @Identity =@@IDENTITY
BEGIN try
Insert into Table2 ()
values ()
END try
BEGIN CATCH
ROLLBACK TRAN
END CATCH
Commit Tran
Kindly suggest your reviews on above procedure. as I am new to SQL
hi am goutham
i want insert per day only 10 values in a table by using
stored procedure in
sql sererver
Ony method I can think of is to use a trigger
Have a datetime column in the table with the defualt value of getdate(). In the after trigger write this
If exists(select * from table where datecol>=dateadd(day,datediff(day,0,getdate()),0) and datecol<dateadd(day,datediff(day,0,getdate())+1,0) )
rollback
Ony method I can think of is to use a trigger
Have a datetime column in the table with the defualt value of getdate(). In the after trigger write this
If (select count(*) from table where datecol>=dateadd(day,datediff(day,0,getdate()),0) and datecol10
rollback
The code should be
If
exists(select count(*) from table where datecol>=dateadd(day,datediff(day,0,getdate()),0) and datecol10
rollback
Here is my code currently:
Select c.CaseNo,
ci.indIncomeSrc1 as indIncomeSrc1,
ci.indIncomeSrc2 as indIncomeSrc2,
ci.indIncomeSrc3 as indIncomeSrc3
from XAKTcomSupServ.dbo.Clients c
–Pulls in Client Income Source
Left Join Clients.dbo.ConsumerIncome ci
on c.CaseNo = ci.cssID_fk
Here is the Output:
1001652
NULL NULL NULL 1802630
13 16 16 1802766
NULL NULL NULL 1804297
14 16 16 1804706
NULL NULL NULL 1805008
10 16 16 1805427
14 16 16 1806114
NULL NULL NULL 1806171
NULL NULL NULL 1806200
14 16 16 1806262
14 15 16 1806421
What I need is if any of the columns match not to be included in final output.. ie
As in column 2 I only want a final output of 13, 16 not
13, 16, 16.
Thank for the help.
hi
can any help me getting date in month and year in one column i.e 07-2010 (mm-yyyy)
This is the formation issue which should be done in your front end application. Refer this
If you dont use any front ends, use
select right(convert(varchar,getdate(),103),7)
is there any other option than concat()
What did you mean by concat?
ohhhh (smile)
i mean
select convert(varchar ,MONTH(GETDATE()),111) +’-‘ + convert(varchar ,year(getdate()),111)
anyway thanx madhivanan i got my soln
Ok. You should do formation at front end application.
Otherwise this is another simple method
select right(convert(varchar(10),getdate(),105),7)
from u ofcourse
It’s not pretty but here how I finally worked: (I am using SQL 2000 so 2008 option are not possible)
SELECT ins.CaseNo,
ins.indSrc1
Into #tmp
FROM #IncomeSource ins
SELECT ins1.CaseNo,
ins1.indSrc2
Into #tmp2
FROM #IncomeSource ins1
Where ins1.indSrc2 not in (ins1.indSrc3)
SELECT ins2.CaseNo,
ins2.indSrc3
Into #tmp3
FROM #IncomeSource ins2
Where ins2.indSrc1 not in (ins2.indSrc3)
–then in the next select statment
select tp.indsrc1+ ‘, ‘
+ t2.indsrc2+ ‘, ‘
+ t3.indsrc3 AS IncomeSource
HOW UPDATE ONE TABLE IN SAME DATA IN EVERY ROWS AND NO USE ANY KEYS THIS TABLE . WHAT WILL BE UPDATED ONE ROW DATA . AND NO ANY CHANGES ANY ROWS ONLY CHANGES PARTICULAR ONE UPDATED ROW HOW IT’S POSSIBLE DO THIS. PLEASE SEND QUERY
Can you give us more informations with sample data with expected result?
A much simpler solution is to do like this:
INSERT INTO table_name (col1, col2, col3, …) VALUES
(val1, val2, val3, …),
(val1b, val2b, val3b, …),
(val1c, val2c, val3c, …),
…
(val1n, val2n, val3n, …);
Each set of parentheses is another record to add.