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,,
how to insert and select a image in sqlserver database … please help me
Simple method is to store path in the table’s column and store actual image in the server’s directory
Hi,
I am using sql server 2000, Once in 2-5 months, it stops insertion records from application;
and after that I have to manually insert one record from query analyzer and then it starts smooths for next 2-5 months; there is no error in sql server log nor in event viewer nor, space issue on HDD, nor log file is over sized;
your expert advice is required, what do i check to fix this issue;
hope to listen from you soon
thanks
sani
You need to run a profiler to understand what is going on
Hi
Thanks for your reply; you means when I database is stuck and not inserting rows; at that time I run profiler ?
as right now it is working and don’t have idea when this problem arise again
hope to listen from you again
thanks and best wishes
sani
You need to run it until you find something wrong in the process
hi everyone !!!!
I’ve six columns in new table….i ve to insert the data in it….all six columns are coming from different table…..consider there is no pk….how can i insert the data in it ?
plz reply !!!!!!
You may need to join all those xiz tables and insert to new table
thnkx !!!!!!
now plz help me in new query…plz find it below
Hi, pinal great website very helpful…great palace to learn new things…thanks
hi everyone
structure of tables
Table- Answer
[Connid] [varchar] (50) ,
[DNIS] [varchar] (50),
[ANI] [varchar] (50) ,
[Date] [varchar] (50),
[Time] [varchar] (50),
[DT] [datetime] NULL ,
[CampName] [varchar] (50).
Table abandoned
[Connid] [varchar] (50) ,
[DNIS] [varchar] (50),
[ANI] [varchar] (50) ,
[Date] [varchar] (50),
[Time] [varchar] (50),
[DT] [datetime] NULL ,
[CampName] [varchar] (50).
Table Abandonedq
[Connid] [varchar] (50) ,
[DNIS] [varchar] (50),
[ANI] [varchar] (50) ,
[Date] [varchar] (50),
[Time] [varchar] (50),
[DT] [datetime] NULL ,
[CampName] [varchar] (50)
there are four table………. one table ( table CDR —> as soon call enter in call center connid is provided) has column called DT….which is start time of call….now for end time data can be come any of the table……connid is uique for all four.
scenario-
in a call center a call comes it gets a unique connid………then the call process…..it could be abandoned or abandoned in queue or answer by agent…….i need to calculate total process time against each connid……may it be answered may it abandoned by user or abandoned in queue or answered by agent.
example–
connid-12312hqwjqheqkeq is answered by agent
connid-121wejqwheheqe is abandoned in queue
connid-121bddaskjdabsdk is abandoned by user
@varun,
hello, you can try following
insert into destination_table (col1,col2)
SELECT
(select col1 from source_table1),
(select col2 from source_table2)
how to store one column in multiple rows in one insert
hi
i want to insert records from table to another table
where record is exists then replace else add
can help me
thanks
Generic method
update t1
set t1.col=t2.col,…
from table1 as t1 inner join table2 as t2 on t1.keycol=t2.keycol
insert into table1(col_list)
select col_list from table2 as t2 where not exists(select * from table1 where keycol=t2.keycol)
Hello,
Can a datatable be passed as a parameter to a stored procedure? I want to insert data from a datatable into a database table. Can you please help me with it?
thanks
Hi Amee,
Are get the Solution for below Request . please help me about this query. i have same requirement. Thanks in Advance …
“Can a datatable be passed as a parameter to a stored procedure? I want to insert data from a datatable into a database table. Can you please help me with it?”
You need to use a dynamic sql. But in this case it is not recommended. Why do you want to do this? Also beware of sql injection
exec(‘use ‘+@db_name+’ your query here’)
sir,
I am uday
I am inserting multiple rows in two tables with relationship using xml.
I trying to inserting compny id(company table) in email table using @@IDENTITY
how i can write stored procedure for that
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
This one help me Great!
Thanku
Excellent job by Pinal…!!! Best Wishes
SELECT City, ModifiedDate
FROM Person.Address
WHERE StateProvinceID DATEADD(yyyy, -5, GETDATE());
GO
SELECT City, StateProvinceID, PostalCode
FROM Person.Address
WHERE StateProvinceID = 15733;
GO
— get the missing indexes that would be beneficial for speeding up above queries
SELECT D.index_handle, [statement] AS full_object_name, unique_compiles, avg_user_impact, user_scans, user_seeks, column_name, column_usage
FROM sys.dm_db_missing_index_groups G
JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle
JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle
CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC
ORDER BY D.index_handle, [statement];
Hi Sir,
your blog is good and so much helpful to me . i have query on the above article insert Multiple records using single insert statement using Stored Procedure please give the solution to my query .
Post your query so that we will be able to help you
Hi Madhivanan,
Thank You for Responding quickly,
Am is the begginer , i have Dynamic Datatable in C# (ADO.net) with 50 records, i want to insert in Db using Stored Procedures with unique insert statement . Please help me ,
Thanks in Advance .
You need to loop thru the data in C# and insert into the database
Hi Madhivanan,
Thank You for Responding quickly,
am doing like that only but to insert every record we call storedprocedure for every record. i want to call storedprocedure only once . Is it Possible . Thanks in Advance.
You have concatenate all values and pass it as parameter to the procedure and split inside it. But it is better to call it everytime from your C#
try this
insert into toy values
(1,’Kitchen set’,200);
(2,’Racer jet’,500);
(3,’Milk Toy’,350)
Note that this will work from versions 2008 only
Hi. When I need to insert multiple values at once it’s ususaly from another table, so I just use the select-statement with the insert-statement like this:
insert into [MyTable] select ProjectID, GroupsID = 131119 from [MySourceTable] where [KeyField] = 1
Note that what I select must have the same columnnames as the table I’m trying to insert into.
Did you try this?
insert into [MyTable] select ProjectID, 131119 from [MySourceTable] where [KeyField] = 1
the syntax for multiple row insertion
INSERT INTO [Age]
([Name]
,[Age]
,[Profession])
VALUES
(‘n’,9,’j’),(‘t’,39,’yj’)
INSERT INTO .[Age]
([Name]
,[Age]
,[Profession])
VALUES
(‘n’,9,’j’),(‘t’,39,’yj’)
Note that this will work from version 2008 onwards
can you tell me how to insert Multiple Records in multiple table Using One Insert Statement –
INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (1000, ‘IBM’)
INTO suppliers (supplier_id, supplier_name) VALUES (2000, ‘Microsoft’)
INTO customers (customer_id, customer_name, city) VALUES (999999, ‘Anderson Construction’, ‘New York’)
Here ‘ALL’ keyword is not supported, but why?
What did you mean by ALL in this content? Read the blog and use as stated