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
Very good performance increase. THANK YOU, Pinal Dave!
I have to insert muliple records in two scenarios:
I have coverages and Rates, each location has muliple coverages and each coverage has muliple rates
coverageId 1
rateId 1,2, 3, 4,
coverageId 2,
rateId 1, 2, 3
Insert above into a table LocationRate that has
LovCovRateId, identity field,
LocationId
coverageId
rateId
I have to first select coverages for each location from another table
(select coverageId from locationInfo table)
Then refer to a rate table to get the rates for each coverageid (Select rateId from rates where coverageId = (select coverageId from LocInformation table where locationId = @locationId)
then get those rateId’s for each coverage Id’s and insert all the relevant coverageId AND their RateId’s into the LocationRate table.
So here there is a case of two scenarios of muliple inserts, muliple coverages and for each coverage muliple rateId’s
Pinal, I always look for solutions given by you, I feel you can help me on this, Any help will be much appreciated, thanks in advance!
i am enter value from one table to other,but in second table their is a primary key and it shows the error of primary key contraint.Solve this problem
Hi
I am new to sql server 2005. i’ve one table (Raw Material)if i updated any data in this table that data will be updated in all 5 tables.
Plz give me the solution
Any one can help me……
i want to insert multiple records into my table through SELECT statement,
and some times i may give different feilds I.e
insted of col1,col2,col3 ,
i may give only col1,col3..
like this……so
is it posiible in Sqlserver2005
plz rep me………
HI,
am write a query which retrieve from view and in that loop am passing set of condition and i have to retrieve some value from table2 by passing where condition ,
My Processing time gets slower when am passing my Second Query in First Loop.is there any alternative Way.
EG:-
Sql=”Select * from View1 Where Month=’Feb’ And Year=’2009′ ”
rs = st.executeQuery(sql);
While(rs.next())
{
String emp=rs.getString(“Empcode”);
String Name=rs.getString(“Name”);
float sal=rs.getFloat(“Salary”);
float Broughtforward=rs.getString(“Broughtforward”);
Sql1=”Select * from Table1 Where Month=’Jan’ and Year=’2009′
rs1 = st.executeQuery(sql1); // When
While(rs1.next())
{
.
. When am running this query my proccesing time gets slower
.
}
Int i =injsert into Table1 values (‘Empcode’,’Name’,’Salary’…)
Thanks in advance
how to display the result of joining of two tables , one returning more than one row and other having only one…
@shree
Check Ur Joining both the table should have one or more comman field thn u ill not have this problem Say for exampple
Employee: Empid,tokeno,name,Depid
Dept:Depid,dept..
now u can join Employee.Depid=Dept.Dept thn it ill return only one row.
hi sir,
i want’s to insert multiple data with entry of single data, for example, if i m inserting patient details, i want’s to insert his personal data as single entry and two medical tests and four medicines, then how do i insert all this data in a single table with no duplication of multiple entry(means obviously sigle entry data(patient’s personal details and unique ID) will be repeated, but two tests should not be repeated four times with four medicines )
plz, give reply as early as possible
@Sushant
You would need to normalize your database structure to one more level. From your post, I can definitely tell you that database structure is not designed properly.
you need to have one table that stores personal details, lets say TableA
Another table that stores Test results, lets say TableB
Another table that stores Medicines, lets Say TableC
And one common key that joins these tables, meaning implementing one to many relation ship, by creating foreign key constraints on tables.
Primary Key on Table A, Table A would be considered as Parent table and Table B and Table C will have a foreign key referencing to Table A.
The above explanation holds good for what you have mentioned in your post.
If you need more clarification, please post your table structure and some sample data.
~ IM
Thank you sir
I performed all database normalisations as mentioned by you, and i used patient_id as primary key in tableA and foreign key for remaining two tables(tableB & tableC). Now, i have to get a single crystal report from these 3 tables, but when i fire a query it again gives unwanted repeatations on crystal report. i might be wrong in applying a query, if possible give query for these three tables. i m posting table structure of all 3 tables.
TABLE-A
hospitalId varchar(50)(Primary key)
patientname varchar(50)
gender varchar(50)
age numeric(18, 0)
weight numeric(18, 0)
TABLE-B
hospitalId varchar(50)(foreign key)
ward_name varchar(50)
patientname varchar(50)
date datetime
test varchar(50)
TABLE-C
hospitalId varchar(50)(foreign key)
ward_name varchar(50)
patientname varchar(50)
date datetime
med_name varchar(50)
doses varchar(50)
duration varchar(50)
Now, on save click i insert two tests and four medicines at a time with all other data.
plz give query for retrieving these data in a single report
Hi,
m using excelsheet as a backend n inserting data thru’ oledb jet4.0 as follows
INSERT INTO [Sheet2$] VALUES(‘” + label1.Text.ToString() + “‘,'” + DateTime.Now.ToShortDateString().ToString() + “‘,'” + DateTime.Now.ToShortTimeString().ToString() + “‘)”;
but problem occurs whn it reaches at last record.it insert 4 records like
F02SD39999 12/15/2009 3:16 PM
F02SD40000 12/15/2009 3:16 PM
F02SD40000 12/15/2009 3:16 PM
F02SD40000 12/15/2009 3:16 PM
F02SD40000 12/15/2009 3:16 PM
I dont want duplicate record,so help me on that?
thanking you.
HOW TO COPY INTO SPECIFIC COLUM DATA INTO A TABLE USING “BCP ” PROGRAME IN SQL SERVER 2000?
EX:-
—————————
COL1 COL2 COL3
—————————
– 1 –
– 2 –
– 3 –
– 4 –
– 5 –
– 6 –
—————————-
Pls revert bak.
Hi,
I have to pass an string to stored procedure but varcgar(8000) may not afford its size since the string is toooooo long.Is ther any data type which can hold such long strings?(m using MS SQL Server 2005)
Thanks.
Hi,
I have to pass an string to stored procedure but varchar(8000) may not afford its size since the string is toooooo long.Is there any data type which can hold such long strings?(m using MS SQL Server 2005)
Thanks.
Use varchar(max)
hi all,plz hlp me out in dis….
m using oracle 9i sql*plus.
it is known dat d size of date datatype is 7….
but when i write- select vsize(sysdate) from dual;
output is 8…
or select vsize(to_date(’31-MAR-09)) from dual:
d output is also 8…bt if i write-select vsize(hiredate) from emp where deptno=30:
d output is 7…
can anyone explain please..
i want to know that how we can insert a marathi font inside the database using sql server 2005. please reply soon. i really need it urgently..
REGARDS
TRUNAL.
Hi Pinal,
When we use Select * into table1 from table2….
It will not create indexes of table 2 in table1…
How i can acheive this.
Hello Kishor,
SELECT * INTO clause is just to copy the data into a new table. It does not copy the table structure. To create index you will have to write CREATE INDEX statements after loading the data.
You can first create the table2 same as table1 with script that includes indexes and other constraints and then insert data using INSERT INTO statement.
Regards,
Pinal Dave
Thanks Pinal,
Can u help me…I hve read abt the global variables..so can we create global variables in sql server ? if yes than How
and how we can call them out of the script…
Thanks & Regards
Kishor
Hello Pinal Dave,
i need your help sir, actually..i try to making a query for fatching a record in different tables in different server Databases using sql server 2008.
so please help me out…
thanks
Atul
Hello Atul,
To fetch record from tables in different server database, use OPENQUERY function or 4 part naming. For that you must have linkedservers for different servers.
Regards,
Pinal Dave
Dear all,
in my case, the clients are inserting the data at time into the table.
i.e. multiple users are insert the data at a time .
that time i am facing problem,. each query taking one connection and it is not relasing quickly. so some users are lost their data.
kindly give some suggestion.