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
What i’m asking is how do i display a set of records for miles = 10, then on the same row the same set of records with values for miles = 20 etc.
Another way to explain:
How do I:
Select “a”,”b”,”c” ,”d”,”e”
From “Table a”
Where “miles”=10
AND
“c”,”d”, “Price”,”date”
Where “Miles”=20
Order BY “a”
But in columns: “a”,”b”,”c” ,”d”,”e”,”c”,”d”, “Price”,”date”
I’m new to sql and need it this format to export to excel.
UNION ALL just puts the records in a vertical format and requires an equal SELECT expression.
Any help at all please.
You need PIVOT
refer this
For version 2005
Fro version 2000
Hi Pinaldave,
i have 2 lines data in the same field which means after the end user typed the first line he press enter and continue typing in the second line of the same field (cell).
How can i delete just the second line
Thanks
thanks a lot….
GREAT TIP — But I felt that I had to contribute a little piece about efficiency.
We had a basic insert statement where we were inserting an ID and a Date. Our problem is that we had approximately 25,000 IDs/dates that we were trying to insert.
Using the standard INSERT INTO convention took about 8 minutes.
I converted to the convention described in this article and it actually took longer than the INSERT INTO method. I gave up and did not look at the code again for several weeks.
HOWEVER, by accident, I tried this format again with 1,000 records and it worked in 3 seconds. Like other people have said, the # of columns and the # of records have a HUGE effect on how efficient this statement is.
Long story short, using the code above I broke up my 25,000 record set into groups of 500. The net result was a script that executed in 21 seconds, a HUGE improvement over the original 8 minutes.
Thanks again!!
Hello,
Need Help in SQL Server 2005.
I need to update few column in a table. So i have putted 2 table joins (All join are putted correctly) and used update lock also. There are nearly 7 lac records are there. Its takeing nearly 20-25 min.
Can you please suggest me what can be done to reduce time and quick update.
Thanks
Jitu
Can you post the code you used?
Dear Sir;
Congratulation for your fantastic web site.
My problem is, I use a FlexGrid to collect the user input records(1-500 Records.) and then when user finished, I read the flex in a Array and with a Loop insert them in two tables(SQL Server 2000).
Is there any way to insert to the tables with one sql query?
I mean I don’t want to use FOR loop to insert them.
Thanks a lot.
Farid.
Pefromance degrades massively over large datasets. For 10000 inserts over 2 columns a standard multiple insert takes about 4 seconds or so whereas this technique takes around 1 minute 27. Not so good!
1. How to insert bulk data in sql table from text file.
if text file is delimited, and only insert third or fourth number delimited value want to insert in table.
2. How to get lacs(more then 8 lacs) of record from table.
when i am executing select query, my pc raise an error of virtual paging error.,
can you tell what is the error in the following statements.
insert into emp_new (name,id,salary)
values (‘Jony’, 215, 20000);
Your blog is very helpful to us.
Thanx
The syntax is correct
Did you get any error?
why are we using UNION when INSERT INTO is doing the same task.Is there any signigicant difference as regards query optimization?
Hai Pinal Dave,
I am Nirmal Singh Working as SE in Madurai.
Your query trick helps me in many situations.
Thank you much.
All the Best.
@Payal,
When you use Union all , you still need to use INSERT INTO.
If you carefully see the code, you will recognize that, if you want to insert 5 records in a table then you will write 5 insert into statements, but when you use union, you can insert 5 records in single insert statements.
Thing to notice is, it is executed only once and 5 records are inserted in the table, It is doing the same work as 5 insert statements.
Hope this helps,
Thanks
Imran.
Hi,
I need to insert data into multiple tables based on one single select on one table only.
Its like i have a requirement wherein some of the column data needs to go in one table and the other columns data into a separate table.
Please do let me know what to do with respect to this ?
Try the code like this
Insert into target_table1(column_list)
select required_columns from source_table
Insert into target_table2(column_list)
select required_columns from source_table
Insert into target_table3(column_list)
select required_columns from source_table
.
.
.
For one of my websites I need to simply copy a set of records or rows from one table to another table. The tables are part of one database only. How can I achieve it. Should we use a datareader to do it?
Insert into target_table(column_list)
select column_list from source_table
where some_condition
@webdesign,
There are many atleast two ways that I know to copy tables.
1(a) Create a new table which is similar to your original tables, and then execute this statement, this will copy all your records from one table to other table.
insert into new_table select * from old_table
1(b) If the table is very big and you want all the constraints, indexes, primary keys… foriegn keys… and everything related to table, then I suggest script the original table,
To srcipt original table – In object explorer, click databasename, click table name and then right click on table , click script table as and select Create to.
Run the script on the same database, but change the name of the table and change the name of foriegn and change keys etc…. and your new table is now ready,
execute the same statement to copy all the data in this new table.
2. If you dont want any constraints, keys, indexes , then simple execute this statement.
this will create a create table and also insert all data, but there will be no constraints , keys ….
Select * into new_table from old_table
You can also do this through DTS package …
Hope this helps.
Thanks,
Imran.
webdesign specified that tables are part of database which meant they already existed and you need sime
INSERT INTO…. SELECT syntax
this statement is new to me, good to know that we can insert multiple record using select and unionall ,
Dear sir
How can i update & insert the records in two join fields in sql 2005. whereas i m updating the records from MS-Access Forms
pls. help
rahul
9910839188
Wonderful Information. I really appreciate your work towards this site.
Though I have potential experience in Oracle, this site helped a lot as I am working on SQL Server now.
Keep going and Rock.
Thanks,
Srikanth Kondeti
INSERT INTO dbo.MOTO_MGPS_STG_PRICE_LIST_DATA
(SEQ_id, Price_List_id, Price_List_Name, Currency_Code,
SKU_Name, Price, Start_Date, End_Date, Updated_By_On, Approved_By_On,
Source_Syatem, ERP_EXTRACT_DATE, WM_LAST_UPDATE_DATE, WM_PROCESS_FLAG, WM_ERROR_MESSAGE)
VALUES
(1, 100, ‘moto’, ‘dollar’, ‘Asia’, 500.00, 23-JUL-2008, 24-JUL-2008,
‘Ravikiran’, ‘Murali’, ‘Ebiz’, 25-JUL-2008, 21-JUL-2008, ‘Y’, ‘INITIAL PHASE’)
please tel me what did i missed in this.trying to insert but failed.
Note that date values should be passed within single quotes
Use single quotes around the date values