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
why is unionall faster than multiple insert …is it true for all conditions or in some particular conditions
how in one insert commend ican insert multiples rows ?
hi sir,
this blig is good and helpful for me….
but how can i insert N no’s of records when we are not Knowing exactly records
plz give me this kind of query solution to improve my query….
Hello Sir,
This blog is very good and very help full to me to become a bda
Thks
thank you!
Hi Sir,
I am struggle to make this work, I receive ‘Subquery returned more than 1 value.’ error, everytime when I try to run this:
INSERT INTO TASKS_DATA_HEADER (ASSET_ID, CLIENT_ID, CREATED, TASK_DEF_ID, MODIFIED, TASK_ID, USER_ID, PROJ_ID, LAST_MODIFIED, LATITUDE, LONGITUDE)
SELECT ASSET_ID, CLIENT_ID, CREATED, TASK_DEF_ID, MODIFIED, TASK_ID, USER_ID, PROJ_ID, LAST_MODIFIED, LATITUDE, LONGITUDE
FROM UNDO_TASKS_DATA_HEADER_DELETE
WHERE CHANGE_ID = ‘19802a81-a42a-4b2e-9c68-ef22a36ef610’
The frustrating part is the the column matches and INSERT suppose to work for multiple rows, and the query without INSERT INTO work just fine, which return 11 rows, any ideas?
Much Thanks in advance!
sir tell me one thing..can i set limit of no of rows in table in sql server 2005 ?..e.g i want insert only 5 rows in table not more than 5.
If the maximum rows is5, then write a insert trigger,see if the count(*)>5, then rollback
thnk u very much
Hi
I am having an issue in running prepared statement.
There are three tables Tab1 contains 2 columns (ID,name) ID is a primary key & Foreign key
Tab2 has (Name,ID) here also ID is a primary key.& Foreign key Tab3 has (ID1,ID2) both have not null contraint and refer to forgein key ID s from tab1 and tab2
The data for tab1 and tab 2 are are inserted now for inserting the data for tab3
i get data as (integer,string)
When i create the SQL statement i create it like this
Insert Into tab3 (ID ,ID ) where values((select ID from tab1 where ID=?),(Select ID from tab2 where name=?);
when i set the prepared statement i set it as
pst.setInt(1,ID);/// over here id is an integer
pst.setString(2,Name);///here name is a string
when i run the prepared statement it tells me that no value found for ID2 ??
When i run the individual SELECT statements from the insert statements in command prompt of sql then it displays the values and if i give the values in the above insert statement also then the value get inserted but same this the prepared statement does not do…
What can be done in this situation????
thanks a lot…
sir ,
how will the ” insert using UNION ALL ” affects in ” after insert Trigger ” ???
INSERT ALL
INTO mytable (column1, column2, column3) VALUES (‘val1.1’, ‘val1.2’, ‘val1.3’)
INTO mytable (column1, column2, column3) VALUES (‘val2.1’, ‘val2.2’, ‘val2.3’)
INTO mytable (column1, column2, column3) VALUES (‘val3.1’, ‘val3.2’, ‘val3.3’)
SELECT * FROM dual;
INSERT ALL
INTO mytable (column1, column2, column3) VALUES (‘val1.1′, ‘val1.2′, ‘val1.3′)
INTO mytable (column1, column2, column3) VALUES (‘val2.1′, ‘val2.2′, ‘val2.3′)
INTO mytable (column1, column2, column3) VALUES (‘val3.1′, ‘val3.2′, ‘val3.3′)
SELECT * FROM dual;
This is not valid in SQL Server. Is this for ORACLE?
This is only possible in Oracle
hello sir,
pls tell me how to insert the values at end of the record? can we add values at end of the record and how does it affect
TRIGGER CREATING
create trigger trigger_name on table_name for insert,update
as
update table_name set column_name=column_name+500
Hi,
how to insert 100 values into a table(in which there are 7 fields in a table entry format) in a single querry.Am a new user.kindly help me on this
thanks in advancce!!
The solution you have given is excellent
“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”
I have two questions after going through this .
A) Is there any limit to number of rows for insertion ?
B) If I insert 2000-3000 rows with this method then would it be a feasible solution or should go for stored procedure insted ?
Use the following
USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1), (‘Second’,2), (‘Third’,3), (‘Fourth’,4), (‘Fifth’,5)
GO
Very nice
I have a question how can you put into new table the data you needed by selecting the column from four different table and that new table will display the summary column you get from four table? Can you help me Im having a hard time solving this.
Just wanted to say thanks for this clear and concise article; it just made my day easier