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 have a customers table, orders table and a products table
A customer orders more than one product at once
i would like to insert those product values associated with the customer with one insert statement into the orders table
How do i do that?
insert into orders (customer_name, product_name_1, product_name_2, product_name_3)
select (select c.customer_name from customer c where c.customer_id = your_customer_id), (select p.product_name_1 from product p where p.customer_id = your_customer_id), (select p.product_name_2 from product p where p.customer_id = your_customer_id), (select p.product_name_3 from product p where p.customer_id = your_customer_id)
this will resolve your problem hoping that there is some customer id in the all three tables for referencing.
This is a classical example of many-to-many relationship.
You need 4 tables with (at least) following columns:
Customer (ID INT PRIMARY KEY, Name NVARCHAR(..), Address NVARCHAR(..), ..)
Order (ID INT PRIMARY KEY, CustomerId INT REFERENCES Customer(ID), OrderDate DATETIME, ..)
Product (ID INT PRIMARY KEY, Name NVARCHAR(..), ..)
OrderLine (ID INT PRIMARY KEY, OrderId INT REFERENCES Order(ID), ProductId REFERENCES Product(ID), AmountOfItems INT, ..)
Customer, Order and Product tables probably explaines themselves. OrderLine table forms a m:m relation between Order and Product tables. It tells how many Product-items belongs to the Order.
Thanks,
But what if one customer orders five products at once, and i want to update those products and associate it with the customer at once.
for instance CustID =1
orders = ProdID =5,8,9,10,15
orderdate = 2010/01/02
How do i write the query to insert the custID with the ProdID’s into the database?
@Whitaker
INSERT INTO OrderLine(OrderId, ProductId)
SELECT @CustId, 5 UNION ALL
SELECT @CustId, 8 UNION ALL
SELECT @CustId, 9 UNION ALL
SELECT @CustId, 10 UNION ALL
SELECT @CustId, 15
Hi Pinal
Mistakenly i have delete all the data in a table.How can i rollback the transaction.?But i am not using any begin trans statement.
directly i have typed
‘delete from tablename’
@ravikumar
It cannot be ROLL BACK.
Do you have a backup?
hi @ pinal n all,
insert into my_dept (dept_no , dname , location)
select 1,’research’,’blore’
UNION ALL
select 2,’ad’,’mysore’
UNION ALL
select 3,’marketing’,’hyderabad’
UNION ALL
select 4,’hr’,’mumbai’
go
i tried the above code to insert multiple records into single table ‘my_dept’ , but m getting ORA-00923 error:”FROM keyword not found where expected”…..
can u point out where the problem
thanx in advance…!!!
@karan
SQL Server allows the FROM clause to be omitted, Oracle does not. Instead, use FROM Dual
select 1,’research’,’blore’ FROM Dual
UNION ALL
select 2,’ad’,’mysore’ FROM Dual
UNION ALL
select 3,’marketing’,’hyderabad’ FROM Dual
UNION ALL
select 4,’hr’,’mumbai’ FROM Dual
go
Note though, this is a SQL Server forum, not an Oracle forum.
I’m sorry but this article is entirely misleading. Why are you creating UNIONS where you can use a STANDARD ANSI SQL INSERT statement for such a task???
Smeet Bhasker correctly pointed this out. I strongly suggest you update your article so that you stop propagating incorrect information.
To insert multiple rows in a single statement can be easily acheived by using ANSI SQL syntax as follows:
Sumeet Bhasker wrote:
INSERT INTO YourTable (FirstCol, SecondCol)
VALUES (‘First’ , 1) , (‘Second’ , 2) , (‘Third’ , ‘3′), (‘Fourth’ , ‘4′) (‘and so on’) ;
This doesn’t work in SQL 2005
Multiple row insertion method works from version 2008 onwards
how to combine more one fields into one field using sql2005
example:
colA col2
1 a
1 b
1 c
2 d
2 e
2 f
i need this
colA colB
1 a b c
2 d e f
thanks
This was a tricky one. I even had to use my good old friend Google a bit! But this is how you can do it in one query. Personally I would do it using APPLY and UDF though.
WITH CTE1(col1, r, col2)
AS
(
SELECT
col1,
ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col1),
CAST(col2 AS VARCHAR(8000))
FROM
MyTable
),
CTE2(col1, r, col2)
AS
(
SELECT
col1,
r,
col2
FROM
CTE1
WHERE
r = 1
),
CTE3(col1, r, col2)
AS
(
SELECT
col1,
r,
col2
FROM
CTE2
UNION ALL
SELECT
CTE1.col1,
CTE1.r,
CTE3.col2 + ‘,’ + CTE1.col2
FROM
CTE1 INNER JOIN
CTE3 ON CTE1.col1 = CTE3.col1 AND CTE1.r = CTE3.r + 1
)
SELECT
col1,
MAX(col2)
FROM
CTE3
GROUP BY
col1
thank u very very much for the stuff provided here….
really u have solved a big problem…
thanks a lot…
Hi,
i have one table which contain one of the column data type is xml. so how can i insert data into this table with single quotes? if i just insert as below
select * from emp where empId=”
while retriving i am getting like below
select * from emp where empId=’
please give me solution.
Tnx.
You need to use four single quotes
Refer this to understand how single quotes work in SQL Server
sir,
can you send me the ‘UPDATE’ query for SQL server 2005 to my E mail address?
I am using UNION ALL to insert 1000 records at a time for realtime data with Sql server 2000 Std Edition service pack 4 it take 1 sec but with Sql Server 2005 Enterprise Edition it takes 5 sec.
Please suggest what can be done to resolve the problem.
Many Thanks
Gunjan
Awating for reply.
On the same DB I m running another insert query using Union all to insert data in 11 columns this is taking 1 sec for 1000 records but other query which is inserting data in 20 columns taking 5 sec. Please reply.
Many thx in advance
Gunjan
INSERT INTO TABEL (test, test1, test2) VALUES (), (), ()
use it like this …
Note that this method will work from version 2008 onwards only
i am nikunj and develope hospital managemet
so i have 78 filed in 1 table how insert data in to database using for loop
please
Where are source data coming from?
Hi Pinal,
I am having 20 variables which are to be inserted into a table all are new rows.
But it is conditional, i.e. if a variable is blank i dont want it to be updated…..
Thnks & Regards,
Kapil Desai
Got the Solution
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
put where clause in Select statement to chk if empty
insert into table(col)
select col from
(
select @var1 as col
union all
select @var2
union all
.
.
) as t
where col is not null
Hello Kapil,
If you are updating the columns with the value of variables then assign the variable value as below:
column1 = ISNULL(@variable1,column1)
And if you want to check for blank space also then write as below:
column1 = CASE WHEN ISNULL(@variable1,”) = ” THEN column1 ELSE @variable1 END
Regards,
Pinal Dave
while we are inserting more then 100 record in a table then we are getting error
ORA-01438: value larger than specified precision allows for this column
Note that this is for MS SQL Server
Post your issues at http://www.orafaq.com
Hey Pinal Dave…I’m working with SQL Server and need to make an operation of inserting values into a new table…but these values come from another table that is in the same database….each field in the new table comes from an operation like this:
INSERT INTO [FT].[dbo].[NewData]
([Name]
)
SELECT [Value]
FROM [FactoryTalk].[dbo].[FloatTable]
WHERE Index=0
the other operations are the same…just changing the column name of the table and the index…the second operation is this:
INSERT INTO [FT].[dbo].[NewData]
([Data]
)
SELECT [Value]
FROM [FactoryTalk].[dbo].[FloatTable]
WHERE Index=1
I tried to do all the operations together but wasn’t sucessfull… the only thing I coud do was to do them separately….but then each operation just completes one column and fill the others with NULL elements…
I tried to use ‘UNION’ and ‘UNION ALL’ but they didn’t work… please help me…
thanks for your attention…
Matt
Try something like
INSERT INTO [FT].[dbo].[NewData]
([Name],[Data]
)
SELECT
case when Index=0 then [Value] else 0 end,
case when Index=1 then [Value] else 0 end
FROM [FactoryTalk].[dbo].[FloatTable]
I need to insert 50 rows in a MsSQL table in a new column, which has been newly added, but all the other columns should have the same data.
How can i do that with a single insert query?
Should i need to delete all the records prior that?
How will you map each of new data to existing values?
we’ve been experiencing problems in retrieval and storing data in sql server 2005. this past few days storing data entered by multiple end-users causes our sql server to slows down in its processing, end-users access one table at the same time which resulted to hanging of computers and many of them their data did not store in the table. how can we resolve this problems? is this one of the weaknesses of sql database server 2005?
How much data does the table have?
Post the code used to insert data to the table
sir, how can i insert value in one field only in mysql?
for example, i have:
1. “dog”
2. “cat”
3. “mouse”
i need to insert that all in one field name Animal. how do i do that?
is it possible to insert value in a field where there is existing value to that field even its not a primary key that will add another value to that field?
sorry for my english..
i need it for my thesis. thank you again.
Are you using mysql?
Use
select group_concat(animal) from your_table
Also note that this site is for MS SQL Server
For mysql related questions post at http://www.mysql.com