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
oh, um the test was run on 5,000 rows.
Hi Pinal,
iam Really very happy to see your website.It is really Healpful for me.
Best of luck
Hi Pinal,
i am trying to do multiple inserts from a table to the same table by changing a field value and i am also have the increment the id ( primary key ) by 1 with each insertion.
How can i increment the recordid with each insertion?
( i should not use identity auto increment )
i tried …
insert into table (id,…..)
select (max(id) + 1), …. from table
ERROR: Violation of PRIMARY KEY constraint ‘PK_IDtable1’. Cannot insert duplicate key in object ‘dbo.table1’.
Hi,
I have written following procedure for inser, where I want to pass table name from application program. But its not working. Same format works for create table.
This procedure giving me error as
Error converting data type varchar to datetime.
my procedure is
Create PROCEDURE [dbo].[InsertSalesOrderDetail]
(
@tablename varchar(20),
@SalesOrderID int,
@SalesOrderDetailID int,
@CarrierTrackingNumber nvarchar(25),
@OrderQty smallint,
@ProductID int,
@SpecialOfferID int,
@UnitPrice money,
@UnitPriceDiscount money,
@LineTotal money,
@rowguid nvarchar (50),
@ModifiedDate datetime)
AS
BEGIN
SET NOCOUNT ON;
exec(‘ INSERT INTO [AdventureWorks].[dbo].[‘+ @tablename +’ ]
values( @SalesOrderID,@SalesOrderDetailID,@CarrierTrackingNumber,@OrderQty,@ProductID,
@SpecialOfferID,@UnitPrice,@UnitPriceDiscount,@LineTotal,@rowguid ,@ModifiedDate ) ‘)
END;
pls help
tell me how to pass table name, if we specify the table name in query, then it works.
Thanks
The same procedure gives error :
Must declare the scalar variable “@SalesOrderID”.
I have declared this variable……
pls help
thanks
I want to know how to insert a multiple rows to a table using ‘&’ symbol in SQL query….Please do help me…
Thanks for sharing valuable tips here. Really it helps lot.
You indeed get a big performance increase, but if you try to use this method where it really counts (like inserting 100k records) it will crash your query compiler and fail to execute. A query simply can’t be that big and complex. Optimizing inserts like this is possible only on mysql.
I have a stored procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_InsertIMEIDetails]
@XMLDOC varchar(8000)
AS
Declare @xml_hndl int
exec sp_xml_preparedocument @xml_hndl OUTPUT, @XMLDOC
BEGIN
INSERT INTO IMEI_IMEIDetails (SerialNumber)
SELECT SerialFrom
FROM OPENXML(@xml_hndl,’//SerialNumbers/SerialFrom’,1)
With (SerialFrom varchar ‘@id’)
END
I want to insert a series that the user will enter from the form the series will start from “000000” and will end on “999999” so we are talking about an insertion of 10 Lakh records in one go and the second problem is I want to store the numbers in the same format as they are getting entered.
Hope I am making sense here, can some one help me on this ASAP.
Thanks!
hi,
i have a little doubt related to insert query.
i have a form in which i have 7 text boxes.
2 text box values stores in table customer.
5 text box values stores in table address.
and i have relate these 2 tables with a foreign key i.e, customer_id is added as new column in address table.
Question?
i want to insert the customer_id into table address as when click the submit button of the form.
and i am inserting all the values from a single Stored procedure first inserting table customer values then tables address values.
so plz tell me how can i do this.
thanks in advance
Thanks a lot! This was very useful and saved me tons of time.
Hi,
Thanks for this tutorials, it’s cool and really helps me a lot in solving my problem.
Hope to know more from you.
Thanks!
Hi
I created below store procedure to update the flag of status to 4 from 2.This will invoke when user hit my web site because written in global.cs file under application_beginrequest event in asp.net.Already so many records will got expired and in status 4 but i need the data of last updated records.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
Create PROCEDURE adStatusUpdate AS
UPDATE [addetails] set ad_status = 4
WHERE (ad_status = 2) AND ( ( DATEADD(dd, ad_validity, ad_publishing_date) <= getdate() ))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Actually i want to expire an ad after validty period get over and also notify user about expiry of their ad using mail.
when i update a query it will display 12 records updated instead Can i get the last updated records like select query so that it will be useful to get their mailids from respective tables or can i store the updated records in another table using insert query
insert into tablename
values (UPDATE [addetails] set ad_status = 4
WHERE (ad_status = 2) AND ( ( DATEADD(dd, ad_validity, ad_publishing_date) <= getdate() )) )
or
select * from aduser (UPDATE [addetails] set ad_status = 4 WHERE (ad_status = 2) AND ( ( DATEADD(dd, ad_validity, ad_publishing_date) <= getdate() )) )
select userdetails.user_email,addetails.ad_title,
addetails.auth_code,addetails.ad_status,
addetails.ad_publishing_date
from userdetails inner join addetails
on addetails.auth_code=userdetails.auth_code
and addetails.ad_status = 2 AND ( ( DATEADD(dd, ad_validity, ad_publishing_date) <= getdate() ))
Kindly sort out my problem else provide me alternate solution to match my scenario.
Thanks in advance.
Hi all,
I tried to run the following query an obviouly i didn’t execute. I know this is terribly wrong. Please help me out. The select statement is to get values from another table and it will return ultiple values which I have to insert into reasonforchange table.
insert into reasonforchange(reasonforchange_id,reasonforchange) values
(newid(),select distinct displayvalue from oc_picklistvalues pv inner join oc_picklistlanguages pl
on pv.picklistid=pl.picklistid where pl.shortdescription=’Reason for Change’and pv.displayvalue=’Promotion’)
@Arvind,
The query is failing because the select statement is returning more than one value.
Test if the query works, using select top 1 ……. something like this,
insert into reasonforchange(reasonforchange_id,reasonforchange) values
(newid(),select top 1 displayvalue from oc_picklistvalues pv inner join oc_picklistlanguages pl
on pv.picklistid=pl.picklistid where pl.shortdescription=’Reason for Change’and pv.displayvalue=’Promotion’)
Work Around : Why don’t you create a default on ReasonForChange column , keep the default as newid(), then insert statement will be,
insert into reasonforchange (reasonforchange) select distinct displayvalue from oc_picklistvalues pv inner join oc_picklistlanguages pl
on pv.picklistid=pl.picklistid where pl.shortdescription=’Reason for Change’and pv.displayvalue=’Promotion’
Hope this helps,
Regards,
IM.
how can i set the logging options through the database management interface in sql server 2005
Actually i want, Record logging shall be handled within the database, and not within the application. The user
shall set the logging options through the database management interface in SQL Server 2005.
Very good concept
Hi ,
I want to insert value into particular column,there is any statement like using “where” condition…”insert table table_name values value1 where id=111″ etc.
Please let us know if any statements like..
Thanks and Regards,
Phani..
Hi Pinal:
I am stack in a piculiar situation.
I have a stored proc (SQL 2005) which is compilled and executed successfully. I used cursor in my stored proc.
Stored proc executes on a table and match data with another table dada. So it will either update existing data or if new data come will insert new data into second table.
My updating command is working ok. But inserting commend is not working. I know in my first table I habe 7 new data which are waiting to insetr into second table.
I’m trying to find it out where is the problem but couldn’t.
Would you please help me. I have to delivery my project by the end of the second week but I’m stack here.
Here is my Stored proc:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[AddMonth_Event_TotalInfo]
–@fiscalYear int
as
Declare
C1 CURSOR READ_ONLY
FOR
SELECT CONVERT(varchar(7), IdsData.EarliestEvent, 111) AS [Yr-Mon], TagType.TagTypeID AS EventID, ISNULL(SUM(IdsData.EventCount), 0) AS Total
FROM TagType INNER JOIN
TagDetails ON TagType.TagTypeID = TagDetails.TagTypeID INNER JOIN
IdsData ON TagDetails.[TagName (Unique)] = IdsData.TagName INNER JOIN
SensorCategories ON IdsData.AgentIP = SensorCategories.SensorIP
WHERE (SensorCategories.Monthly = ‘Y’)
GROUP BY CONVERT(varchar(7), IdsData.EarliestEvent, 111), TagType.TagTypeID
ORDER BY [Yr-Mon], EventID
Declare
@MonthID int,
@EventMonth varchar(7),
–@EventName varchar(30),
@EventID int,
@Total int,
@EventTotal int
–delete from Month_Event_TotalInfo
OPEN C1
FETCH NEXT FROM C1
INTO @EventMonth,@EventID,@EventTotal
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @MonthID=isnull(MAX(Month_ID),0)+1 from Month_Event_TotalInfo
select @Total=Event_Total from Month_Event_TotalInfo where Event_Month=@EventMonth
and Event_ID=@EventID
if (@Total>=0)
update Month_Event_TotalInfo
set Event_Total=@EventTotal where Event_Month=@EventMonth and Event_ID=@EventID
else
insert into Month_Event_TotalInfo(Month_Id,Event_Month,Event_ID,Event_Total)
values (@MonthID,@EventMonth,@EventID,@EventTotal)
FETCH NEXT FROM C1
INTO @EventMonth,@EventID,@EventTotal
END
CLOSE C1
DEALLOCATE C1