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 Pinal Dave,
I am very glad to see your talent hear,
Its really good and intresting for all software engineer.
Thanks again dear.
select name from sys.objects where type = ‘c’ –CHECK Constraint
select name from sys.objects where type = ‘D’ –DEFAULT Constratint
select name from sys.objects where type = ‘f’ –FOREIGN KEY Constraint
select name from sys.objects where type = ‘l’ –Log
select name from sys.objects where type =’fn’ –Scalar Function
select name from sys.objects where type =’if’ –Inline table function
select name from sys.objects where type = ‘p’ –Stored Procedure
Select name from sys.objects where type = ‘K’–PRIMARY KEY & UNIQUE Constraint
select name from sys.objects where type = ‘RF’–Replication filter stored procedure
select name from sys.objects where type = ‘S’ –System Table
Select name from sys.objects where type = ‘TF’–Table Function
select name from sys.objects where type = ‘TR’–Trigger
select name from sys.objects where type = ‘U’ –User Table
select name from sys.objects where type = ‘V’ –View
select name from sys.objects where type = ‘x’ –Extended stored procedure
I want to store the result of every single query in one column in a table. Say First qery gives 40 records then those 40 records should be in first column, and if second query gives 23 records then those 23 records should be in second column, the way so on….So could any say how to do ? Which would be highly appreciated.
This is kind of cross-tab report
Read this and make use of it
Howdy –
Can I insert multiple rows into one table by doing a select on another table. For example:
INSERT INTO table1(col1,col2, col3)
SELECT col1,col2, col3 FROM table2 WHERE col1 > 5
Thanks, Matt
Yes it is possible as long as the datatypes of the columns match correctly
I browse and saw you website and I found it very interesting.Thank you for the good work, greetings:
Hi Pinal,
gone thru ur blog…ur blog z highly useful… thx a lot man…
Hi Pinal,
Fantastic work. Can make alot of people happy to see.
One question:
Employee table contains emp_id, other employee details and a column manager_id. Some of the manager_ids contains NULL. How can we update those nulls to zero USING self join.
You can simply use
Update employee_table
set manager_id=0
where manager_id is null
Hi to find last modfiy table and stored procedure in sql server 2000 And 2005
In My Table does not have creation date And Modify date field.
Question:
Can you do something like this in MSSQL?
INSERT IF NOT EXISTS
INTO YourTable (FirstCol, SecondCol)
VALUES (’First’ , 1) , (’Second’ , 2) , (’Third’ , ‘3′), (’Fourth’ , ‘4′) (’and so on’) ;
Thanks for any insight into this
Yes it is possible from SQL Server 2008 version onwards
Very helpfull and performance improving article.
Thanks
Hi,
we are using SQLserver2000 in our application.In that we are
handling Chineese characters. Collation is “SQL_Latin1_General_CP1_CI_AS”. datatype is nvarchar.
But in most of the situation chineese characters displays as ??.
sometimes there is nothing displays. Pls. advice me to resolve this case.
Hi Dave I’m using Access how could I insert multiple records using SQL let’s say I got a Destination Database and a Source Database and the user could choose what table he/she would use and from the Source Database choose Table1 and in the Destination Database also Table1 and everything in that Source table would be copy or append to the Destination Table.
Insert into (Destination Table) in (Destination Database) Select
(Source Table.*) from (Source Database)
Please need help on this, Thanks in advance
Hi Pinal,
Thanks a lot for sharing this great information with us.
I need to insert mulitiple value in a table whose value will be decided at runtime. Also no. of value will be decided at runtime.
Is there any way to resolve it at backend.
Thanks and Regards.
Gyan.
I WANT TO CREATE A PROCEDURE IN SQL SERVER 2000 JUST LIKE THIS PLZ HELP ME BY GIVE PROPER SOLUTION
IF(SET @IN_SERVICE=’DEVOTIONAL’)
INSERT INTO TBL_DEVOTIONAL
IF(SET @IN_SERVICE=’ASTRO’)
INSERT INTO TBL_ASTRO
IF(SET @IN_SERVICE=’CRICKET’)
INSERT INTO TBL_CRICKET
You need a dynamic SQL
Make sure you read this article fully
http://www.sommarskog.sq/dynamic_sql.html
I’m trying to write a script that updates multiples tables at the same time.
For example, I’m trying to update an Employee Database where Name = John, i need to change the name John to Peter. (Database contains over 30,000 records )
I need the script to search all tables and update firstName where necessary
Here you go
declare @sql varchar(max)
set @sql=”
select @sql=@sql+’ update ‘+table_name+’ set first_name=”peter” where first_name=”john”’
from information_schema.columns
where column_name=’first_name’
exec(@sql)
Hi
I have 500 customers in a table call PM_CUST_CURR.
THe Base currency is Riyals.
There are 40 currencys that are still to be uploaded for same 500 customers.
I am uploading all by benthic. but this a kinda donkey biz.
is there ne way to upload the customer with nase currency and then copy 40 currency for every customer though some script rather then uploading 1 by 1
Do reply
This is really useful
It will save a lot of time
Thanks
Ankur
Can I perform the “Insert multiple records using one insert statement” task using a stored procedure ? If so, how do achieve this ?
Very useful Query for Developers and DBA’s.
Hi,
I want just thank you pinal dave for solution good very. It help problem solve sql server insert records lots. You best sql dev person and this site much nice. thank much, for problem my you solve.
Amand.
I need help. i have 2 tables with 40 columns each and i need to insert the data from one table to the other. destination table is normalized but the source table isnt. so i will need to check for description to ascertain the relevent ‘id’ from the code tables. i need to generate a reusable script to perform this.
Regards
Sbosh
Dear all,
Which is best performance (INSERT,DELETE and UPDATE) in ms sql
Regards
Adhi