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
Sir,
I have an issue in SQL 2000.
i HAVE 5 fields in a table.
Have ot insert 5 rows at a time with same id.
For e.g.
If I have Empid = 5 ,
at a time 5 rows with 5 columns should be inserted.
What would be the single insert query for generating 5 rows with same empid???
Can you post some sample data with expected result?
hello
pinal
i want to insert records with where clause ..i.e
insert into table1(column1,column2)values(‘abc’,’xyz’) where name=’cccc’
can i??
I think you need to use an update statement. If not, give more informations
does this statement works with SQL 2005?
Which statement are you talking about?
sorry this sttement..
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
…
when i try for the 1st time, it didn’t work, but now it’s working for sql 2005 because the statement thant i wrote got error..
anyway tq pinal…
I want to get onefield like “subject” its data a,b,c,d,e
display different columns
a b c d e
below of a its information ,below b its information
give solution in crystal report2.0 with vb.net 2005
In Crystal reports, use a formula containing this code
replace({col},”,”,crlf)
and use this field in the report
hi,
i have table temp1
CREATE TABLE temp1(id int,name varchar(50),add varchar(50))
and another table student
CREATE TABLE student(name varchar(50),add varchar(50))
insert temp1(name,add) select(name,add) from student
but i want id of table temp1 automatic contain value start from 0, 1,2,3 and so on
after delete table temp1 when insert value through command
insert temp1(name,add) select(name,add) from student
then again id valu start from 0,1,3 and so on
how can solve this problem
Thank & Regards
Atul Prajapati
Make use of identity column for temp1 table
Hi,
I have a table of 30000 rows, and it made it work slower than separated insert statements.
Thanks anyway,
Chen
actually iu have a problem with sql ig auto gen
i used the following syntaxes
this one is working
CREATE TABLE new_employees(id_num int IDENTITY(1,1),fname varchar (20),minit varchar(20),name varchar(30))
but this is not working while inserting the values
INSERT INTO new_employees(id_num,fname,minit,name)values(‘hkdfgf’,’4535′,’kjlfkhg’)
and it is showing the following errors
Msg 109, Level 15, State 1, Line 1
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
It should be
INSERT INTO new_employees(fname,minit,name)values(‘hkdfgf’,’4535′,’kjlfkhg’)
I am using Java J2ee (struts framework) and MS SQL server 2005, My functionality (business logic) will be in the stored procedure.
The problem is how to send set of records (output of a query) to java. Likewise i need to send set of entered records to Sql Server, to be inserted in a table.
Please help on the same
Thanks
Rasheed
You can loop thru a Java code to send data to a table
HI Friends,
I need another urgent help with example.
Task:-
1.Create ssis package with import multi format file into same table like excel and xml and db resources.
2.And create loop for checking this data and import into correct table and error table
3. And create meta data and read this data path from meta table to import
pls any one my friends help me with small examples
Your query is very much help to work but can u help me
how can i generate this from asp.net page
i want to print next 100 books barcodeand i am creating it run time in a panel , but if there is any command to save all the record using single query run time then it save my executing time
plz help me
You need to use pagination. Search for the same in this site
hi madhivanan,
it could be consume more time as we are passing insert statement from the Java. i will increase IO operation from DB to Java.
Please advice
Thanks
Rasheed
How many row are you going to add at a time?
Dear Pinal,
I am new in to fields of Databases and i am interested in term DBA, but one thing really makes me confussing most of the time, when i am ask about the memory structure for SQL Server as you know every thing that is with the scopr of databases is mainly maintained and managed within the prescribed structre. so please can you elaborate the Instance and Datafile involvement in depth.
Thanks and Regards.
Ziyad Mehmood(DBA)……
May all u live long.this site is pretty much informative, appreciated.
i have a proble any one can solve my problem:
the problem is
i have a client side form having fields refno, company, amount, paid, through which a franchise can pay bills. i want that after 5 pm the form is automatically disabled and no bill payment can a franchise make after 5 pm. will u plz help me. its urgent
i need this in php. tell me about it a php query
ur admirer
web developer
sami
Which application are you using?
When saving data you can check for the time and display an alert if the time is aboe 5 PM
very nice site it realy helpful.
i have a problem in php, that is a form having fields refno, company, amount, paid which is used to make a bill payment for franchise. my problem is if a reference no is, say 111 of a particular bill, is paid in a particular month, if a franchise used to pay that bill in that month again,they should receive a message u can not pay this bill again it paid once in a month.
but in next month they are allowed to pay that bill.
plz plz solve my problem. i will be very thank ful to all of u fine people.
web developer
sami
You need to check for the payment date. If there exists data for this month for that bill, give an alert message
i am using SQL 2000 plz tell me insertion of records(i used above but there is coming eror)
What was the error you got?
good morning sir
i have employee table
some columns sal_id, sal_salary,sal_address etc
sal_salary
14000
12000
3000
12000
2000
4500
9800
12000
6000
2300
1200
1800
means toal column no 100
if i want to sum use select sum(sal_salary) as col name from employee
if i want to sum only for 10 to 50 column then i don’t know about answer this question
plz sir reply me answer this question
Post the exected result
hello sir
means sir , sir plz give me query for this question plz
thankyou
You need to post the exepcted result for the sample data
This is sourse table
eno,sal
1,10
2,20
3,30
destination table
eno,sal
1,10
2,30
3,60
how to write quaries?
select eno, sal from source_table
union all
select eno, sal from target_table
how can i insert bulk values at a time
ex:- i had a table custid and the table consists of single column i,e custid 1 to 100
table
i want to insert all the values at time how can it be done
insert into target_table(custid)
select custid from custid
tnks it helps me alot
i have table data in col1 and col2 like
28 10
25 25
28 58
30 42
i need result like
28 10
25 25
30 42
means no repeatation of col1 Plz advise query of sql 2005
select col1,min(col2) as col2 from table
group by col1
i am getting a error while inserting using a union all
drop table #t
create table #t (sno int,num varchar(10))
— Create a comma delimited string to test with
declare @str varchar(500),@sno int
select @str = ‘ABC|DEF|GHI’
select @sno = 123
–select @str = ‘4,2,7,7834,45,24,45,77’
——————————————————–
—- Code to load the delimited string into a table —-
——————————————————–
— Create insert for comma delimited values
declare @sql varchar(8000)
select @sql = ‘insert into #t select (@sno, ”’+
replace(@str,’|’,”’) union all select (@sno, ”’)
select @sql=@sql+”’)’
print @sql
— Load values from comma delimited string into a table
exec ( @SQL )
——————————————————–
——————————————————–
— Select values from temp table to show results
select * from #t
Error is:
Must declare the variable ‘@sno’.
But i have already declare that variable. What may be the error
select @sql = ‘insert into #t select (@sno, ”’+
replace(@str,’|’,”’) union all select (@sno, ”’)
should be
select @sql = ‘insert into #t select (‘+@sno+’, ”’+
replace(@str,’|’,”’) union all select (‘+@sno+’, ”’)