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
Thank you, it didn’t improve my performance very much, because i only need to insert 5-10 rows, but it was exactly what i wanted to do
Hi every one ,can any one help me in this regard ,i want to do this in SQL Server2000,presently iam doing this in Access but i am giving example in Excel.
Policy no RISK STATRT DATE RISK END DATE DOB ACTIVE/LAPSE
111 1-Feb-03 9-Aug-09 19/09/1982 ACTIVE
112 9-Dec-03 9-Jun-09 01/01/85 LAPSE
now I want the following records in this format
Pno Policy year NO of Days exposed AGE ACTIVE/LAPSE
111 0 365 20 0
111 1 366 21 0 IF A POLICY IS IN ACTIVE STATUS WE NEED TO GIVE 0 OTHERWISE 1
111 2 365 22 0
111 3 365 23 0
111 4 365 24 0
111 5 366 25 0
111 6 188 26 0
FOR A LAPSE POLICY WE NEED TO CALCULATE EXPOSURE UPTO THE NEXT POLICY ANNIVERSARY
Pno Policy year NO of Days exposed AGE ACTIVE/LAPSE
112 0 366 18 0
112 1 365 19 0
112 2 365 20 0
112 3 365 21 0
112 4 366 22 0
112 5 365 23 1
WE NEED TO GIVE 1 IN THE COLUMN ACTIVE/LAPSE IN THE POLICY YEAR WHERE IT GOT LAPSED
@Kumara Datta
Are all those results from the two sample records?
Hello,
my name is dipti.
i read ur bolg it’s really helpful.
but i have one query ;
i want to insert values in one table (consider as table1) from another table(table2). also consider there are 4 fields in table1 and table2 has more fields including fields in table1.
now problem is i want to insert first field into table1 from another global variable and rest three fields are from table2.
can you please help me in this.
@dipti
INSERT INTO table1(col1, col2, col3, col4)
SELECT col1, col2, col3, @global_var FROM table2;
@ brian tkatch
i tried it. it works. Thanx!!
Thanks for this. I had a MySql table with 3 columns and 4,500 rows. I used your syntax to convert to Sql Server and the insert took 23 seconds. Not bad!
hi i want a query 2 retrive data from 2 database & add it in another 3rd database
please suggest me the query.. m fed up.. i’m searching since 2 days & found the following:
Insert into table_name(col_name1, col_name2)
values ((select column_name from table_name1 where id = ’29’) union (select column_name from table_name2 where id = ‘3’))
please give me a solution…
Insert into db3..table_name(col_name1, col_name2)
select column_name from db1..table_name1 where id = '29'
union
select column_name from db2..table_name2 where id = '3'
sirr,
how to insert more data(for ex 10 mb) in sql serverr
how insert the dynamic data in mysql using c pogram.
ie
a=5;
b=10;
c=15;
INSERT INTO per(a,b,c) VALUES(‘a’,’b’,’c’)
@Ankit
I don’t understand the question. What exactly is the problem?
@karthi
Which part is dynamic? Is this a T-SQL script?
how can append/insert records in excel format, at once, to Ms access database table using the insert sql query
@VIJAY
In SQL Server 2008, that syntax should work. Prior to that version, a VALUES statement can only INSERT one record at a time.
So, there are two options:
insert into DEPT(DNO) VALUES (1);
insert into DEPT(DNO) VALUES (2);
insert into DEPT(DNO) VALUES (3);
Or:
insert into DEPT(DNO)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3;
Brian, I’m getting same error as Vijay on SQL Server 2008. Can you tell me how to fix this?
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘,’.
When I run:
INSERT CT_ProductFailures (Old_Modes, New_Modes)
values
(‘AAA’, ‘BBB’),
(‘AAA2’, ‘BBB2’),
(‘AAA3’, ‘BBB3’)
Make sure the compatibility of the database is 100
How can i get all newly inserted identiy id. I used following query but it returns only the last record id. Can u help me?
INSERT INTO TableNew(
column1,
column2,
column3
)
SELECT
1,
xmlcontent.value(‘ column1[1]’,’VARCHAR(200)’) column1,
xmlcontent.value(‘ column2[1]’,’BIT’) column2
FROM @xmlData.nodes(‘//Table1’) AS R ( xmlcontent )
SELECT IDENT_CURRENT(‘TableNew’)
How can i load a text file in sqlserver 2005 by using .net
How can i insertthe values of a text file in sqlserver 2005 by using .net
HI,
I have a jsp Page where User Add Text Box’s as per is requriment for that am using JavaScript,
Now i want to insert those textbox values in my database table when the user click submit button,
Regards
Vanishree
Hi,
I wanted to know about multiuser scenario…
While many users are inserting values to the DB at a time how to get the last id being inserted..
And i want information about record locking. Please help.
Thanks & Regards
Kavya
@Kavya,
One solution if you are still in designing phase, Add Last_updated_Time column to your table and assign a default value of getdate().
By this way, you can know what is the last value that was inserted into the table based on the datetime column.
Or you can also an Identity column that will give you lastest inserted record.
Regarding Second Question.
Locking a record in a table.
This is just a summary for you to start, please refer books online for in depth knowledge…
SQL Server applies Shared / Exclusive locks when dealing with records in a table.
Exclusive lock: SQL Server puts a Exclusive lock on the table when a user updates / inserts a record into a table.
Shared Lock: SQL Server puts a Exclusive lock on the table when a user reads data from a table.
Shared lock is compatible with other shared Locks, meaning multiple reads can happen simultaneously on a table.
Exclusive lock is not compatible with Shared Lock, meaning if SQL Server puts a Exclusive Lock on a table, No other connection can read data from that table.
But still, there could be scenarios, where it is possible to read dirty data, lose updates, phantom data…. To overcome all these cases, You need to apply proper Isolation level before you initiate any transaction.
I strongly Suggest you read the topic, ISOLATION LEVELS in SQL SERVER 2005.
This topic has been explained very well in simple words with example in below weblink:
~ IM.