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,
i need to insert one row for each day of the year. i.e 365 rows. it is so frustrating to insert one by one. I followed the above given format, still i am unable to insert records.
Below is the query used. Please let me know if there is any correction required in the query
INSERT INTO TST_ADM_TST_PKG (TST_ADM_TST_PKG_ID,TST_ADM_NO,TST_PKG_ID,TST_PKG_TYP_CDE,TST_ADM_TST_PKG_DESC,UPDT_USR_LGN_CDE, UPDT_DTE_TM, PROD_ID )
SELECT (TST_ADM_TST_PKG_SEQ.NEXTVAL,’57067′,’PKG001′,’000′,’STANDARD 2008′,’GUEST’,SYSDATE , NULL )
UNION ALL
SELECT (ngt01.TST_ADM_TST_PKG_SEQ.NEXTVAL,’57068′,’PKG001′,’000′,’STANDARD 2008′,’GUEST’,SYSDATE , NULL )
UNION ALL
SELECT (ngt01.TST_ADM_TST_PKG_SEQ.NEXTVAL,’57069′,’PKG001′,’000′,’STANDARD 2008′,’GUEST’,SYSDATE , NULL );
Hello Sathish,
Use the below script:
DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
— You can change @year to any year you desire
SELECT @year = 2010
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year – 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year – 1900 + 1, 0)
— Creating Query to Prepare Year Data
;WITH cte AS (
SELECT 1 AS DayID,
@FirstDateOfYear AS FromDate,
DATENAME(dw, @FirstDateOfYear) AS Dayname
UNION ALL
SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.FromDate),
DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
FROM cte
WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)
SELECT FromDate AS Date, Dayname
FROM CTE
OPTION (MaxRecursion 370)
Regards,
Pinal Dave
Thanks Pinal :)
will work on your suggestion.
Sathish
Hi Pinal,
Thanks for wonderful suggestions and tips.
I have two questions.
1) I need to know that why unique key allows only one NULL value, why not more than one ?
2) Can we use transactions and commit or rollback transaction inside instead of Triggers ? If NO then Why ?
Thanks in advance for your kind suggestions and answers.
Thanks,
Sanjay
Hello Sanjay,
Unique column allow any value for once even it is null. If null comes again then its duplicate and unique constraint not allow that.
You can use transaction inside trigger. But if you rollback a transaction:
1. All data modifications made to that point in the current transaction are rolled back, including any made by the trigger.
2. The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back. No nested triggers are fired by the execution of these remaining statements.
3. The statements in the batch after the statement that fired the trigger are not execu
Regards,
Pinal Dave
Hi ,
Q: How to update more than one records with using single update statement with different where conditions
MyRequirement: I have requirement to update almost 150 records with different values based on primary key id.
for this , I have formated a query with multy update statements
like
MyAns:
update my_table set col1=val1,col2=val2,…. where id=valID1
update my_table set col1=val4,col2=val5,…. where id=valID2
update my_table set col1=val6,col2=val7,…. where id=valID3
update my_table set col1=val8,col2=val9,…. where id=valID4
but Is It possible to do with single update statement
and Is their any better solutions for my Requirement, If so Let me know
It is possible with one statement with CASE:
update my_table set col1= CASE id WHEN ‘valID1’ THEN ‘val1’ WHEN ‘valID2’ THEN ‘val4’ END, col2= CASE id WHEN ‘valID1’ THEN val9,…
It is also possible with a join.
WITH Data AS (SELECT new data)
UPDATE my_table FROM my_table, Data …
Though, 150 separate statements shouldn;t be much of a problem, especially if they are wrapped in one transaction.
can any1 tell me what could be the problem .
when i am inserting data in mysql server it insert the same row for two times where as i want it to be entered for single time
Hi Nagaraju,
Single statement is not possible this way but you can rewrite the query if all values (val1, val2, valID1…) are in another table.
Regards,
Pinal Dave
actually
I created an tblemployee table
as
create table tblemployee(
eid primarykey ,
ename varchar(50),
email varchar(50),
date varchar(10),
zipcode numeric(6),
basicsal double(10),
total double(10)
);
eid is set to automatic increase
now on using isert query on it
as
insert into tblemployee(‘ename’,’email’,’date’,’zipcode’,
‘basicsal’,’total ‘)values (“rajni”,”abc@bbc.com”,”12/20/2010″,”400089″,”10000″,”10000″);
now this is inserted properly into table but for 2 times with 1 entry as eid 1 and next entry with eid 2 rest all data is same just primary key is increasing for second entry.
i mean 1 entire row is inserted for 2 times that to with increase in id which is a primary key for the table
Hi,
I found a space between my database fields, it is possible to remove? also my data is huge like 30Million……..Please can give any solution for this………
Regards,
Imran Saiyad
Hello Imran,
Are you talking about unused space in database file or space in table columns?
If you mean unused space in database file then use DBCC SHRINKDATABASE or DBCC SHRINKFILE.
To remove space in table records use REPLACE function.
Regards,
Pinal Dave
i had created a table like this
eg –
CREATE TABLE #Employee
(EmployeeID INT IDENTITY(1,1),
EmployeeName VARCHAR(20))
now i want to insert mulitple values into the this table
i had tried it in this way –
WHILE SCOPE_IDENTITY()<5 OR SCOPE_IDENTITY() IS NULL
INSERT INTO #Employee (EmployeeID,EmployeeName)
VALUES (SCOPE_IDENTITY(),'Mahesh' + SCOPE_IDENTITY())
it doesn't gives me error nor does it inserts the values in the table.
can u explain me what could be the problem
Can you tell me the best possible way to achieve this (multiple row insert into a table with mulitple columns)
as this can be achieved with a single table with single identity column
eg –
CREATE TABLE #InnerJoinTest1( InnerJoinTest1_PK INT IDENTITY(1,1) )
WHILE SCOPE_IDENTITY()<5 OR SCOPE_IDENTITY() IS NULL INSERT INTO #InnerJoinTest1 DEFAULT VALUES
I want to achieve this with the above table structure(#Employee) so how can i achieve it is there any way
need ur help
pls suggest
I’m confused as to what you are trying to do.
INSERT INTO Employee (EmployeeName)
SELECT ‘Mahesh’ UNION ALL
SELECT ‘Nitin’ UNION ALL
SELECT ‘Manisha’ UNION ALL
SELECT ‘Dipthi’
What happens is that…
First you try to insert values (NULL, ‘Mahesh’ + NULL) into the #Employee table. SCOPE_IDENTITY() returs NULL because you haven’t actually inserted any IDENTITY value yet. You can check this by calling the following right after the CREATE TABLE statement:
SELECT SCOPE_IDENTITY();
Now, you can’t insert value into the IDENTITY field (well, you can, but you need to explicitly enable it first). This gives an error:
Cannot insert explicit value for identity column in table ‘#Employee’ when IDENTITY_INSERT is set to OFF.
Now the back to the WHILE loop. Take notice that you have “OR SCOPE_IDENTITY() IS NULL” in there. That’s why you entered the loop in the first place. SCOPE_IDENTITY() returns NULL again because last insert failed and keeps returning NULL every time because all the inserts fails.
Now you got yourself a nice endless loop which, I think, is consuming A LOT of server’s resources, mainly CPU :)
Pinal’s fix is good but I would add COALESCE in there and for some reason I like to use CAST over CONVERT:
WHILE COALESCE(SCOPE_IDENTITY(), 0) < 5
INSERT INTO #Employee (EmployeeName)
VALUES ('Mahesh' + CAST(COALESCE(SCOPE_IDENTITY(), 1) AS VARCHAR))
Hello Marko Parkkola,
Thanks for giving a explanation .
I tried the query and it worked greatly.
Its my first post now will enjoy this arena.
Thanks and Regards,
D.Mahesh.
Hello Mahesh,
I do not know how the first insert statement is not returning error.
Anyway the first statement has two issues: 1. you are trying to insert a value in IDENTITY column. 2. you are concatinating varchar and integer type values.
Replace the first insert statement with following and check.
WHILE SCOPE_IDENTITY()<5 OR SCOPE_IDENTITY() IS NULL
INSERT INTO #Employee (EmployeeName)
VALUES ('Mahesh' + convert(varchar(2),SCOPE_IDENTITY()))
Regards,
Pinal Dave
Hello Pinal,
Thanks that worked.
Its my first post now will enjoy this arena.
Thanks and Regards,
D.Mahesh.
Hi , how to find out value for a particular column for which duplicate records are inserting in to the table which is causing vialation of primarykey
Select Colname,count(ColName) From TableName
Group by ColName Having Count(ColName)>1
It will show all the duplicate records with number of occurrences.
The below qquery will show all the duplicate records with number of occurrences.
Select Colname,count(ColName) From TableName
Group by ColName Having Count(ColName)>1
where ColName = Primary key in your table.
hi pinal,
in stored procedure i am inserting data in master in master 1 identity column and getting that using scope_identity value insert into child table. if same time multipla user inserting then what will happen and how resolve this problem. Here I have to insert all the data.
can u plz resolve this.
Thanks.
How do i remove from the mailing list of this great post? it’s been over a year and i still get replies… thank you
Hello Masih,
No need to worry about simultaneous execution of your code. Because you are using SCOPE_IDENTITY function and SQL server provide you the identity that is generated by one current user’s session.
Regards,
Pinal Dave
set @newrecord =’select 1,’09:00′,’17:00′,1,1 union all select 2,’10:00′,’18:00′,1,1′
set @finalsql = ‘select’ + ‘ insert into HR_ShiftBreak (BreakID, BrekStTime, BreakEnTime, ShiftID, company_id) ‘ + @newrecord
exec @finalsql
i want to insert 1000 thousand record in my table or may be more (depend upon user request) ..
i m using concate method to make my Query string like Pinal…
@final=’select insert into Shreak (BreakID, BrekStTime, BreakEnTime, ShiftID, cany_id) select 1,’09:00′,’17:00′,1,1 union all select 2,’10:00′,’18:00′,1,1’
i m giving you an example of two data ….
kindly tell me the syntax to enter more that 1000 records…
using union all..
hi
i want my query like this
declare @stateQuery as nvarchar(200)
if @stateid is null
set @stateQuery=”
if @stateid is not null
set @stateQuery=’and tbl_city.stateid =’+cast(@stateid as nvarchar(10))
select top 1 len(dbo.Tbl_city.cityName) FROM dbo.tbl_city INNER JOIN
tbl_cityinfonew ON dbo.tbl_city.cityId = tbl_cityinfonew.cityId
where tbl_cityinfonew.language =@language
+@stateQuery
order by tbl_cityinfonew.createdate desc
Thanks in advance
Siva
Hi Siva,
You should use sp_ExecuteSQL for this purpose.
Here we just need to write dynamic query and pass parameters.
Let me give you an example:
DECLARE @qry NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)
SELECT @params = ‘
@Language VARCHAR(MAX),
@StateID INT’
declare @stateid int, @language VARCHAR(MAX)
select @stateid =10
if @stateid is null
set @qry=”
SELECT @qry = ‘
SELECT TOP 1 LEN(dbo.Tbl_city.cityName)
FROM dbo.tbl_city
INNER JOIN tbl_cityinfonew ON dbo.tbl_city.cityId = tbl_cityinfonew.cityId
WHERE tbl_cityinfonew.language = @language ‘
IF @stateid IS NOT NULL
SELECT @qry = @qry + ‘ and tbl_city.stateid = @StateID ‘
SELECT @qry = @qry + ‘ORDER BY tbl_cityinfonew.createdate DESC’
PRINT @qry
EXEC sp_executesql @qry, @params,
@language,
@StateID
Let me know if you have any question.
Thanks,
Tejas
hi Pinn,
I need a single sp taht perfrom a single functionality fro difeerent forms, depending on which from is been used the record needs to be updated in its table
thanks