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
Ended up using IF THEN ELSE instead of CASE and it worked like a charm.
Great help Pinal Dave.
Thanks
hi!
how can i copy multi field data from one table to other table in one field in new new column
please help!
post some sample data with expected result
Hello,
I have some problem . i want change My Sql Data base in MS Sql data base . i am trying that but Both Syntax are different.
plz help me
You need to change the queries manually
Hello,
Insert into myTable
(col1, col2, col3)
select 1, 2, 3
union all
select 4, 5, 6
works really fine for me.
But I want it to be a stored procedure, and I want to pass all the data as a parameter. Is there a way to do that?
Thanks in advance..
I’m having a problem with a Java code Sir,I find it hard to spot where the exception might be,during compilation,it runs properly,but when I execute it,it displays some certain exception (Error :java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘Schedule’.) please help,below is the code:
import java.sql.*;
public class Test
{
public static void main(String args[])
{
try
{
/* Initialize and load the JDBC-ODBC Bridge driver */
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverManager.getConnection(“jdbc:odbc:air_dsn”,””,””);
Statement stat = con.createStatement();
ResultSet rs = stat.executeQuery(“SELECT Fl_no,Fl_route,Dp_timings from Schedule”);
System.out.println(“******* Flight Schedule *******”);
while (rs.next())
System.out.print(rs.getString(1) + ” : ” + rs.getString(2)+ ” : “+rs.getString(3) + “\n”);
con.close();
con = DriverManager.getConnection(“jdbc:odbc:air_dsn”,””,””);
rs = stat.executeQuery(“Select Fl_no,Aircraft_type,Seating_capacity from Flights”);
System.out.println(“******* Flight Details *******”);
while (rs.next())
System.out.print(rs.getString(1) + ” : ” + rs.getString(2)+ ” : “+rs.getInt(3) + “\n”);
con.close();
}
catch (Exception e)
{
System.out.println(“Error :”+e);
}
}
}
Hi Pinal,
I am very new to SQL server programming and I am learning everything online. Your advice would be very valuable.
Is it possible to connect to a URL , pass parameters and load all the data (about 500 records)?
We have a webservice set up and is running using SAP CRM.
The automated webservice never loada all the records. So I end up having to run the web service ( passing 3 different sets of paramaters) emanually (using SAP) about 5 times a day and it really does take up my time! I have to pause my reports schedule until the data loads complete and rerun them.
Is there a way to avoid this slow loading? When i manually run it, I have to keep repeating many times before the table is fully loaded. I have tried to trace for any locks using SQL profiler but this doesn’t help. The web service quits with no error, after loading partially.
Any advice on how to make this run to completion, very much appreciated.
Regards,
Uv
I want to take user input to insert values into a table. the table exhists in he db already. I want the satement to loop for x amount of times, where x is the number of rows that need to be inserted… for instance I can run the following:
insert into Table
VALUES (‘NH’, ‘&ID’, ‘&ZONEID’, ‘&AREAID’);
this will prompt the user to inser values for ID, ZONEID and AREAID.
how to I loop and make it work if there are say 5 rows to insert with this information?
Thanks,
Doug
Hey really nice explanations…
Thanks very much..
I have question. I have two tables and I am comparing it using query listed below
SELECT MIN(TableName) as TableName, ID, employee,unit FROM (
SELECT ‘Table A’ as TableName, A.ID, A.employee, A.unit FROM Table1 A
UNION ALL
SELECT ‘Table B’ as TableName, B.ID, if (A.ID>B.ID,’Y’,’N’)as Flag , B.employee, B.unit FROM Table2 B
) tmp
GROUP BY ID, employee,unit
HAVING COUNT(*) = 1
ORDER BY ID
getting result
Table A 15001 s1sangare ppm1
Table B 15948 d1wilson ppm1
Table A 15948 d1wilson sgi1
Table A 18548 d1mschroe ppm1
Table B 19546 n1mpeshma ppm1
my question : in above query it compars data if any changes are made to any of columns data is displyed like d1wilson.
I want to add flag if any one of column is change say N if both columns have same data than flag should be Y
Please help to redefine query
INSERT permission denied on object ’emp’, database ‘Test1’, schema ‘dbo’.
how to insert many thousands of records in a table using single insert that has four columns the first column is an auto number the second one takes value from another table column and the third one takes a static string value
insert into target_table(cols_list)
select col2,’value’ from source_table
Note that you need to exclude identity column in Cols_list
hello,
pinaldevo ,
i read your blog that is very effective , i am frsher working in vc++ developer as a fresher in small company,i have MCA from2010 in Jss engineering college,noida;
This makes your work more complicate. as a simple solution just use this.
USE DATABASENAME;
INSERT INTO tableName(col1,col2,col3….) VALUES(‘Record 1 val1′,’Record 1 val2′,’Record 1 val3’), (‘Record 2 val1′,’Record 2 val2′,’Record 2 val3’),(‘Record 3 val1′,’Record 3 val2′,’Record 3 val3’);
if you wanna use this with C# just put that into a string variable like this.
SqlConnection connectionVariable = new SqlConnection(“connection string blabla bla…”);
string cmdStr= “INSERT INTO tableName(col1,col2,col3….) VALUES(‘Record 1 val1′,’Record 1 val2′,’Record 1 val3’), (‘Record 2 val1′,’Record 2 val2′,’Record 2 val3’),(‘Record 3 val1′,’Record 3 val2′,’Record 3 val3’)”;
SqlCommand cmd = new SqlCommand(cmdStr,connectionVariable);
connectionVariable.Open();
cmd.ExecuteNonQuery();
connectionVariable.Close();
————————
Greeting from Rez.!
how to insert 1000 row in a table at a time?
insert into table(cols) select values…
GO 1000
Hi there!
Is it possible to insert the DEFAULT value in multiple insert statement.?
Thanks in Advance
Default values can be automatically added if the column is omitted in the insert statement
I have two separate table on my DB, they have the same column ttitles though with different rows length, so I will like to combine both into a single table.
I wrote the syntax below for that purpose, bit it doesn’t work
create table newtable
(col1,col2,col3)
go
insert into newtable
(col1,col2,col3)
select col1,col2,col3 from tab1
union all
select col1,col2,col3 from tab2
union all
;
It returned with tis error
“Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ‘;’.”
Please help
Great tip……going to get many uses out of this one!
Hi,
In table1 “4 fields” and table2 “3 fields” are there.
In table1 “4th filed” not null, here can i use use insert method with “SELECT” .
Regards,
Kumar. A
Hello Sir
I want to insert data in final table from test table in a single query… it is showing error in last line.. what is wrong there??
Please help me how can we insert data from different table of same schema into a single table?
select * into nitm_final
from ((select * from test_nitm_1)
union all
(select * from test_nitm_11)
union all
(select * from test_nitm_12)
union all
(select * from test_nitm_13)
union all
(select * from test_nitm_14)
union all
(select * from test_nitm_15)
union all
(select * from test_nitm_16)
union all
(select * from test_nitm_17)
union all
(select * from test_nitm_18)
union all
(select * from test_nitm_19)
union all
(select * from test_nitm_20)
union all
(select * from test_nitm_21)
union all
(select * from test_nitm_22)
union all
(select * from test_nitm_23)
union all
(select * from test_nitm_24)
union all
(select * from test_nitm_25)
union all
(select * from test_nitm_26)
union all
select * from test_nitm_27)
go
Got it..
I was missing alias name..
Add alias name at the last
select * from test_nitm_27) as t
go