SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL

June 8, 2007 by pinaldave

Update: For SQL Server 2008 there is even better method of Row Construction, please read it here : SQL SERVER – 2008 – Insert Multiple Records Using One Insert Statement – Use of Row Constructor

This is very interesting question I have received from new developer. How can I insert multiple values in table using only one insert? Now this is 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 me focus on task, instead of copy paste. I have explained following script to new developer. 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 (http://blog.SQLAuthority.com) , SQL SERVER – Union vs. Union All – Which is better for performance?

Posted in Best Practices, Database, Pinal Dave, SQL, SQL Authority, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged SQL Union clause | 364 Comments

364 Responses

on June 20, 2007 at 1:52 am | Reply Required

I try to execute 400 insert statements to table about 20 columns (int or float). Regarding performance there is MUCH difference. ;)

INSERT statements way: about 5 seconds
INSERT SELECT UNION ALL way: 40 miliseconds!!!

thats funny isn’t it?

thanks a lot for this trick.
on June 24, 2007 at 8:41 am | Reply Ruba

I have a 200 rows to insert and I tried to use your way because it seemed much faster, but since I didn’t know how to end it properly, it generated an error.
that’s what I wrote:

INSERT INTO FACULTY (faculty_id, fac_name)

SELECT ‘F123′, ‘Jim Taylor’
UNION ALL

SELECT ‘F124′, ‘Amy Johnson’
UNION ALL;

Can you tell me what did I miss?

Thanks
on June 24, 2007 at 9:12 am | Reply pinaldave

You do not need last UNION ALL; as shown in example above.

Regards,
Pinal Dave (SQLAuthority.com)
on July 2, 2007 at 10:25 pm | Reply Gurmeet Singh

Hello pinaldave,
Myself Gurmeet singh working as Soft. Engg in Mohali.
I have read ur blog its really helpful.
Wish u very good luck.
on July 4, 2007 at 7:45 am | Reply Rajesh Swami

Hello pinaldave,

Thanks a lot to provide such hidden info about sql server
I never thought that we can do this with a single statement.
it’s really helpful us.
wish u all the best
on July 5, 2007 at 6:43 am | Reply Randhir Singh

Hi,
Thanx, it was very use full for me.
You have solved my big problem.
Thnx again.

RandhirSingh
Data Base Developer
Haryana(India)
on July 9, 2007 at 10:26 am | Reply David Barr

This solution is useful, but it does not appear to scale well. For 1000 records things went swimmingly. I then tested with 5000 records and an error was returned:

“Internal Query Processor Error: The query processor ran out of stack space during query optimization.”

Just thought I would provide a bit of warning. This does appear to perform better than a single command with multiple INSERT statements, but the cost in server resources may be a problem. Has anyone else experienced this?
on July 9, 2007 at 10:31 am | Reply pinaldave

Thanks David for sharing information here.
It also depends on the size of the table and row to be inserted. We are successfully inserting nearly 10K row using this union.
Regards,
Pinal Dave (SQLAuthority.com)
on July 15, 2007 at 11:37 pm | Reply Sumeet Bhasker

If you need to insert multiple records at a time then you may use a simpler syntax. Here is how it goes:

INSERT INTO YourTable (FirstCol, SecondCol)
VALUES (‘First’ , 1) , (‘Second’ , 2) , (‘Third’ , ‘3′), (‘Fourth’ , ‘4′) (‘and so on’) ;

This is what I use. It is simple and effective.

However, I wanted to know if there is any way to upload values in a batch from text file with values to a SQL database.
on July 16, 2007 at 10:40 am | Reply cybertoast

Sumeet, the syntax you provide is only valid on mysql, not sqlserver2005. i believe pinal’s method is the only one that really works (barring the aforementioned caveats, of course).
on July 18, 2007 at 10:18 pm | Reply Sri

Hi It’s helpful

But is thr someway where i can use a single insert to insert values for some columns from one table and the remaining columns from another table?

Sri
on July 18, 2007 at 10:21 pm | Reply pinaldave

Sure,

you can do something similar to following example

Insert into yourtable (table1col, table2col)
Select table1col, table2col
From table1 inner join table2 on table1.table1col = table2.table2col

Regards,
Pinal Dave (SQLAuthority.com)
on July 18, 2007 at 11:15 pm | Reply Sri

Thanks

Say if i don’t have a common field to join then what can be done?Coukd you pls tell me?
on July 19, 2007 at 5:53 am | Reply Kalpesh

Pinal,

Good to see your site & blog.
You could generate a script which does that, if the values are stored in some tabular format

e.g. if you have an excel worksheet containing rows with data – you could write a formula in an excel cell (for each row)

OR

you could write sql statement do generate insert statements
e.g. select “insert into mytable (field1, field2, field3) values” + field1 + “,’ + field2 + “,” + field3 “)” from mysampletable

No need to do copy-paste :)
on July 20, 2007 at 12:18 am | Reply Somesh Vashisht

Sir,
I want to know that how i can update one by taking data from other table.

For Exmp. There is one master table having col name ID, Amt.

There is one second table daily account( Id, amt_dipo). How i can update the master table in the evening

With Best Regards,

Somesh Vashisht
on July 30, 2007 at 10:14 pm | Reply Hari

Hi,

This blog is very good and helpful. I need to insert records into 2 tables at a time. Can you please tell me the procedure for it.

Thanks & Regards

Hari
on July 30, 2007 at 10:21 pm | Reply MrSnipey

In response to question 9:
Try ‘Bulk Insert’ to load data from a file.
on August 1, 2007 at 12:57 am | Reply Josh

Hello. Thank you for creating a website like this. It is very helpful.

I have a question. Is it possible for an INSERT command to fail?
In what situations would this happen?

I insert about 1000 records in a table and only the first 200 get inserted. The rest of the other 800 do not. I split the process by inserting in 5 batches, 200 records each and all are successfully inserted? What could be the reasons for an incident like this?

Thank you for any help you can provide.
on August 1, 2007 at 10:26 pm | Reply Sanjay

This blog is very helpful. I got a lots of things new here. keep it up.

Thanks.
on August 1, 2007 at 11:49 pm | Reply pavanich

can you please suggest an answer for my question
how can we insert the values into different tables at a time
on August 2, 2007 at 6:11 am | Reply pinaldave

Hi Pavanich,

We can not insert values into different tables at a time. We need to use multiple Insert statement.

Kind Regards,
Pinal Dave (http://www.SQLAuthority.com)
on August 2, 2007 at 10:16 pm | Reply pavanich

Hi,
thank you for your suggestion,can you give me an idea of how to insert data which is in the form of xml into a table.kindly explain with an example.

ragards,pavanich
on August 2, 2007 at 10:21 pm | Reply pavanich

Hi,
you have given suggetion for inserting multiple rows at a time in a table using query.but whats the difference between inserting the data using insert into query and the one you have given.even the one you have suggested is very big.i mean to say here also we use many statements.

Regards,
pavanich.
on August 2, 2007 at 10:24 pm | Reply pinaldave

Hi Pavanich,

The advantage of using the query I have suggested is performance and single insert statement (reduction in network traffic).

Regards,
Pinal Dave (http://www.SQLAuthority.com)
on August 5, 2007 at 10:48 pm | Reply suvarna

Hello ,

Really this is very useful site for me too…
on August 5, 2007 at 11:11 pm | Reply suvarna

Insert into Company(Name,Role_Of_Person)
Select Name, Role_Of_Person
From Company inner join Company_Person on Company.Name = Company_Person.Role_Of_Person

This is my query..

As i want to insert “Name,Role_Of_Person” values in 2 dif tables as..

Company & Company_Person respectivelly…

while i m executing this query in SQL 2005…getting error as

Invalid column name ‘Role_Of_Person’.

so i think it is due to..that it is column of 2nd table n i mentiond
INSERT INTO Company(As a 1st table)…

plz tell me how to resolve this…

thank u!
on August 9, 2007 at 7:03 am | Reply Ravi

Hi Pinal,
I need to duplicate the rows in same table , simply changing one field value. (Only 5 columns)
Sql is like this

INSERT SETTINGS (CATEGORY, NAME, OWNER,VALUE, SYSTEM)
SELECT CATEGORY, NAME, VALUE, SYSTEM
FROM SETTINGS
WHERE OWNER = ‘XXXXX’

Owner field will not accept ‘NULL’ values
I need to add Owner ‘YYYYYY’ and other column values taking from ‘XXXX’
Thanks in advance.
Ravi
on August 11, 2007 at 9:23 pm | Reply Paraminder

I think this should work.

select @yyyyy = xxxxx from yourtable

INSERT SETTINGS (CATEGORY, NAME, OWNER,VALUE, SYSTEM)
SELECT CATEGORY, NAME, @yyyyy, VALUE, SYSTEM
FROM SETTINGS
WHERE OWNER = ‘XXXXX’

Regards
Paraminder
on August 14, 2007 at 1:44 am | Reply Bijoy C

INSERT SETTINGS (CATEGORY, NAME, OWNER,VALUE, SYSTEM)
SELECT CATEGORY, NAME, ‘YYYYY’, VALUE, SYSTEM
FROM SETTINGS
WHERE OWNER = ‘XXXXX’

This will work

Regards

Bijoy C.
on August 14, 2007 at 4:45 am | Reply Sumonn

Hi,

I have to bulk insert into a table from a text file.

The following code is working fine

BULK INSERT Table_1 FROM ‘D:\Test\t2.txt’ WITH (ROWTERMINATOR = ‘\n’)

But if i want to set the textfile name form a variable then it is not working. Pls look at the code

DECLARE
@fname varchar(500);
BEGIN
SET @fname = ‘D:\Test\t2.txt’ ;
BULK INSERT Table_1 FROM @fname WITH (ROWTERMINATOR = ‘\n’)

END

Can you please help me in this regards.
on August 14, 2007 at 12:02 pm | Reply Cindy

Hi,
I was very excited about this piece of code but it did not compile at all.
I kept getting that From is expected in the expression.
I used the code as is from above.
I had to copy and paste.

Can you explain why this may have happened?
on August 14, 2007 at 12:36 pm | Reply pinaldave

Hi,
Did you change yourDB to “your databasename” example like adventureworks?
Regards,
Pinal
on August 15, 2007 at 8:11 am | Reply SQL SERVER - Insert Data From One Table to Another Table - INSERT INTO SELECT - SELECT INTO TABLE Journey to SQL Authority with Pinal Dave

[...] Both of the above method works with database temporary tables (global, local). If you want to insert multiple rows using only one insert statement refer article SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL. [...]
on August 16, 2007 at 9:01 am | Reply w1sdumb

>> INSERT statements way: about 5 seconds
>>INSERT SELECT UNION ALL way: 40 miliseconds!!!

the reason this happened is the data was already in memory for the second statement
on August 19, 2007 at 3:06 pm | Reply Sean

Hi :
I have a dilema and that is ; I have createda page that shows repeated records from one table for one Invoice ID
and I want to insert the itemized records to another table at once .
the problem is that some times these itemized records are 2 sometime are 10 or 14 records ( Repeated nest from a record set )
How I make sure that alll these records can be inserted to another table with one click of submit from one form ?

Thanks in advance

SEan
on August 19, 2007 at 4:09 pm | Reply pinaldave

Hi Sean,

Answer to your question is here : http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/

Regards,
Pinal Dave
on August 21, 2007 at 9:35 am | Reply Habs

is there any way of doing a sql insert where you inserting multiple values into 1 table and selecting 1 value from another table, i.e.

INSERT INTO table(field1,field2, field3) VALUES
(id,1) (SELECT id FROM table2 where field1= ‘hello)

?

thanks, most appreciated
on August 21, 2007 at 9:41 am | Reply pinaldave

Hi Habs,

I think you can do it in SQL Server 2008 constructing rows. I am not sure how you can do it in other versions.

There are alternate ways of creating temp table or using sub-routines.

Regards,
Pinal Dave (http://www.sqlauthority.com)
on August 22, 2007 at 9:45 pm | Reply prathiba

Hi ,
Is it possible to insert different values in multiple tables unconditionally by one insert query command?

eg: INSERT into table1(field1,field2,field3) VALUES (value1,value2,value3);
INSERT into table2(field1,field2,field3) VALUES (value4,value5,value6);
INSERT into table3(field1,field2,field3) VALUES (value8,value7,value9);
INSERT into table4(field1,field2,field3) VALUES (value18,value28,value38);

can you help me in this regard…
thanks in advance
on August 27, 2007 at 10:07 pm | Reply Dhayanethi

Hi pinaldave,

I tried your below query in SQL Plus it is not working

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
;

could pls help me on this
on August 28, 2007 at 3:04 am | Reply Mam

Hi Dhayanethi,

I think u r missing “GO” in the script.
on August 31, 2007 at 4:12 am | Reply Satyendra

Hi Sumeet Bhasker,

The example which you given is obsolete.I tried the same query in SQl Sever 2005 but it was not working.

Regards
satya
on August 31, 2007 at 6:23 am | Reply pinaldave

The example provided by Bhasker is for MySQL. SQL Server user do not try that.

Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on September 4, 2007 at 9:19 am | Reply vamsi

is there any way of doing a sql insert where you inserting multiple values into 1 table and selecting 1 value from another table, i.e. or inserting data from externally

INSERT INTO table(field1,field2, field3) VALUES
(id,1) (SELECT id FROM table2 where field1= ‘hello)

?

Thanks in advance
on September 4, 2007 at 9:58 am | Reply pinaldave

You can do something like this

INSERT INTO table(field1,field2, field3)
SELECT id,1,id FROM table2 where field1= ‘hello’

Regards,
Pinal
on September 7, 2007 at 5:22 am | Reply Hetal Dave

insert into dbtrelation (town_code, dscr,vo,b) values
select 23,relation_code,
count(case when status_code=’V’ then status_code end)as Victim,
count(case when status_code=’O’ then status_code end) as offender,
count(case when status_code=’B’ then status_code end) as Both

from name_file, offense_file, pdid_code p
where
name_file.offense_id=offense_file.offense_id and
offense_file.pdid=p.pdid
and date_offense>=’1/1/2006′ and date_offense=’1/1/2006′ and date_offense=’1/1/2006′
and date_offense<=’12/31/2006′ and pdid=11)

order by relation_code

This give me error like Incorrect syntax near the keyword ’select’. Please tell me how to resolve it.
on September 7, 2007 at 6:22 am | Reply pinaldave

Hi Hetal,

You have following code
insert into dbtrelation (town_code, dscr,vo,b) values
select 23,relation_code,

Replace it with following code (remove the keyword value)
insert into dbtrelation (town_code, dscr,vo,b)
select 23,relation_code,

Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on September 12, 2007 at 12:17 am | Reply pavanich

hi pinaldev,
i have a doubtregarding a query.
select * from table where name like ‘a%’ and group=’g’.i am getting an error saying incorrect syntax near keyword group. wherein name and group are column names of table. can you please suggesr an answer for it.is it that error becoz of group keyword
on September 13, 2007 at 9:00 am | Reply Neeti

Hi All,

I am inserting some 7 lakhs records into a custom table. I am using UNION in place of UNION ALL. Will it effect the performance ? When I am running this Script as Program from Front End it is taking hours to Complete…!!
Please help on this….

Thanks In Advance
Neeti
on September 13, 2007 at 10:02 pm | Reply pinaldave

Hi Neeti,

If your data is distinct you should use union all otherwise union.

Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on September 13, 2007 at 11:22 pm | Reply sathish

Hi Dave,

Is it possible to Insert/update a data in Multiple tables with single query?
on September 14, 2007 at 12:33 am | Reply pinaldave

Hi Sathish,

It is not possible.

Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on September 17, 2007 at 2:34 pm | Reply Joshy M Raj

Awesome !

What an increase in performance.Thank you pinaldave.Thank you Very Much.
on September 24, 2007 at 2:41 am | Reply swetham

Your site is pretty much helpful.
I have a problem and need a solution for it….
I have Multiple select statements from different tables in my stored procedure .Can i have a name for each select statement.
on September 25, 2007 at 2:45 am | Reply Zecc

Hi pavanich,

Surround group with []s, like so:

select * from table where name like ‘a%’ and [group]=’g’
on October 4, 2007 at 2:36 am | Reply Vishwanathan

i want to insert in one table and update in another table with one query how
on October 14, 2007 at 7:55 pm | Reply ifo

Hello,
Please I need advice.
SQL Server 2005. I have a table consisting of 200 fields with a primary key(auto-increment). I need to be able to duplicate via a stored procedure a specific row in the table identified by primary key value i.e. insert a record form that table into the SAME table …. the primary key of the inserted record so be incremented and I would need to change to of the field values in the inserted record (i.e. duplicating a row in a table with a primary key(auto increment). What is the best way to do this… one suggested writing to temp table then re-insert to original table. I have had some issues with this is there another method? Please not that other users may try and access the orginal record during this insert process. Can you give me example syntax and comments.
Thank You!
on October 17, 2007 at 9:37 pm | Reply ambily

hi all,
i wanted to know whether a insert statement can contain where condition in it.
on October 18, 2007 at 9:22 am | Reply karthik

Hi Pinaldave,

This site is awesome. I am a beginner and got stuck at some point.

My query is i have 2 tables (tblbuilding & tbltype) with columns as building, buildingid, type, typeid.

I have one main table tbltechen2 with columns group,seats,room,buildingid, typeid.

My question is when i submit the survey page with building and type specified, i should get the buildingid and typeid to be entered into tbltechen2 getting the buildingid and typeid from tblbuilding and tbltype respectively..

Can you tel me the query for inserting into tbltechen2.

Thanks a tonnnnn,
karthik
on October 23, 2007 at 6:01 am | Reply chiranjeevi

hi i am trying to insert data for 5 lines its giving following error,pls suggest me

INSERT INTO c2 (type, invtype)
SELECT ‘First’ ,’a’
UNION ALL
SELECT ‘Second’,'b’
UNION ALL
SELECT ‘Third’,'c’
UNION ALL
SELECT ‘Fourth’,'d’
UNION ALL
SELECT ‘Fifth’,'e’

ERROR: FROM Keyword not found where expected

please suggest me for this response
on October 23, 2007 at 10:31 pm | Reply Shreyas

Chiranjeevi, did you try giving semicolon (;) at the end?
on October 23, 2007 at 10:36 pm | Reply Shreyas

Hi Dave,

I have sql server 2000. It runs very slow. I checked in SQL Server – Current Log and it shows that database is backed up after every 2/3/4 hours. However, I checked the Database maintenance plan. There is no single plan wherein backup is scheduled on hourly basis. Is there any place else where backup could have been scheduled and I’m not able to track? If not then could you please let me know why am I able to see the messages in SQL Current Log?

Regards
Shreyas
on October 30, 2007 at 1:33 pm | Reply Yasser

Sir
I have the following procedure

————————————————-
declare @xmlData xml
set @xmlData = ‘

select …..
from FROM @xmlData.nodes (‘/NewDataSet/Table’) AS p(nref)
————————————————-
it takes 6 seconds

but when I write
————————————————-
declare @xmlData xml
set @xmlData = ‘

Insert into tempTable
select …..
from FROM @xmlData.nodes (‘/NewDataSet/Table’) AS p(nref)
————————————————-
it takes 10 minutes ?????
on November 2, 2007 at 2:25 am | Reply Amit Bhandarkar

this is a pretty neat trick. espcially in my java program where executing each insert equals one call to the executeUpdate method.
on November 2, 2007 at 3:17 pm | Reply Sawan

Is Union All works with Oracle
on November 3, 2007 at 9:03 am | Reply sridhar.r

I need to find out ,No of row inserted into the table like
SQL:> insert into table where some condition
Supppose if i fire this Query i need to know no of records inserted into table. Using java
on November 3, 2007 at 5:22 pm | Reply Dnyanesh

Hi All,
Using following code I am getting recods month wise
but the months are not coming in ascending order.

Select DATENAME(month, sportendt) as eventdt from sportsnews where sport_type=’Cricket’ and sportendt>=getdate()-1
GROUP BY DATENAME(month, sportendt)
HAVING count(*) >= 1

Help me

Dnyanesh
on November 5, 2007 at 7:01 pm | Reply Rama Krishna

Hi Shreyas,

First check the indexes status whether the indexes are fragmented or not and what is the scan density?

rgds
on November 6, 2007 at 3:18 pm | Reply Amit

hi Pinal…I am trying this UNION ALL trick to insert more than one row in the table on informix…But it does not seem to be working…. :(
on November 7, 2007 at 8:12 am | Reply Shreyas

Hi Rama Krishna,

Thanks for your reply.
Did you mean that the scanning density or index fragmentation could be the cause of why it is showing as “Database backed up” in sql current log?

Thanks again.
Awaiting reply
on November 7, 2007 at 10:53 am | Reply Shreyas

Thanks rama krisha.

Is that relevant to displaying “database backed up” every 3 hrs. in sql current log?

shreyas
on November 8, 2007 at 11:16 pm | Reply S

Is there anyway to do multiple inserts as in mysql?

e.g.

INSERT INTO sometable VALUES (
(a,b,c),
(c,d,e),
(e,f,g)
)

thanks!
on November 16, 2007 at 12:01 am | Reply Umer

i want sum thing different
insert into table1 ( c1,c2) values ( ‘1′,’2′)

insert into table2 ( c1,c2, c4) values ( ‘1′,’2′,’4′)

tell me i want to to excute abouve in same query and also once ne query fail they both roll back/
plz tell me how to do dat
on November 16, 2007 at 9:17 am | Reply Claudia

I am trying to insert multiple rows in a database using the “select” and “union all” statements. This is working great.
I am also using the @@identity + (variable – 1.toString) as my primary key.

I am getting a “primary key constraint error” in SQL Server. “Duplicate key not allowed”

I have tried to set Identit_Insert = On and then Off at the end of the query but am still getting this error. Does anyone know how I can fix this problem?

(I am doing this in order to migrate from mySQL to SQLServer) and was using last_Identity() instead of @@identity previously. I thought if I changed to the sql server naming convention to get the last id entered and set the Identity_insert to on/off it would work but it’s not working.
on November 19, 2007 at 5:51 pm | Reply Deepak

Hi all,

How to load bulk data in db2 database??
The database resides on OS/390 machine.

i want to load data in tableA(say) from other tableB(say0 which is in other DB2.. ..

Both the tables are same??? but resides on different DB2..

can anyone help on this???

I tried using export and import commands. but it takes a hell lot of time….

is there a way to load the data faster than this??………..
on November 20, 2007 at 10:45 am | Reply Shreyas

Hi Dave,

Presently I’m using in sql server below mentioned query for insert in a stored procedure. I have declared the variables @a, @b and @c respectively. Is this the correct way?
If I do it without variables I’m getting correct output whereas with the variables I’m getting duplicates. Can anyone please help?
Also the stored procedure returs me correct records as well as null records without variables. Is there any way of getting rid of NULL? I’m using cursor.

insert into
(select @a = a, @b = b, @c = c from a,b,c where a = b and b = c group by… order by…)

Thanks
Shreyas
on November 23, 2007 at 9:03 pm | Reply Simon

I have a similar query to one of the above. Perhaps you could help.

I have 2 tables Login and Salt

I want to insert UserName,Password into the Login Table and

instert LoginID from the Login Table and Salt into the salt table.

Please help

Regards

Simon
on November 26, 2007 at 7:02 pm | Reply Patrick Kursawe

The method using …. UNION ALL … brought me a slowdown of factor five. Seems it highly depends on the amount of data.
on December 4, 2007 at 4:26 am | Reply Liz

How do you remove orphan records from a table which was not referenced by a foreign key?

Thanks,
Liz
on December 4, 2007 at 3:12 pm | Reply Ravi

I have two database with same tables. Depending on a column value(takes binary value 0 or 1) in one table I have to copy those into another table if that column is 1.

how can i do this?

thanks in advance

Ravi D
on December 5, 2007 at 6:52 pm | Reply SATNAM SINGH

very good article i wanted to know that can we practice
administration part articles sitting on the local machine
because i tried to apply permissions to the user but was unable to do it

Thanks and Regards,
Satnam Singh
Database Developer
MUMBAI
on December 6, 2007 at 11:01 am | Reply shishma

very good article.

i tried , its working fine.

Thanks and Regards,
shishma
S/w engg
Blore
on December 8, 2007 at 12:06 am | Reply happyholiday

Hello,
I need help to insert multiple rows from one table to another.I have to do something like this.Select the one colum from the table and rest all i have to insert by myself.How do i do i?
on December 9, 2007 at 7:21 pm | Reply AHMAD

I USE SQLSERVER.IN MY PROJECT I IMPORT A DATABASE WITH MAXIMUM 20000 RECORD IN A DATA BASE WITH 200000 RECORD.IN THIS PROJECT WE COMPARE THIS IMPORTED RECORD WITH SECOND DATA BASE THAT NEED SOME INSERTING AND SOME UPDATING IN LARG SCALE DATABASE.I WORK WITH DATA SET AND I MUST COMPARE AND MANUPLIATE DATA IN FIELDS !! BUT THIS WORK GET ALOT OF TIME FOR UPDATE AND INSERT !! HOW CAN I SAVE TIME AND DO WORK FASTER.
on December 10, 2007 at 10:51 am | Reply sandevni

Hi..
I need to insert a clob type data into a table.
I break this clobe into few chunks and added them into a temporary table. Now i want to add those chunks into a one column of some other table.

it is not allowed to use text type data locally with in a stored procedure? how iam suppose to do this?

Thanks

Sandevni
on December 10, 2007 at 11:19 am | Reply shreyas

Hi Sql junkies,

I have a problem with scheduled backups. Any backups run through database maintenance plan or called by procedure xp_sqlmaint hang. They just show status as executing but nothing happens. I ran a profiler too and checked log messages, no clue. Nothing has been recorded in log, etc.
Please can somebody help me urgently?

Many thanks
Shreyas
on December 10, 2007 at 5:09 pm | Reply Hitesh Agja

Pinal,

I m Hitesh Agja and I have prolem with multiple insertion. My table has 10 fields including one identity field. and i want to insert more then 10,000 rows in just one shot…how can i do this…union all gives me “Insufficient system memory” kind of error. Plz help me out budy….i m trying with bulk insertion what are the other options for me pinal..

Hitesh Agja
(Ahmedabad)
on December 11, 2007 at 4:58 pm | Reply Hitesh Agja

I got the solution…faster then ever you have seen….bye guys….
on December 12, 2007 at 12:16 pm | Reply rahul sharma

hi…

pinal if for multiple record insertion i do not have fix number of records then how i can insert multiple insert using your union all type query………..actually i have tried and find error…bcoz …i have not fix record insertion
on December 19, 2007 at 8:24 pm | Reply amit

Hi Pinal,

I am using your way to insert bulk data. But i am facing a problem in this command.

if there are 100 records to insert if any of statement raises error, no record is insert into database table.

can i ignore these type of error and insert error free data.

Actaully i am read data from excel sheet and insert into sql database. there is problem of primary key constraints. i have made user define id and user id composite key.

so how could i achieve this thing
on December 21, 2007 at 10:08 am | Reply Tim

Super reference site – thank you for having this – very helpful!

I need to update many similar db’s with a similar record in one table – and most of the record is populated with my known text, but a few fields need to be carried over from another row (easily identified) in that same table (the value is obviously different in each db) – but in the same table. Can this be done with one statement or do I need to run an Insert followed by an Update statement? For example…
===
INSERT INTO TableXYZ
(Col1, Col2, Col3)
VALUES (‘Value 1′, ‘Value 2′, ‘Value 3′)
===
UPDATE TableXYZ
SET Col4=’Value 4′, Col5=’Value 5′
on December 21, 2007 at 10:39 am | Reply Tim

Super reference site – thank you for having this – very helpful!

I need to update many similar db’s with a similar record in one table – and most of the record is populated with my known text, but a few fields need to be carried over from another row (easily identified) in that same table (the value is obviously different in each db) – but in the same table. I want to run it in one statement (repeat it for each db), but will use two statements if needed. I can’t seem to get the Update statement to work though. Can you help???

For example…
===
INSERT INTO TableXYZ
(Col1, Col2, Col3)
VALUES (’Value 1′, ‘Value 2′, ‘Value 3′)
===
UPDATE TableXYZ
SET Col4= a, Col5 = b, Col6 = c
WHERE (Col1 = ‘Value 1′) AND EXISTS
(SELECT Col4 AS a, Col5 AS b, Col6 AS c
FROM TableXYZ
WHERE Col1 = ‘Old Known Value’)

Hoping you can assist – thank you!
Tim
on December 21, 2007 at 11:45 am | Reply Tim

Here’s what I figured out works if I use two steps:

(Step 1: Create a new record with known data in TableXYZ)

INSERT INTO TableXYZ (Col1, Col2, Col3)
VALUES (’Value 1′, ‘Value 2′, ‘Value 3′)

(Step 2: Update that new record by referencing data already existing in the table (ie: Col1 = ‘Value 1′)

UPDATE TableXYZ SET
Col4 = (SELECT Col4
FROM TableXYZ
WHERE Col1 = ‘Old Known Value’), Col5= (SELECT Col5
FROM TableXYZ
WHERE Col1 = ‘Old Known Value’), Col6 = (SELECT Col6
FROM TableXYZ
WHERE Col1 = ‘Old Known Value’)
WHERE (Col1 = ‘Value 1′)

- Thanks again, Tim
on December 21, 2007 at 8:52 pm | Reply Dipak

hi
can we insert multiple record at the same time in sql + ?
please help me..
on December 28, 2007 at 11:22 am | Reply swaminathan

can we insert records from excel or text in sql server2000?
on January 3, 2008 at 12:49 pm | Reply amali

how to use massage command in sp
on January 7, 2008 at 9:29 am | Reply Anandh S

Hai ,,,,

I have to insert the values based on three conditions .first i have to insert based on the id values and secondly based on the names and so ..what kind of stored procedure i have to write using sqlserver
on January 7, 2008 at 5:42 pm | Reply Purushotham Reddy

Hi,

Its a nice site dave. How to insert values into a table, where 1st column in static value and other columns are from select Query which will come from another table..

Thanks in Advance
on January 10, 2008 at 2:13 pm | Reply Chitra

Hi Dave,

This query really helped me a lot both in time and performance.

Keep the spirit going on
on January 10, 2008 at 9:19 pm | Reply Ryan F

Thank alot! This really helped…
on January 11, 2008 at 6:58 am | Reply Grant

Perfect! Saved me lots of time. I just inserted 4,358 records with one statement! “UNION ALL”

G
on January 12, 2008 at 12:02 am | Reply SQL SERVER - Execute Same Query and Statement Multiple Times Using Command GO Journey to SQL Authority with Pinal Dave

[...] by one of long time reader who really liked trick of SQL SERVER – Explanation SQL Command GO and SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL. She asked how can I execute same code multiple times without Copy and Paste multiple times in [...]
on January 12, 2008 at 7:33 pm | Reply Faisal Ahmed Qureshi

Hello pinaldave,
Myself Faisal Qureshi working as Soft. Developer in Mumbai.
I have read ur blog its really helpful.
Wish u very good luck.
on January 15, 2008 at 12:05 pm | Reply Indu P

Hi Dave,

This z a very good site and a beautiful and useful article. It really helped.. Wish u all the best and expecting much more from you.
on January 16, 2008 at 9:06 am | Reply srikant

Hi there…If there are say 10,000 records in a table..How do I select the second 500 records from it…i.e. the records from number 500 to 1000
on January 19, 2008 at 12:53 am | Reply CStroliaDavis

Hey srikant,

Assuming you are using SQL Server 2005, you can do that using the ROW_NUMBER function with the OVER clause.

Here is an example from msdn:

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS ‘RowNumber’
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;

you can find more info at: http://msdn2.microsoft.com/en-us/library/ms186734.aspx
on January 22, 2008 at 3:57 pm | Reply srikanth

Can we apply the same for multiple update statements
on January 26, 2008 at 10:51 pm | Reply Luis

How to build a unconditional “INSERT ALL” with can statement as Oracle
ej

insert all
into sal_history values(…..)
into sal_history2 values(…..)
select col1,caol2,…. from tableX
on January 27, 2008 at 1:58 am | Reply harry

hi there,

plz help me to find the solution

I have a table with 3 fields name,price and department.

now i want to

Insert the table with exactly 100,000 records. name field is to be made up of random length A-Z characters but never the letter M. However there MUST always be atleast 1 record of all possible field lengths i.e. 0 to 20.
The price number is a random between 0-10000
The departments are random from the following list
sales,computers,hr.
on January 28, 2008 at 10:42 am | Reply geetha

hi ,u can easily move the excel sheet data to the Sql server using DTS if using SQL 2000 and using SSIS for SQL 2005
on February 2, 2008 at 3:55 pm | Reply Martin

I wonder how to do insert query with more than one MS Access database in ADO?
eg:
database1, field1 (password=111)
database2, field2 (password=222)

How do insert field1 from database1 into database2 field2 with one ADO connection?

Thanks in advance

regards,
Martin
on February 2, 2008 at 8:20 pm | Reply HC

I have a source table, T1, with three columns col1, col2 and col3.

I have a destination table, T2, with the same three columns.

I want to move all the data from T1 to T2, deleting all those records in T2 where there exists in T1 a record with the same col1 AND col2 values.

In other words, a record is uniquely defined by the combination of col1 and col2.

What’s the best way to achieve the above?

Thanks
on February 4, 2008 at 10:52 am | Reply Manoj K Joshi

Its Really good and Interesting stuff.
on February 5, 2008 at 6:23 pm | Reply Rahul Jain

gud stuff
on February 5, 2008 at 8:27 pm | Reply Tejal

Hi Pinal Dave

i am facing problem in updating more than one nodes in a single xml document.

is there any way to replace more than one node in a sinle xml document

If not then what is the best way to achieve this?

Many thanks…..

Tejal
on February 6, 2008 at 9:51 am | Reply chandrashekar

Hi thanks a lot It is very much useful.

I want to know is there any thing to replace cursor in sql server.
on February 8, 2008 at 4:19 pm | Reply Dhruv

Hi Pinal Dave,
I am very glad to see your talent hear,
Its really good and intresting for all software engineer.
Thanks again dear.
on February 9, 2008 at 1:52 am | Reply Hitesh

select name from sys.objects where type = ‘c’ –CHECK Constraint
select name from sys.objects where type = ‘D’ –DEFAULT Constratint
select name from sys.objects where type = ‘f’ –FOREIGN KEY Constraint
select name from sys.objects where type = ‘l’ –Log
select name from sys.objects where type =’fn’ –Scalar Function
select name from sys.objects where type =’if’ –Inline table function
select name from sys.objects where type = ‘p’ –Stored Procedure
Select name from sys.objects where type = ‘K’–PRIMARY KEY & UNIQUE Constraint
select name from sys.objects where type = ‘RF’–Replication filter stored procedure
select name from sys.objects where type = ‘S’ –System Table
Select name from sys.objects where type = ‘TF’–Table Function
select name from sys.objects where type = ‘TR’–Trigger
select name from sys.objects where type = ‘U’ –User Table
select name from sys.objects where type = ‘V’ –View
select name from sys.objects where type = ‘x’ –Extended stored procedure

I want to store the result of every single query in one column in a table. Say First qery gives 40 records then those 40 records should be in first column, and if second query gives 23 records then those 23 records should be in second column, the way so on….So could any say how to do ? Which would be highly appreciated.
on February 9, 2008 at 1:13 pm | Reply Matt

Howdy –

Can I insert multiple rows into one table by doing a select on another table. For example:

INSERT INTO table1(col1,col2, col3)
SELECT col1,col2, col3 FROM table2 WHERE col1 > 5

Thanks, Matt
on February 12, 2008 at 7:25 am | Reply sveta

I browse and saw you website and I found it very interesting.Thank you for the good work, greetings:
on February 12, 2008 at 12:09 pm | Reply Murugesh

Hi Pinal,
gone thru ur blog…ur blog z highly useful… thx a lot man…
on February 12, 2008 at 8:15 pm | Reply Sharansh

Hi Pinal,
Fantastic work. Can make alot of people happy to see.
One question:
Employee table contains emp_id, other employee details and a column manager_id. Some of the manager_ids contains NULL. How can we update those nulls to zero USING self join.
on February 14, 2008 at 7:00 pm | Reply soundar

Hi to find last modfiy table and stored procedure in sql server 2000 And 2005

In My Table does not have creation date And Modify date field.
on February 15, 2008 at 2:15 am | Reply Phill

Question:

Can you do something like this in MSSQL?

INSERT IF NOT EXISTS
INTO YourTable (FirstCol, SecondCol)
VALUES (’First’ , 1) , (’Second’ , 2) , (’Third’ , ‘3′), (’Fourth’ , ‘4′) (’and so on’) ;

Thanks for any insight into this
on February 15, 2008 at 12:22 pm | Reply Piyush

Very helpfull and performance improving article.
Thanks
on February 15, 2008 at 4:00 pm | Reply Bala

Hi,
we are using SQLserver2000 in our application.In that we are
handling Chineese characters. Collation is “SQL_Latin1_General_CP1_CI_AS”. datatype is nvarchar.
But in most of the situation chineese characters displays as ??.
sometimes there is nothing displays. Pls. advice me to resolve this case.
on February 18, 2008 at 8:10 am | Reply Jorjie

Hi Dave I’m using Access how could I insert multiple records using SQL let’s say I got a Destination Database and a Source Database and the user could choose what table he/she would use and from the Source Database choose Table1 and in the Destination Database also Table1 and everything in that Source table would be copy or append to the Destination Table.

Insert into (Destination Table) in (Destination Database) Select
(Source Table.*) from (Source Database)

Please need help on this, Thanks in advance
on February 21, 2008 at 1:28 am | Reply Gyan Ranjan

Hi Pinal,

Thanks a lot for sharing this great information with us.
I need to insert mulitiple value in a table whose value will be decided at runtime. Also no. of value will be decided at runtime.
Is there any way to resolve it at backend.

Thanks and Regards.
Gyan.
on February 26, 2008 at 9:20 pm | Reply AJAY DHIMAN

I WANT TO CREATE A PROCEDURE IN SQL SERVER 2000 JUST LIKE THIS PLZ HELP ME BY GIVE PROPER SOLUTION

IF(SET @IN_SERVICE=’DEVOTIONAL’)
INSERT INTO TBL_DEVOTIONAL
IF(SET @IN_SERVICE=’ASTRO’)
INSERT INTO TBL_ASTRO
IF(SET @IN_SERVICE=’CRICKET’)
INSERT INTO TBL_CRICKET
on February 26, 2008 at 9:33 pm | Reply Nnenna

I’m trying to write a script that updates multiples tables at the same time.

For example, I’m trying to update an Employee Database where Name = John, i need to change the name John to Peter. (Database contains over 30,000 records )

I need the script to search all tables and update firstName where necessary
on March 5, 2008 at 4:40 pm | Reply Ankur

Hi
I have 500 customers in a table call PM_CUST_CURR.
THe Base currency is Riyals.
There are 40 currencys that are still to be uploaded for same 500 customers.
I am uploading all by benthic. but this a kinda donkey biz.
is there ne way to upload the customer with nase currency and then copy 40 currency for every customer though some script rather then uploading 1 by 1

Do reply
This is really useful
It will save a lot of time

Thanks
Ankur
on March 6, 2008 at 7:17 am | Reply Wyatt Wong

Can I perform the “Insert multiple records using one insert statement” task using a stored procedure ? If so, how do achieve this ?
on March 6, 2008 at 1:05 pm | Reply Agnel J

Very useful Query for Developers and DBA’s.
on March 7, 2008 at 8:06 pm | Reply Amand Kumar

Hi,

I want just thank you pinal dave for solution good very. It help problem solve sql server insert records lots. You best sql dev person and this site much nice. thank much, for problem my you solve.

Amand.
on March 12, 2008 at 7:51 pm | Reply Sbosh

I need help. i have 2 tables with 40 columns each and i need to insert the data from one table to the other. destination table is normalized but the source table isnt. so i will need to check for description to ascertain the relevent ‘id’ from the code tables. i need to generate a reusable script to perform this.

Regards
Sbosh
on March 17, 2008 at 4:04 pm | Reply adhi

Dear all,

Which is best performance (INSERT,DELETE and UPDATE) in ms sql

Regards
Adhi
on March 17, 2008 at 7:24 pm | Reply kameswararao

Hi Pinal,
Is it possible to insert multiple records by passing parameters?
if so, I requesting you to write me the example and also post mail to my email id ASAP.

eg. same above example to pass parameter values through a function. is it possible? please write in detail,
To understand in detail:
let’s say i’m reading inputs values from a column and saving into a DB table.(VBA perspective) i want to utilize effectively the SQL Server Database rather frontend functionality code.

Regards & thanks
Kameswararao
on March 19, 2008 at 7:22 am | Reply Dev

I get the foll error :

INSERT INTO [mxmc_db].[dbo].[AuditTrail] (Refid,jobNumber)
Select 01,01,01
Union ALL
Select 01,01,01,01,01,01,01,01,01,01
Union ALL
Select 01,01,01
Union ALL
Select 01,01,01,01,01,01,01,01,01,01,01,01,01
go

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
on March 23, 2008 at 4:31 am | Reply sravanthi

hi,
how can I find the username that starts with minimum 3 chars.Actually,I tried with like,but like is showing the rows with one character only,but how can I get username that starts with minimum charcters.
on March 24, 2008 at 4:58 am | Reply makaroo

I just want to thank pinaldave for the original tip above, then thank Sumeet for his improvement, and I will also thank MrSnipey for his suggestion regarding how to insert data from a text file via the bulk method, although I haven’t tried that one out yet.

Anything that can reduce the tedium of copy & paste is highly welcome. I was also positive that there must be a better way, as I could not imagine that experienced coders would suffer the infinite repetitiveness of the alternative of copy & paste. Most sites I found when I Googled the problem dealt with other issues than the solution here, which is what I was of course looking for. Thanks again all around!!!

Yours,
makaroo
on March 26, 2008 at 11:57 pm | Reply Felix Amoa

Hi Guys,

I want to combine something like 6O excel templates of the same structure into one template. I ‘ve tried ”SELECT…UNION ALL SELECT… ” with 3 templates which worked fine. But with 60 templates do I need to write 60 or so SELECT…UNION ALL SELECT… statements? What is the easy way out?

Help out please.

Regards,

Felix
on March 27, 2008 at 4:42 pm | Reply rani

hi ,
I get error

insert into my_dept (dept_no , dname , location)
select( 1,’research’ , ‘blore’)
union all
select ( 2 , ‘ad’ , ‘mysore’)
union all
select ( 3 ,’marketing’ , ‘hyderabad’)
union all
select ( 4 , ‘ hr’ , ‘mumbai’)
go
;

please help me , i want to know this command

Advance thx ,

rani
on March 28, 2008 at 5:12 am | Reply Simmo

rani, I believe your error is occuring because of your last semi-coln ‘;’

no ‘;’ is required after the ‘go’ statement

Cheers.
Simmo
on March 29, 2008 at 12:20 am | Reply Eduardo

Hello,

I need send all SELECT clause for parameter, because my INSERT is into store procedure. But i have problem with ” ‘ ” in varchar values. How to send this sentece as parameter?

Or

how to fix this code? if i want use variable:

declare @teste varchar(255);
declare @teste2 varchar(255);

set @teste = ‘INSERT INTO MyTable (FirstCol, SecondCol) ‘;

set @teste2 = ‘SELECT ‘First’ ,1
UNION ALL
SELECT ‘Second’ ,2
UNION ALL
SELECT ‘Third’ ,3
UNION ALL
SELECT ‘Fourth’ ,4
UNION ALL
SELECT ‘Fifth’ ,5 “;

exec (@teste + @teste2);

thanks
on April 1, 2008 at 3:53 pm | Reply spoorthi

please help

how do i insert e records records at a time suppose if i dont know no of records to be inserted. it depends on the selections of records.i mean inserting no of records changes each time.how do i insert by passing the variables.if the values is not hard coded.

thank you,
Spoorthi
on April 1, 2008 at 5:15 pm | Reply syed mazhar nadir

insert into company (‘EMPNO’, ‘ENAME’, ‘JOBS’, ‘MGR’, ‘HIREDATE’, ‘SAL’, ‘COMM’, ‘DEPTNO’)
SELECT ( 4587, ‘STANLEY’, ‘TEAM MEMBER’, 8745, ‘28-JAN-08′, 5000, 2100, 10)
UNION ALL
SELECT ( 4787, ‘THOMPSON’, ‘TEAM MEMBER’, 5825, ‘18-JULY-05′, 2500, 1100, 30)
UNION ALL
SELECT ( 2584, ‘ROGER T’, ‘TEAM MEMBER’, 8745, ‘28-JAN-08′, 5000, 2100, 10)
GO

sir i have made this statement the issue is that it says that the select statement is missing . it is unable to insert the muliple rows.pls tell me how to work on this statement .
on April 2, 2008 at 2:33 am | Reply PM

Hello
I have created three dimension table and one staging table want to load data into fact table .Getting an error
My Insert statement is

Insert into dbo.facthurricane (DIM1, DIM2,Nameid,WIND,PR)
select dim1 from dbo.locationD
SELECT DIM2 from dbo.DIMTIME
SELECT NameID from dbo.NameD
SELECT WIND from dbo.StagingHurrincane
SELECT PR from dbo.StagingHurrincane

MY select statements are fine but some INSERT is not working

Any help

-Praveen

I am very new to SQL world..
on April 4, 2008 at 10:56 am | Reply manojsemwal

hai dave how r u ……

i visit your web site it s really i a good enclopedia of SQL server.

i am facing one problem in sql 2000 server actually in my database iwant to see Roll
No and Degree No in order by Rollno .
it is not showing my query is like

select * from student where Uid=’A’ order by RollNo.
select * from student where Uid=’A’ order by DegreeNo.

some time it is working in sql query analyzer .but not working in jsp Script result.

Waiting for your Reply

ASAP

Manoj
on April 12, 2008 at 6:02 am | Reply Michel

HI , i have a problem
could ‘u help me please :)

y have 2 “select” one of those with IDENTITY and the SLQ7 do not allowed the UNION… for example

select name, age, ID = IDENTITY (int)
Into #tempTable
from Table1
where …….
UNION
Select name, age, 0
from Table2

why i want do that? … becouse …i have to UPDATE th TempTable but i don’t know whay don’t bellow to me do that
on April 16, 2008 at 8:40 pm | Reply Vijay

Hi Pinal,

Is there a way that you can insert values into multiple tables at one go. I am a beginner and I was writing a procedure, so got this question. Your input is greatly appreciated.

Thanks
on April 16, 2008 at 10:29 pm | Reply Niko

Is there any Examples to INSERT or (UPDATE) values in two different tables by writing only one statement in SQL Server 2000.
Case example: Say I have a THREE values. I want to INSERT two of the values in TABLE_ONE and the one value on TABLE_TWO.
INSERT is more important to me but as well UPDATE
on April 18, 2008 at 2:57 am | Reply Ragu

Hello Can you please tell me how I can INSERT multiple rows using checkboxes?
on May 7, 2008 at 4:25 pm | Reply Sushil

Your blog is very usefull!
Can u explain the difference between Union and Union All?
on May 8, 2008 at 5:11 pm | Reply allureana

Sumeet Bhasker stated that:

INSERT INTO YourTable (FirstCol, SecondCol)
VALUES (’First’ , 1) , (’Second’ , 2) , (’Third’ , ‘3′), (’Fourth’ , ‘4′) (’and so on’) ;

this is used when number of row is defined. What if the number of row is dynamic? Can someone help me on this? Thank You!
on May 9, 2008 at 1:36 pm | Reply AShish

hi pinal,

i have tried the option u have provided as below-

INSERT INTO mytable(id,name)
SELECT 1, ‘pizza’
UNION all
SELECT 2, ‘donuts’
UNION all
SELECT 3, ‘milk’;

but every time i get the error like-
ORA-00923: FROM keyword not found where expected

i also tried a another option-

INSERT INTO mytable(id, name) values(1, ‘pizza’),(2, ‘donuts’),(3, ‘milk’);

but i get the error like-

ORA-00933: SQL command not properly ended

Please, help me, tell me what wrong i m doing.
on May 10, 2008 at 12:14 pm | Reply Mushtaque

Hi, i have a one proble it is make me made and it’s happen so many time

i am using TDBGRID 7. OLDB and XArrayDB

i am using loop like that

For M = 0 To Myarray.UpperBound(1)
If Myarray(M, 1) “” Then

‘Insert Command

End If
Next

it is inserting twise a same record, Why ? I don’t have any idea about that if any budy know exact solution for that pls. reply on my email id

i have a one solution that is Primary Key but i can’t keep Primary key For Item Code . it can be dublicate saling same item.

now a am going to do ID Of Record No and ItemCode (ID + ItemCode) as a Primary key but

still question is hungup why it is happening like that

i have a dought some time network slow that time happning
but i am not sure about that

Pls. any budy know reply me.
on May 17, 2008 at 11:01 am | Reply Mithu

Well i liked ur site . i will soon post my probs regarding sql server 2005
on May 23, 2008 at 2:03 pm | Reply hnorden

For those using ORACLE:

Oracle NEEDS a FROM in the SELECT statement, so just use a FROM DUAL to make it work with ORACLE.

Example:

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT ‘First’ ,1 FROM DUAL
UNION ALL
SELECT ‘Second’ ,2 FROM DUAL
UNION ALL
SELECT ‘Third’ ,3 FROM DUAL
UNION ALL
SELECT ‘Fourth’ ,4 FROM DUAL
UNION ALL
SELECT ‘Fifth’ ,5 FROM DUAL
;
on May 24, 2008 at 1:22 pm | Reply Vasagar

If i use update command for particular table my condition statisfy for example three rows. I dont want update three rows, so i want update Row wise if one row updated means i want break.
on May 26, 2008 at 10:05 am | Reply mrbatchu

Hello,
I need help please send to my mail

Table
year,Sname,FName,RollNo,Class,paidDate,paidAmount

here i want to get grand total with all columns in the table
it should be displayed under Paidamount

please reply as soon as possible

thanking you
on May 26, 2008 at 10:06 am | Reply mrbatchu

Hello,
I need help please send to my mail

Table
year,Sname,FName,RollNo,Class,paidDate,paidAmount

here i want to get grand total with all columns in the table
it should be displayed under Paidamount

please reply as soon as possible

thanking you
on May 27, 2008 at 12:39 pm | Reply Zeenat

Hi Pinal,

The solution was very Useful….

Thanx
on May 27, 2008 at 6:06 pm | Reply ramya

USE master
GO
INSERT INTO emp (ename, eid)
SELECT ‘First’ ,4
UNION ALL
SELECT ‘Second’ ,6
UNION ALL
SELECT ‘Third’ ,7
UNION ALL
SELECT ‘Fourth’ ,8
UNION ALL
SELECT ‘Fifth’ ,9
GO
on May 30, 2008 at 3:56 am | Reply jmoss

Hi. Is there a way to do multiple insert statements while ignoring duplicates (varchar type) if the id is auto_increment?

Nice blog btw – thanks for sharing your knowledge.
on May 30, 2008 at 6:35 am | Reply sql_!!

Thanks!… this is an interesting solution
on May 30, 2008 at 1:28 pm | Reply Tom T

Hi mate.

First off, this is a fine solution to reduce network load if you have a lot to insert.

But suppose you never know how much needs to be inserted, eg somewhere between 1k and 25k records. You write a method to make a statement that inserts all the records at once (using your neat trick). Will it still work if you insert 25k records in one statement (assuming records of considerable size)? Does SQL Server impose a limit in this context? This should be documented, should it not?

Also, will SQL Server choke on making a ‘temporary table’ before inserting it into the target table if you have a lot of records to insert? I wonder what the slow down is in terms of records… Maybe SQL Server optimizes this and inserts the records into the target table at once?

Regards,
TT (BE)
on June 4, 2008 at 4:50 pm | Reply zaved

sir,,,i have two different values but column name is same and where clause column name is same…so how can i update record with it….

ex…
update tblName set Same_clmnName=’var1′, same_clmnName=’var2′ where differ_clmnName = ‘001′ . while variable value ill be one at a time….means either var1 having any value or var2 having any other value Plz help me….

zaved warsi
on June 4, 2008 at 5:22 pm | Reply zaved

query 1-
update tablename set clumName = ‘var1′ where id = ‘001′

query 2-
update tablename set clumName = ‘var2′ where id = ‘001′

i want to update record depending upon variable value if exits then update column name otherwise update var2 value…
on June 4, 2008 at 5:25 pm | Reply zaved

like this…

UPDATE feedbacktbl SET `fullname` = CASE WHEN `sn` =2 THEN “za” WHEN `sn` =3 THEN “ma” END ..

but here is different sn number……i want same sn number and same coulumname ‘fullname’ but different variable name…i hope u got it…
on June 9, 2008 at 11:06 am | Reply Star

haha, Good Blog for me, Fresh Uesers.

thank you!
on June 21, 2008 at 7:46 am | Reply Nash Vyas

SQL Integration Services package is the best way to import multiple rows (Provided the number is significant, otherwise the overhead of package execution can be an overkill) into a Sql Database.

Happy Coding
Nash Vyas
on June 23, 2008 at 10:51 pm | Reply gagan

sir
i want to insert values entered by user into the textbox to the table into database sir please tell me the command.

plz check this code

string insertSql;
insertSql = “INSERT INTO dcfuture.FIRST(username,firstname,secondname,age,rollno,email)”;
insertSql +=”VALUES(‘” & lastnametextbox.Text& “‘,’” & agetextbox.Text & “‘,’” & rollnotextbox.Text & “‘,”;
insertSql +=”‘” & emailtextbox.Text & “‘)”;
on June 26, 2008 at 11:58 am | Reply Awanish Rai

Hi All,
I want match where condition record in select statement,
suppose where condion have 3 record and i want to match 1
record and find result,and vice versa
my query is below

Select max(groupid) as Groupid,title from incidenthistory where title in (select Title from incidenthistory where GroupId=’2′ Group by Title) and assigneeid is not null
group by title

In above query if title come in groupid=4 then
it should not come when using query groupid=2

Any Help Appreciated to me……..

Thanks In Advance
on June 26, 2008 at 12:01 pm | Reply happy

Hi,
I want to insert values in a table from 2 different tables which don’t have any common field
say all columns of table 1 and then a single date field from table2

can u help plzzzzz..
on June 27, 2008 at 2:50 am | Reply Meshal

union all works great with up to 2500 records ..
if i use more the query returns -1 as number
of rows affected.

I am using sql 2005

Please help
on June 27, 2008 at 5:47 pm | Reply Steve

Great web page:

I’m trying to display rows of data that have duplicate records side by side with the miles increasing.

SELECT “CapDer”.”cder_capcode”, “basetableversions”.”TV_PubDate”, “FutureResidual”.”fr_ID”, “FutureResidual”.”fr_mileage”, “FutureResidual”.”fr_6″, “FutureResidual”.”fr_12″, “FutureResidual”.”fr_18″, “FutureResidual”.”fr_24″, “FutureResidual”.”fr_30″, “FutureResidual”.”fr_36″, “FutureResidual”.”fr_42″, “FutureResidual”.”fr_48″, “FutureResidual”.”fr_54″, “FutureResidual”.”fr_60″, “FutureResidual”.”fr_66″, “FutureResidual”.”fr_72″, “FutureResidual”.”fr_78″, “FutureResidual”.”fr_84″, “NVDPrices”.”PR_Basic”, “NVDPrices”.”PR_ModifiedDate”, “basetableversions”.”TV_PubSeq”
FROM ((“PUB_CAR”.”dbo”.”CapDer” “CapDer” INNER JOIN “PUB_CAR”.”dbo”.”FutureResidual” “FutureResidual” ON “CapDer”.”cder_ID”=”FutureResidual”.”fr_ID”) INNER JOIN “PUB_CAR”.”dbo”.”NVDPrices” “NVDPrices” ON “FutureResidual”.”fr_ID”=”NVDPrices”.”PR_Id”) INNER JOIN “PUB_CAR”.”dbo”.”basetableversions” “basetableversions” ON (“FutureResidual”.”fr_pubdate”=”basetableversions”.”TV_PubDate”) AND (“FutureResidual”.”fr_pubseq”=”basetableversions”.”TV_PubSeq”)
WHERE (“basetableversions”.”TV_PubSeq”=1051) AND (“FutureResidual”.”fr_mileage”>5 AND “FutureResidual”.”fr_mileage”15 AND “FutureResidual”.”fr_mileage”<21)
ORDER BY “FutureResidual”.”fr_mileage”

is it possible,

Any help appreciated.

Steve.
on June 27, 2008 at 6:03 pm | Reply Steve

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.
on June 27, 2008 at 6:12 pm | Reply Steve

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.
on June 27, 2008 at 9:19 pm | Reply Fouad

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
on July 1, 2008 at 2:06 pm | Reply Sapan Thakkar

thanks a lot….
on July 1, 2008 at 8:34 pm | Reply Dave

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!!
on July 2, 2008 at 4:11 pm | Reply SQL SERVER - 2008 - Insert Multiple Records Using One Insert Statement - Use of Row Constructor Journey to SQL Authority with Pinal Dave

[...] 2, 2008 by pinaldave I previously wrote article about SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL. I am glad that in SQL Server 2008 we have new feature which will make our life much more easier. [...]
on July 2, 2008 at 6:35 pm | Reply Jitu

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
on July 7, 2008 at 9:39 am | Reply Farid

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.
on July 9, 2008 at 9:07 pm | Reply Stimul8d

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!
on July 10, 2008 at 10:33 am | Reply VIJAY

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.,
on July 10, 2008 at 7:13 pm | Reply Prabha

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
on July 14, 2008 at 11:58 am | Reply payal

why are we using UNION when INSERT INTO is doing the same task.Is there any signigicant difference as regards query optimization?
on July 16, 2008 at 6:26 pm | Reply Nirmal Singh

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.
on July 17, 2008 at 7:43 am | Reply Imran Mohammed

@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.
on July 17, 2008 at 5:19 pm | Reply vijoy

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 ?
on July 18, 2008 at 1:48 am | Reply web design

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?
on July 18, 2008 at 8:19 am | Reply Imran Mohammed

@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.
on July 18, 2008 at 5:44 pm | Reply Giribabu

this statement is new to me, good to know that we can insert multiple record using select and unionall ,
on July 19, 2008 at 1:05 pm | Reply Rahul

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
on July 24, 2008 at 2:45 am | Reply Srikanth Kondeti

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
on July 24, 2008 at 5:14 pm | Reply Ravikiran

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.
on July 24, 2008 at 9:48 pm | Reply Imran Mohammed

@Ravikiran,

You mentioned one column name as “Source_Syatem” , is it correct, I think it should be Source System.

Second thing, if everything is correct, then may be the datetime values you are passing are incorrect, check the format how table is configured.

a) check if the date column has datetime datatype or character datatype.
b) if it is character then you need to change the values you are passing.
c) if it is datetime then check the formatt ( style of date).

Also check the datatype of “price” field, it is suppose to be decimal ( X.2) – where X can be any value.

Hope this helps

Thanks.
on July 24, 2008 at 10:55 pm | Reply saurabh

I am trying to insert above 5000 records in a database using the “select” and “union all” statements. i am getting error “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
on July 25, 2008 at 4:25 pm | Reply Anup

It really helps to insert multiple values in a single table.
Following is the one way by which we can insert the value:-

DECLARE @Authors TABLE(lastname VARCHAR(20), firstname VARCHAR(20))
INSERT Authors VALUES (‘asd’,'asd)
INSERT Authors VALUES (‘gfh’,'fgh’)
……
…..
…..
SELECT * FROM @Authors
GO
on July 28, 2008 at 11:27 am | Reply Madhusudan Pawar

Hello pinaldave,
Myself Madhusudan Pawar working as Soft. Engg in Mumbai.
I have read ur blog its really helpful.
Wish u very good luck.
Thanks
on July 28, 2008 at 4:53 pm | Reply Akhil Raj

hi Pinal,

I read this articles. very nice. Can u tell me some more.
Actually i used one stored procedure for inserting multiple values from asp.net. The number of rows inserting is according to the user requirement. So i create a command object with the stored procedure name with parameters and put one loop statement for inserting the values. here u provide one method for multiple insertion but i think this method is static ie if i put 5 rows i can use 4 union all like that. But i cant assure the number of rows. so how i can do with one insert query or stored procedure.
on August 1, 2008 at 8:36 pm | Reply Deepak

Dear Pinal Dave

I have a field named reminder as datetime in database.

I need to check whether today’s date and month are equal to the reminder date and month. don’t consider year.

So how this can be compared.
Please Help

Regards
Deepak Biradar
on August 2, 2008 at 12:19 pm | Reply shan

how can i isert more than one rows in a same field, one of the field is set as primary key. inserting each row is not possible . please give a solution.
on August 4, 2008 at 3:54 pm | Reply raina

Hi Everybody….i want to write sql script which will insert 1000 data in a table..and i need to complete the script by 2morow morning..i need to auto increment the number i.e..422000001199,the last 4 number needs to be changed..any idea how to proceed plz..thanks in advance
on August 4, 2008 at 4:55 pm | Reply Viny

Why don’t you use newid() which will generate auto ids for you.
on August 4, 2008 at 4:58 pm | Reply Viny

select newid(), contactname
from Customers

I have used northwind database.
on August 9, 2008 at 6:40 pm | Reply pankaj

i want the syntax of inserting multiple rows with
insert into tablename values(‘&col name”);
on August 15, 2008 at 7:36 am | Reply Aleem Latif

Hi!

Good work guys …

I tried to migrate a table from MySQL into MSSQL 2000 using the same technique mentioned in this website using multiple SELECT and Union Alls .. but the trouble is it works fine on SQL Server 2005 Express till about 2000 records … but in SQL Server 2000 I have been facing:
“The query processor ran out of stack space during query optimization” error …
So, be carefull if you are running this query on SQL Server 2000… Due to 8k limit of Row Data in SQL Server 2000 and SQL Server 7.0.

Cheers

Aleem Latif
————–
on August 21, 2008 at 9:36 pm | Reply Anirudh Sood

Sir,

i want to insert records in a table but in ascending order by name or ID.

how to do that.

regards,

Anirudh Sood

Programmer
on August 22, 2008 at 5:13 pm | Reply Leigh

Oddly, this approach was way slower for me.
on August 26, 2008 at 10:47 am | Reply monaz

hi Thanks the solution. but i need to insert 500 records at a time and inserting for different values every time is difficult.. plz can anyone help me on this.
on August 29, 2008 at 12:27 pm | Reply Siva

this is very useful to new developers such as me, very thanks to the author.
on September 2, 2008 at 7:30 pm | Reply teoman

thank you! you saved me :)
on September 2, 2008 at 11:04 pm | Reply rohan

i have a problem
all of my data is stored in a text file, each column seperated by a comma, and each record in a new line

how can i insert this data in an oracle sql table
on September 4, 2008 at 12:13 am | Reply millie

This insert into -select works great, thanks very much
on September 8, 2008 at 10:09 am | Reply surendar

Hello sir,

how to insert the records for multiple tables using one form
on September 10, 2008 at 3:28 am | Reply Cam

Hello

Is there a way to do this in SQL Server 2005?

Insert into Table1 (a,b,c,d,e)
values
(select fruit from Table2, ‘apple’, ‘pear’, select vegetable from Table3, ‘banana’)
on September 10, 2008 at 9:00 am | Reply dmandman1

I’m attempting to pull distinct records from Access. When I use:

SELECT DISTINCT MyField1 INTO MyUnion2 FROM MyUnion1

I get back 131M rows, which is what I want. But, when I use:

SELECT DISTINCT MyUnion1.Field1, MyUnion1.Field2, …… INTO MyUnion2 FROM MyUnion1

I get back 190M rows, which is not what I want. How do I get Access to see the “DISTINCT MyUnion1.Field1″ in the second example?
on September 14, 2008 at 5:05 pm | Reply Sourav

While trying to check for the bulk import operation, I am getting error message.

Please help to fix this problem -

steps:

1. First I created a table named City and inserted records -

create table city(cityid int identity(1,1) primary key,cityname nvarchar(10) unique)

insert into city(cityname)
select ‘Kakinada’
union all
select ‘Guntur’
union all
select ‘hyd’

2. Exported the records of the table to a new txt file.

2. now I created a new table city1 which the same table structure as City.

BULK INSERT city1 FROM ‘C:\city_1.txt’ WITH (ROWTERMINATOR = ‘\n’)

Getting the error message -

Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature.
Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature.
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.

Thanks
Sourav
on September 19, 2008 at 3:58 am | Reply shaik

hi pinal

how r u . Hope ur fine by the grace of god.

i’ve query that suppose i want to insert a complete row between the rows . how should i insert . can u plz help me.

like this

EMPID EMPNAME
——– —————
1. 111 IMRAN
2. 112 PINAL
4. 114 SHAIK

So now i want to insert between 112 and 114 i.e (113,Rohit)

o/p should be like this

111 imran
112 pinal
113 Rohit
114 shaik

Thanking u very much.
shaik .
on September 19, 2008 at 4:06 pm | Reply Pavan

Hi,
I was getting the following problem when i used union all”
“Maximum number of user tables under all sides of a UNION statement can not exceed 256. Please simplify the query and retry”. But this went away when i used Union instead of Union all. I will be inserting huge number of rows around a lakh or even more. Will it be safe for me to use it?
Any help will be very much appreciated.
Thanks
Pavan
on September 23, 2008 at 8:48 pm | Reply Rikin

Hi Pinal,

I am currently working on MySQL and SQL Server 2005 databases.

I have created stored procedure in SQL Server 2005 what it does is..It calls the stored procedure in MySQL and try to insert 5 milions of records from one table to another in MySQL and once it is done, It will try to fetch all data from second table and insert into SQL Server table….

but this whole process takes lot of time to process so can you show me more efficient and best way to do it….

Repsonse will be very much appreciated…
on September 25, 2008 at 4:29 am | Reply Shimaa

Gr8 way, thanks, it worked fine, good job
on September 25, 2008 at 11:32 am | Reply priya

I am quite impressed by this site learned many new things. I hav query i guess i will get help from here for sure.

In my table i have 4 column 1st column is PK with Auto numbers, and in third column i have some value where i have to merge this PK col value.
Id name price merge_col
1 ABC 11 xyz_1
2 sdf 12 god_2

to achive this i am inserting a record and then retrieving the Id filed value and again using update query to change merge_field.

So is there any way to get the value of Id field as soon as the value is inserted in that field and i can use it in the same insert query for next column
on September 26, 2008 at 8:22 pm | Reply Rikin

Hi Priya,

What you can do is

INSERT INTO Test([NAME]) SELECT ‘XYZ_’ + CAST(MAX(id) + 1 AS VARCHAR) FROM test;

Try this out…Chage table name and column according to yours.
on September 29, 2008 at 12:08 pm | Reply Nilesh

Hello Sir,

I am nilesh chaudhari. I have to store a gujarati text

value in sql server 2005.

And also in textbox i have to write a text in gujarati in asp.net 2.0 window applicatiion.

Plz, help me.
on October 1, 2008 at 6:07 pm | Reply Durai

Hi,
I am Durai.How to retrive 7th record in sql server 2005 with out using field condition like( where Empid=7). Is there possible to retrive using rownum=7 in sql server 2005

Plz Tell me
on October 3, 2008 at 11:09 am | Reply Trilok Giri

Hi Sir,

insert into table1( col1, col2, col3, col4, col5, col5)
( SELECT * from table2)

Suppose table2 contains 1000 records, while insertion it throw error on 251 row, it’s all rollback. But i dont want to rollback all. I want keep as it is the inserted record, it generate a log for 251 and the insertion is continuing from 252 row onwards.
For this i need to use a cursor.
But i’m not dont no the pros and cons for the cursor.
Is there any other approach to solve this issue?
Let me know ASAP
on October 7, 2008 at 9:58 pm | Reply SQLTech

Hi,

Pls suggest an SQL query to get a list of all tables that are neither referencing nor referenced by any other table.

Thanks for your help
on October 14, 2008 at 3:25 pm | Reply Sello

Dear all

I am using visual basics 6 and I am trying to insert records from a select statement into the table. The count for the select statement shows that there are 1 136 098 records. The insert statement makes the system to hang as it is trying to insert 1 record at a time.Can you provide me with the altenative code to insert records in bulk rather?
on October 16, 2008 at 9:23 pm | Reply Vishal

Hi,

How to create a array object in sql Server 2005
I want to Multiple insert into Mapping table so i don’t
want to exe every time sp from front end only pass the array
object from front end and insert value in mapping table

“I already use split function for string data type”

Thanks & Regard

Vishal Jain
on October 18, 2008 at 11:51 am | Reply senthilsjc

name,id,age,address,addresstype,hiredate ..how to normalize this…
on October 27, 2008 at 11:07 pm | Reply Ljubomir

Hi,
I have two tables and i want to insert multiple values in FirstTable for all primary keys from secon table and some fixed values.
METACODE example

insert into TableOne (value1, value2, value3)
values (12, primary keys from tableTwo,2009)

something like this

12 Primary_key_from TableTwo1 2009
12 Primary_key_from TableTwo2 2009
12 Primary_key_from TableTwo3 2009
…
12 Primary_key_from TableTwon 2009

SQL2005
on October 29, 2008 at 3:52 pm | Reply Kundan

Hi Sir,
Today I did browse your website, It’s really a nice work carried out by you for the programmers either very new or old but in dialemma at some point.
Thanking Again
BEst Regards
Kundan Kumar
on November 1, 2008 at 4:47 pm | Reply Atul

Sub : Regarding duplicate value…..

Hai sir,

I have two tables that are..
1. tbl-a
2. tbl-b

tbl-a contains fields
1.btblc_no
2.btblc_value

and tbl-b contains fields
1.btblc_id
2.btblc_payment
3.btblc_date

now my query is join two tables and remove duplicate value if there is any from tbl-a as it btblc_payment is more time but btblc_value is one.

my qyery is coming like that way is as below

btblc_no btblc_value btblc_id btblc_payment btblc_date
——— ————– ——— —————– ————–
101 5000 1 3000 10/10/2008
101 5000 1 2000 12/10/2008
102 2000 2 Null Null
103 1000 3 Null Null
—————————————————————————-

But I want this result like this way is bellow..

btblc_no btblc_value btblc_id btblc_payment btblc_date
——— ————– ——— —————– ————–
101 5000 1 3000 10/10/2008
101 Null 1 2000 12/10/2008
102 2000 2 Null Null
103 1000 3 Null Null
—————————————————————————-

OR..

btblc_no btblc_value btblc_id btblc_payment btblc_date
——— ————– ——— —————– ————–
101 5000 1 3000 10/10/2008
Null Null Null 2000 12/10/2008
102 2000 2 Null Null
103 1000 3 Null Null
—————————————————————————-

Pls Pls Pls help me out of this problem..

Best Regards
Atul
on November 3, 2008 at 2:17 pm | Reply Tejasiwni

Hi,

i have about 2000 records in an excel sheet. the records need to be written to ms sql server 2005. I have to insert them in batches. Please help me. i have to take up first 100 records from the excel sheet, write it to a batch file. at the end, i need to execute these batch files in one go……. so that the time consumed is less…….. please help me.
on November 3, 2008 at 4:23 pm | Reply sivaani

hai i using stored procedure to insert records in to the database table taking only 99999 records not morethan that please tell me where i have to do changes

Thanks in advance
on November 13, 2008 at 6:42 pm | Reply Ijaz

Hi Pinal
This was realy an informative forum for me. I want to ask one question if u can answer that what is the replacement of [Sum(column) over(partition by column order by column) as Alias]
in sql server 2000. This function is supported in sql server 2005,2008 and in oracle.
Regards
Ijaz, Pakistan
on November 18, 2008 at 8:26 pm | Reply RANI

hi,
i am new to sql server 2000

I have a table W_ORDER_F with 64 coloumns and 532 rows . i need to create a backup table W_ORDER_FBKUP.

can you healp me to get the syntax?

with regards
Rani
on November 21, 2008 at 9:54 am | Reply Nalaka

Hi !!

I created Unicode DB using sql 2005 and insert value using this statement,

Insert into Test1(Name,Address ) values(‘vasana’ ,N ‘Ήεàĺћ & §àεý’ );

This work fine,

The problem is with my select statement,

select Name,Address from Test1;

it give result like this

vasana , ???????

Can u please tell me how to write proper select statement to get Unicode value .

Thanks.
on November 22, 2008 at 6:35 pm | Reply jikku

hi,

java and sql query for getting primary key of recently inserted row
on November 25, 2008 at 5:11 pm | Reply Rakesh Kumar Jaiswal

Hi,

which one better ‘OR’ or ‘IN’ to filter a result set
on December 1, 2008 at 8:34 am | Reply Jeet

Hi Pinal,

I have 2 tables as below :

1. mst_Customer
2. mst_Installment

mst_Customer contains customer infor with unique id which is tagged with mst_Installment.

I would like to retrive only 1 record from mst_installment on the basis certain criteria like date etc. and would like to show against mst_customer table record. Query I am ready with return proper record if I use top 1 but when it comes to more than that result is not proper at all..
on December 3, 2008 at 6:16 am | Reply praveena

Hi Pinal,

I accidentally browsed ur blog.Its very informative.keep up the good work..when i followed ur same techinque and got this error,can u pl help. iam new to this sql concepts

SQL> insert into employee_usa(e_id,e_name)select 100,’pranu’ union all select 101,’satyam’;
insert into employee_usa(e_id,e_name)select 100,’pranu’ union all select 101,’satyam’
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
on December 4, 2008 at 3:22 pm | Reply Babu

Hi Pinal,

I am trying to insert data from one table to another using a procedure,but its erroring out.

Insert into table2 select * from table1 where col1=@var

Error:Incorrect syntax near Insert statement

Regards
Babu
on December 5, 2008 at 10:13 am | Reply Imran Mohammed

@Babu,

Script what you posted seems to be incomplete, and may be error is in the script which you did not posted here… I dont see anything wrong in your script you posted. Just check the line above insert statement, make sure you declared @var variable…

here is an example…

CREATE TABLE table1 ( STATUS VARCHAR(50))
go
INSERT INTO table1 (STATUS) SELECT ‘GREEN’
go
CREATE TABLE table2 ( STATUS2 VARCHAR(100))
go
DECLARE @var varchar(100)
SET @var = ‘GREEN’
INSERT INTO table2 SELECT * FROM table1 WHERE STATUS = @VAR
go
select * from table2
go
drop table table1, table2

works perfectly fine with me…

Regards,
IM
on December 5, 2008 at 4:06 pm | Reply Prakash

Hi Pinal,
Good to see your website which gave a lot of solutions.

And I have a question..

Why we go for table variables rather than cursors and views?

Can you please clearify?

Thanks

Regards Prakash
on December 5, 2008 at 4:34 pm | Reply Prakash

Hi Praveena,

insert into employee_usa(e_id,e_name)
select 100,’pranu’
union all select 101,’satyam’

Its working in sql server 2005..

Which DataBase are you using?

Regards Prakash
on December 5, 2008 at 4:57 pm | Reply Prakash

Hi Durai..
I just went to this site today..and cheked your comment..

How to retrive 7th record in sql server 2005 with out using field condition like( where Empid=7). Is there possible to retrive using rownum=7 in sql server 2005

so u can do like this

select * from Table where ID=
(SELECT MAX (ID) FROM Table WHERE ID in
(SELECT top 7 ID FROM Table ORDER BY ID ))

This is not only for ID ,from any column name u can get the corresponding row..

Regards Prakash
on December 5, 2008 at 5:22 pm | Reply Prakash

Hi Durai,
Even better way for that…

select top 1 * from Table where ID not in (select top 6 ID from Table)
on December 9, 2008 at 1:40 pm | Reply Krishnangshu

Hi All,

I want to get max id from that table where i am inserting data by ‘Select’ statement.

I have follwing T-SQL and Function:-

1. T-SQL:::::

INSERT INTO Risk([Description],RiskCode)
SELECT distinct(Risk),dbo.ReturnUniqueRiskID(Risk)
from ImportRiskControlErrorLog

2. Function:::::::

ALTER FUNCTION [dbo].[ReturnUniqueRiskID]
(
@Risk VARCHAR(50)
)
RETURNS VARCHAR(50)
BEGIN
Declare @UniqueRiskID VARCHAR(50);
SELECT @UniqueRiskID=’R-’ + CAST(MAX(CAST(SUBSTRING(riskCode,3,9) AS INT)) + 1 AS VARCHAR(50)) FROM Risk
WHERE RISKCODE LIKE ‘R-%’

IF @UniqueRiskID IS NULL
Set @UniqueRiskID=NULL;
Return(@UniqueRiskID);

END

Regard

Kris
on December 16, 2008 at 3:39 pm | Reply Chandra Sekhar

You are really greate Dave. The article was posted nearly 1 and half year back. Still there are comments running on this article. This is the first time I have ever seen. Keep it up Dave.
on December 16, 2008 at 5:58 pm | Reply Rumba

Hi Pinal,

Can you please suggest your favorite method from above article when you have 50 insert statements(4 fields to update) with each field’s value not exceeding 100 char – there may be 50 concurrent users ?

Many Thanks,
Rumba.
on December 17, 2008 at 12:05 pm | Reply Sukitha

Hi all,
can you please send me coding for the insert the values from different forms. my coding capture the 2nd form values only. this is not capture the first form. so please help me

thanks
on December 19, 2008 at 5:14 am | Reply Tamer Yousef

This is a good suggestion it does not perform well when inserting lots of records (20K in my case).

I did a comparison between the Method above (using Union All) and using a stored procedure.

I’m trying to insert:
total columns in table: 2
columns to insert: 2
rows sample: 20,000 rows
data types: int
table status: empty before insertion

Method one:
—————
using stored procedure:
basically the sp takes two parameters, one for each column, and it will do a straight insert into the table as in:

————————————————–
Create PROCEDURE [dbo].[sp_record_Insert]
@id1 int,
@id2 int
AS
SET NOCOUNT ON
insert into myTable(id1,id2) values(@id1,@id2)
————————————————–

and the calling code is like:
————————————————–
exec sp_record_Insert 140319,47857539
————————————————–
this is executed 20,000 times to insert 20,000 records

TOTAL Execution time: 3 seconds

Method 2:
————
Using the Union All method took: 5 minutes and 9 seconds.

So I beleive you can use the union all method on smaller number of records, but once you go beyond that, you are better off trying something else.
on December 22, 2008 at 11:19 pm | Reply Crosby

Hi Folks, wanted to report my real-world test on this one.
We have a database operation doing 2.5MM row data loads on a daily basis – so performance is of interest.
We ran a test with the 3 different syntaxes from above:

1) many individual inserts, one per row
2) one multiple-row Insert, one total for all rows
3) Union All

Loading into a 9 column table with a couple of indexes on it, we found the following:

1) 00:03 – Even with the extra overhead of the command, this is by far the fastest for us.

2) 00:10 – A little slower.

3) 01:30 – The slowest of the bunch. Maybe better for smaller sets?

We ran on Windows Server 2008, with SQL 2008. This is a quad-core with Intel 2.83GHZ cpus, 8GB RAM, and a 7200 RPM data-drive.
on December 22, 2008 at 11:21 pm | Reply Crosby

oh, um the test was run on 5,000 rows.
on December 26, 2008 at 9:29 am | Reply Mahesh J.Rane

Hi Pinal,
iam Really very happy to see your website.It is really Healpful for me.
Best of luck
on December 31, 2008 at 12:55 am | Reply Sri

Hi Pinal,

i am trying to do multiple inserts from a table to the same table by changing a field value and i am also have the increment the id ( primary key ) by 1 with each insertion.

How can i increment the recordid with each insertion?
( i should not use identity auto increment )

i tried …

insert into table (id,…..)
select (max(id) + 1), …. from table

ERROR: Violation of PRIMARY KEY constraint ‘PK_IDtable1′. Cannot insert duplicate key in object ‘dbo.table1′.
on December 31, 2008 at 10:12 am | Reply Arti

Hi,
I have written following procedure for inser, where I want to pass table name from application program. But its not working. Same format works for create table.

This procedure giving me error as
Error converting data type varchar to datetime.

my procedure is
Create PROCEDURE [dbo].[InsertSalesOrderDetail]
(
@tablename varchar(20),
@SalesOrderID int,
@SalesOrderDetailID int,
@CarrierTrackingNumber nvarchar(25),
@OrderQty smallint,
@ProductID int,
@SpecialOfferID int,
@UnitPrice money,
@UnitPriceDiscount money,
@LineTotal money,
@rowguid nvarchar (50),
@ModifiedDate datetime)
AS
BEGIN

SET NOCOUNT ON;

exec(‘ INSERT INTO [AdventureWorks].[dbo].['+ @tablename +' ]
values( @SalesOrderID,@SalesOrderDetailID,@CarrierTrackingNumber,@OrderQty,@ProductID,
@SpecialOfferID,@UnitPrice,@UnitPriceDiscount,@LineTotal,@rowguid ,@ModifiedDate ) ‘)

END;

pls help
tell me how to pass table name, if we specify the table name in query, then it works.
Thanks
on December 31, 2008 at 10:15 am | Reply Arti

The same procedure gives error :

Must declare the scalar variable “@SalesOrderID”.

I have declared this variable……
pls help
thanks
on January 2, 2009 at 12:53 pm | Reply Devi Dayalan

I want to know how to insert a multiple rows to a table using ‘&’ symbol in SQL query….Please do help me…
on January 7, 2009 at 9:57 pm | Reply Nirvaya

Thanks for sharing valuable tips here. Really it helps lot.
on January 8, 2009 at 5:12 pm | Reply Bitstar

You indeed get a big performance increase, but if you try to use this method where it really counts (like inserting 100k records) it will crash your query compiler and fail to execute. A query simply can’t be that big and complex. Optimizing inserts like this is possible only on mysql.
on January 9, 2009 at 10:30 pm | Reply Amit Oberoi

I have a stored procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_InsertIMEIDetails]
@XMLDOC varchar(8000)
AS

Declare @xml_hndl int

exec sp_xml_preparedocument @xml_hndl OUTPUT, @XMLDOC

BEGIN
INSERT INTO IMEI_IMEIDetails (SerialNumber)
SELECT SerialFrom
FROM OPENXML(@xml_hndl,’//SerialNumbers/SerialFrom’,1)
With (SerialFrom varchar ‘@id’)
END

I want to insert a series that the user will enter from the form the series will start from “000000″ and will end on “999999″ so we are talking about an insertion of 10 Lakh records in one go and the second problem is I want to store the numbers in the same format as they are getting entered.

Hope I am making sense here, can some one help me on this ASAP.

Thanks!
on January 11, 2009 at 9:41 pm | Reply hk

hi,

i have a little doubt related to insert query.

i have a form in which i have 7 text boxes.
2 text box values stores in table customer.
5 text box values stores in table address.

and i have relate these 2 tables with a foreign key i.e, customer_id is added as new column in address table.

Question?
i want to insert the customer_id into table address as when click the submit button of the form.
and i am inserting all the values from a single Stored procedure first inserting table customer values then tables address values.

so plz tell me how can i do this.

thanks in advance
on January 14, 2009 at 6:59 pm | Reply Roger Gordon

Thanks a lot! This was very useful and saved me tons of time.
on January 23, 2009 at 9:23 am | Reply Gary Aure

Hi,

Thanks for this tutorials, it’s cool and really helps me a lot in solving my problem.

Hope to know more from you.

Thanks!
on January 27, 2009 at 11:06 am | Reply Akthari

Hi
I created below store procedure to update the flag of status to 4 from 2.This will invoke when user hit my web site because written in global.cs file under application_beginrequest event in asp.net.Already so many records will got expired and in status 4 but i need the data of last updated records.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
Create PROCEDURE adStatusUpdate AS
UPDATE [addetails] set ad_status = 4
WHERE (ad_status = 2) AND ( ( DATEADD(dd, ad_validity, ad_publishing_date) <= getdate() ))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Actually i want to expire an ad after validty period get over and also notify user about expiry of their ad using mail.

when i update a query it will display 12 records updated instead Can i get the last updated records like select query so that it will be useful to get their mailids from respective tables or can i store the updated records in another table using insert query

insert into tablename
values (UPDATE [addetails] set ad_status = 4
WHERE (ad_status = 2) AND ( ( DATEADD(dd, ad_validity, ad_publishing_date) <= getdate() )) )

or

select * from aduser (UPDATE [addetails] set ad_status = 4 WHERE (ad_status = 2) AND ( ( DATEADD(dd, ad_validity, ad_publishing_date) <= getdate() )) )

select userdetails.user_email,addetails.ad_title,
addetails.auth_code,addetails.ad_status,
addetails.ad_publishing_date
from userdetails inner join addetails
on addetails.auth_code=userdetails.auth_code
and addetails.ad_status = 2 AND ( ( DATEADD(dd, ad_validity, ad_publishing_date) <= getdate() ))

Kindly sort out my problem else provide me alternate solution to match my scenario.

Thanks in advance.
on January 29, 2009 at 4:30 pm | Reply Arvind

Hi all,

I tried to run the following query an obviouly i didn’t execute. I know this is terribly wrong. Please help me out. The select statement is to get values from another table and it will return ultiple values which I have to insert into reasonforchange table.

insert into reasonforchange(reasonforchange_id,reasonforchange) values
(newid(),select distinct displayvalue from oc_picklistvalues pv inner join oc_picklistlanguages pl
on pv.picklistid=pl.picklistid where pl.shortdescription=’Reason for Change’and pv.displayvalue=’Promotion’)
on January 30, 2009 at 9:32 am | Reply Imran Mohammed

@Arvind,

The query is failing because the select statement is returning more than one value.

Test if the query works, using select top 1 ……. something like this,

insert into reasonforchange(reasonforchange_id,reasonforchange) values
(newid(),select top 1 displayvalue from oc_picklistvalues pv inner join oc_picklistlanguages pl
on pv.picklistid=pl.picklistid where pl.shortdescription=’Reason for Change’and pv.displayvalue=’Promotion’)

Work Around : Why don’t you create a default on ReasonForChange column , keep the default as newid(), then insert statement will be,

insert into reasonforchange (reasonforchange) select distinct displayvalue from oc_picklistvalues pv inner join oc_picklistlanguages pl
on pv.picklistid=pl.picklistid where pl.shortdescription=’Reason for Change’and pv.displayvalue=’Promotion’

Hope this helps,

Regards,
IM.
on January 30, 2009 at 11:09 am | Reply Prabhat Singh

how can i set the logging options through the database management interface in sql server 2005
on January 30, 2009 at 11:12 am | Reply Prabhat Singh

Actually i want, Record logging shall be handled within the database, and not within the application. The user
shall set the logging options through the database management interface in SQL Server 2005.
on February 2, 2009 at 4:39 pm | Reply Sarika

Very good concept
on February 5, 2009 at 11:52 am | Reply phani

Hi ,

I want to insert value into particular column,there is any statement like using “where” condition…”insert table table_name values value1 where id=111″ etc.
Please let us know if any statements like..

Thanks and Regards,
Phani..
on February 5, 2009 at 8:12 pm | Reply Maksuda

Hi Pinal:
I am stack in a piculiar situation.
I have a stored proc (SQL 2005) which is compilled and executed successfully. I used cursor in my stored proc.
Stored proc executes on a table and match data with another table dada. So it will either update existing data or if new data come will insert new data into second table.
My updating command is working ok. But inserting commend is not working. I know in my first table I habe 7 new data which are waiting to insetr into second table.
I’m trying to find it out where is the problem but couldn’t.
Would you please help me. I have to delivery my project by the end of the second week but I’m stack here.
Here is my Stored proc:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[AddMonth_Event_TotalInfo]
–@fiscalYear int
as
Declare
C1 CURSOR READ_ONLY
FOR
SELECT CONVERT(varchar(7), IdsData.EarliestEvent, 111) AS [Yr-Mon], TagType.TagTypeID AS EventID, ISNULL(SUM(IdsData.EventCount), 0) AS Total
FROM TagType INNER JOIN
TagDetails ON TagType.TagTypeID = TagDetails.TagTypeID INNER JOIN
IdsData ON TagDetails.[TagName (Unique)] = IdsData.TagName INNER JOIN
SensorCategories ON IdsData.AgentIP = SensorCategories.SensorIP
WHERE (SensorCategories.Monthly = ‘Y’)
GROUP BY CONVERT(varchar(7), IdsData.EarliestEvent, 111), TagType.TagTypeID
ORDER BY [Yr-Mon], EventID

Declare
@MonthID int,
@EventMonth varchar(7),
–@EventName varchar(30),
@EventID int,
@Total int,
@EventTotal int

–delete from Month_Event_TotalInfo

OPEN C1
FETCH NEXT FROM C1
INTO @EventMonth,@EventID,@EventTotal

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @MonthID=isnull(MAX(Month_ID),0)+1 from Month_Event_TotalInfo

select @Total=Event_Total from Month_Event_TotalInfo where Event_Month=@EventMonth
and Event_ID=@EventID
if (@Total>=0)
update Month_Event_TotalInfo
set Event_Total=@EventTotal where Event_Month=@EventMonth and Event_ID=@EventID
else
insert into Month_Event_TotalInfo(Month_Id,Event_Month,Event_ID,Event_Total)
values (@MonthID,@EventMonth,@EventID,@EventTotal)

FETCH NEXT FROM C1
INTO @EventMonth,@EventID,@EventTotal
END
CLOSE C1

DEALLOCATE C1
on February 11, 2009 at 4:53 pm | Reply Rakesh Gupta

what is the max rows can we insert into one table
on February 13, 2009 at 12:14 pm | Reply Hussain

What is SESQL
on February 17, 2009 at 10:43 am | Reply Vijendra Chauhan

HI Pinal,
please help me out…i have a multiple insert query in a file which is about 125 MB (they are around 5 lac inserts). I tried to fire the osql command but it says that it cannot process more than 1000 inserts at a time…i cannot put GO lines after every 1000 inserts in that file…please help how to overcome this issue
on February 17, 2009 at 12:43 pm | Reply Satyanarayana

Hi guys,
I like pinal site very much, but somebody posting waste answers, please don’t do like that. So please post only correct answers.
on February 20, 2009 at 3:39 pm | Reply SQL SERVER - Three Rules to Use UNION Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Union vs. Union All – Which is better for performance? SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL [...]
on February 25, 2009 at 11:09 pm | Reply SQL SERVER - Top Five Articles of Year 2008 Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL Insert multiple records using UNION clause. [...]
on March 2, 2009 at 2:48 pm | Reply sachin

Better way:
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1), (‘Second’,2), (‘Third’,3), (‘Fourth’,4), (‘Fifth’,5);
on March 3, 2009 at 10:51 pm | Reply Dev

Hi,

Every one of u r experts. Pls help me with sp syntax that will insert values into a table Named “Reference”
which has fields
ReferenceID int, ReferenceNo bignt, ReferencedBy varchar(50)

the sp will be called like

exec sp1(1,100)

it needs to insert 100 rows starting from 1 to 100 into the Reference field.

Waiting for your help
on March 21, 2009 at 6:08 pm | Reply marias

Thanq for the above solution which was very useful to me.(About insertion of multiple values at single instance through query).
on March 21, 2009 at 6:25 pm | Reply Wilson Titus

Hi All,
I am happy to get the code for inserting multiple records using one INSERT statement.

Special thanks goes to Pinal Dave.

cheers.
on March 23, 2009 at 5:45 pm | Reply Navin C

Hii,
Nice solution, thanks for sharing.

With Many Regards
on March 30, 2009 at 5:54 pm | Reply sreetheja

hai,
i need insert multiple records table with clear example like table name as customer or employee etc for clarity im in starting stage
thanking you,
sreetheja.
on April 14, 2009 at 5:25 pm | Reply Viren

Hi,

I am inserting multiple records in to a table through .Net application. With each insertion i have to increment the recid field with 1.

I tried,

insert into table2 (recid,…..)
select (max(recid) + 1), …. from table2

this is working for only 1record insertion. If I insert more than 1 record then the recid value is not getting incremented for each record I insert.

So, Can anyone give a solution for this???
on April 25, 2009 at 8:42 pm | Reply Manoj

create table product_master
(
product_no varchar2(6) primary key check(product_no like ‘P%’),
description varchar2(5) not null,
profit_percent number(2,2) not null,
unit_measure varchar2(10) not null,
qty_on_hand number(8) not null,
reorder_lvl number(8) not null,
sell_price number(8,2) not null check(sell_price>0),
cost_price number(8,2) not null check(cost_price>0));

insert into product_master values(‘&product_no’,'&description’,&profit_percent,’&unit_measure’,&qty_on_hand,&reorder_lvl,&sell_price,&cost_price);
ORA-01008: not all variables bound

can u plz explain why this error came and how to solve it
on April 26, 2009 at 4:50 pm | Reply Rajashekar

HI,

I just want to insert a new row in between 2 rows & also 2 news row in one single query

Eg.
Table A has 2 columns(no,name) & values are present in table as
1,’a’
2,’b’
3,’c’
4,’c’
so i want to insert a 2 new rows in one Statement

1. I want to insert a new row (5,’e') in between 2 & 3.
2. I want to insert a 2 new rows (6,’f’ )& (7,’g’ ) at a time into a table at position above 1 & below 4.

also
Can i use where condition in Insert statement

can we do like this……….
on April 30, 2009 at 11:51 am | Reply SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL [...]
on May 2, 2009 at 1:24 pm | Reply Mirei

Hi!

I am new to this and i need to create a table with 1,000,000 entries wherein it starts at 000000001 and so on. It has to be sequential. The second column must be composed of 3 random numbers from 000-999.

Does any of you have an idea on how i can achieve this?

Thanks a lot in advance. :)
on May 2, 2009 at 9:13 pm | Reply SHIBU

i want to insert one table completely to another table
destination table is nk_np_pst
the inserting table is nk_np_unp
so wat command i want to give
on May 6, 2009 at 10:31 am | Reply Shreyas

Hi Shibu,

Assuming that both the tables have got same number of columns, you can write an sql like:

(a table to be inserted in b table)

SQL: Select a.* into b from a

Let me know if your number of columns vary (Table a number of columns is different to table b number of columns)

Hope it helps
Shreyas
on May 12, 2009 at 8:34 am | Reply meetsang

Nice dude…that helped me save a lot of memory issues I was having with multiple inserts!!
on May 12, 2009 at 2:25 pm | Reply rajasekaran

i am extracting 15 columns from many tables which contains many sub queries and i am inserting into a table (using this select statements),but it takes 45 mins…
how to do this…
pls
on May 23, 2009 at 10:44 pm | Reply Keith B.

This works too…

DECLARE @Target TABLE
(
[Id] int,
[Value] varchar(25),
[Size] int
);

DECLARE @Source TABLE
(
[Id] int,
[Value] varchar(25),
[Size] int
);

INSERT INTO @Source ([Id], [Value], [Size]) VALUES (0, ‘Blue’, 1)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (1, ‘Orange’, 3)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (2, ‘Purple’, 2)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (3, ‘Red’, 2)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (4, ‘Yellow’, 1)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (5, ‘Black’, 1)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (6, ‘Green’, 4)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (7, ‘Brown’, 1)

INSERT INTO @Target ([Id], [Value], [Size])
SELECT [Id], [Value], [Size]
FROM @Source
WHERE [Size] = 1

SELECT * FROM @Target
on May 27, 2009 at 5:40 pm | Reply Ratnesh singh

I want to know is dere any method to insert multiple rows
By executing Insert command once and after that only entering values.
Thanks in advance
on May 29, 2009 at 11:26 am | Reply Enna

i want to know on how i can insert data to sql row from a textarea??the text area contains multiple names and i would like to insert it in each row with the details of it…help me..thank you..
on May 29, 2009 at 8:49 pm | Reply Lakshman

Mr. Dave,

This is related to using Distinct clause in a Subquery.

I am converting over 100 attributes from Access Database table to SQL Server 2000, using Asp.Net 1.1 and Vb.Net.

The Access table has duplicates and I am trying to eliminate duplicates. The key is Alien_Number.

The query looks like this:

SELECT *
FROM tblClients T1
WHERE (ALIEN_NUMBER IN
(SELECT DISTINCT RTrim(Alien_Number)
FROM tblClients T2
)
ORDER BY ALIEN_NUMBER

I am still getting duplicate Alien Numbers.

I would appreciate if you have any suggestions.

Thank you for your time.

Regards
Lakshman
on June 5, 2009 at 11:17 am | Reply zaim raza

hi lakshman,

it is very appropriate if you remove the duplicate data from the source and then run the insert into statement.the same problem is resolved using the above said statements.

regards,
Zaim Raza.
on June 5, 2009 at 2:51 pm | Reply santosh jha

Hi sir,
I have Two tables one is temprory table #temp .

and i want to update those records which are in both table and insert those from # temp table which are not in main table
i have tryed but .it only upades
if exists (select PId from payoutdetails where PayoutNo=@PayoutNo and PId in(select SponserId from #p1 ))

update payoutdetails set ClubIncome=@CalAmountForPlan1 where PId in ( select SponserId from #p1 )

else
insert into payoutdetails (PId, SmallClubIncome) select SponserId,@CalAmountForPlan1 from #p1

but it doesnot insert any record

thanks
any help
on June 5, 2009 at 6:07 pm | Reply Brian Tkatch

@Santosh

If you are using 2008, you might want to look at the MERGE statement.

Anyway, no reason to use IF:

update payoutdetails set ClubIncome=@CalAmountForPlan1 where exists(select PId from payoutdetails where PayoutNo=@PayoutNo)
and PId in(select SponserId from #p1)

insert into payoutdetails (PId, SmallClubIncome) select SponserId,@CalAmountForPlan1 from #p1
where NOT exists(select PId from payoutdetails where PayoutNo=@PayoutNo)
and PId NOT in(select SponserId from #p1)
on June 10, 2009 at 5:56 pm | Reply Piyush

Hi,

I have used the above query to insert multiple rows in single statement. But now the question is how do it get @@identity of each row inserted back. the OUTPUT clause is for SQL 2005. I am using SQL 2000.

Please help me.
Thanks!!
on June 11, 2009 at 5:15 pm | Reply Lava Kafle

Perfect example thanks, my SQL INSERT disease got remedied.
on June 12, 2009 at 10:28 am | Reply Deb

Hi,

Can anyone please tell me when we use ‘ (single quotes) in an insert query to add the new values to the table and when we should not use any quotes (like inserting NULL)?

What does quotes do exactly? pls explain..

Thanks!
on June 12, 2009 at 5:10 pm | Reply Brian Tkatch

@Deb

single-quotes are used for literals. If there are to single-quotes, it want to insert a zero-length string. NULL means there is no value there at all.
on June 20, 2009 at 5:37 pm | Reply Gilsygirl

Hi Pinal Dave

I am using access database but i am try to search a table where i should pick the firs t record and insert it into another table and be able to update the record with the next 4 records in the first table.
I can insert but my update is giving an error. this a a bit of my code. Pls help i am using a for loop to get the next 4 records, but i also though of rowcount but do not know how to use it .

gcon.OpenConnection()
cmd.CommandText = “select * from english where centcode like ‘” & centre & “%’ and subjcode like ‘” & Subjcode & “%’”
cmd.Connection = gcon.con

dr = cmd.ExecuteReader

Dim Subjcode1 As String

While dr.Read()

i += 1

For i = 1 To CandCount
CentCode = “”

If i = 1 Then

CentCode = (dr(“centcode”))
CandNo = (dr(“candno”))
Subjcode1 = (dr(“subjcode”))
‘Abs = (dr(“abs”))
Yr2 = (dr(“y2mrk”))
Yr3 = (dr(“y3mrk”))
‘mQuery = “Insert into casstbl(centcode,subjcode,candno1,y2mrk1,y3mrk1,candno2,y2mrk2,y3mrk2,candno3,y2mrk3,y3mrk3,candno4,y2mrk4,y3mrk4,candno5,y2mrk5,y3mrk5)” _
‘ & ” values” _
‘ & “(‘” & CentCode & “‘,’” & Subjcode1 & “‘,’” & CandNo & “‘,’” & Yr2 & “‘,’” & Yr3 & “‘,’” & Subjcode1 & “‘,’” & Yr2 & “‘,’” & Yr3 & “‘,’” & Subjcode1 & “‘,’” & Yr2 & “‘,’” & Yr3 & “‘,’” & Subjcode1 & “‘,’” & Yr2 & “‘,’” & Yr3 & “‘,’” & Subjcode1 & “‘,’” & Yr2 & “‘,’” & Yr3 & “‘)”

mQuery = “Insert into casstbl(centcode,subjcode,candno1,y2mrk1,y3mrk1)” _
& ” values” _
& “(‘” & CentCode & “‘,’” & Subjcode1 & “‘,’” & CandNo & “‘,’” & Yr2 & “‘,’” & Yr3 & “‘)”
cmd = New OleDb.OleDbCommand(mQuery, gcon.con)

cmd.ExecuteNonQuery()
End If

If i = 2 Then

CentCode = (dr(“centcode”))
CandNo = (dr(“candno”))
‘Subjcode1 = (dr(“subjcode”))
‘Abs = (dr(“abs”))
Yr2 = (dr(“y2mrk”))
Yr3 = (dr(“y3mrk”))
mQuery = “Update casstbl set=(candno2,y2mrk2,y3mrk2)where centcode like ‘” & centre & “‘” _
& ” values” _
& “(‘” & CandNo & “‘,’” & Yr2 & “‘,’” & Yr3 & “‘ )”

cmd = New OleDb.OleDbCommand(mQuery, gcon.con)
cmd.ExecuteNonQuery()

End If

gilsygirl
on June 22, 2009 at 3:14 pm | Reply raj

Hi Pinal,

First of all, Excellent work!!!

My query is related to which is based for performance:
i am tryign to insert NEW rows into a table- ard 500 rows per sec
AA) using a dataset appproach- call a ds.update/insert
or
BB)directly giving values as abv proc of insert using union all.
or
CC) passing an array to a stored proc with values i wish to insert.

thnx!
on June 23, 2009 at 5:38 pm | Reply subbu

Hi sir,

i am facing one proble plz give me solution

I am using mysql database sir,

i have one table that table one column is there delevey_email in that column i am insert one row like subbu@gmail.com,madhu@gmail.com
but these mail ids are insert two rows another table
this is my problem give me solution sir
on June 26, 2009 at 12:41 pm | Reply Dhurendra

This is very nice solution for inserting more than on row in a table …………………….
on July 9, 2009 at 1:34 am | Reply eliezer

this is quite amusing! congrats!
i’m wondering if you can use a counter into the sentence cause i have a field that’s not auto_increment and i need it to be inserted in a fashion autoincremental.

I know I can just edit the column and put auto_increment on the atributes but I could use this trick also to insert products names in varchar unique fields. (product1,product2…product9999)…

hope I was clear on my question!!!

Thanks a lot, amazing contribution!
on July 9, 2009 at 1:38 am | Reply eliezer

Uhm, if I’m just about to get it right, if the query you provided would be equal to $sql, you’re telling me to copy and paste that as many times i want it to be executed?

That’s not cool. I don’t know if there’s another way, maybe writting even a little iterator in any language would be better in terms of redundance.
on July 9, 2009 at 1:42 am | Reply eliezer

How about…

INSERT INTO ”table_dance”
(”id”, “name”, “brasize”)
VALUES
(”0”, “striperella”, “36dd”),
(”1”, “demi moore”, “32d”), …
on July 11, 2009 at 4:18 pm | Reply Psam

hi all ,

i was lacking in syntax , n i got that from here, thank u so much for all
on July 20, 2009 at 4:56 pm | Reply Farooq Abdulla

Hi All

I’m programmer from and about to insert 10Lac Records at a time depending on some condition but 2,000 to 4,000 records are getting skipped even though the condition is getting satisfied what could be the reason.
on July 21, 2009 at 1:48 pm | Reply SACHIN

I stored 200 values in an array and that 200 values represent one record.

It is easy to insert ‘n’ record, that i did N no. of times with MAX of 20 values but this is first time im supposed to insert this large no. of values.

So donno how to do…

HELP!!!
on July 23, 2009 at 11:55 am | Reply Majo George

how to get the date between two date through a query.
For Example: i need the date between 1/3/2009 and 30/3/2009. Please mail the answer if anybody knows to
on July 23, 2009 at 1:33 pm | Reply Lubos

hi,

i have table with columns uniqueid, datetime, float, smallint. In my test code i’m creating collection of dummy objects (with related properties) and then i’m inserting them to my table.
1. one by one by calling stored procedure
2. insert with multiple select
3. multi insert

results are following :
for 1200 records are time like 0.9s, 5.46s, 0.6s
for 2400 recs : 1.65s, 13.0s, 1.12s
…
for 4800 recs 2nd fails saying “insufficient memory”…

why is it so slow???
on July 23, 2009 at 2:05 pm | Reply Tejas Shah

Hi Majo George,

You can get dates between periods by these two ways:

1.

select CAST(‘2009-03-01′ AS DATETIME )+ a.Number
from master..spt_values a
WHERE Type = ‘p’
AND number<30

2.

;with cte as(
select CAST('2009-03-01' AS DATETIME) AS Dt
UNION ALL
select Dt + 1
from cte
WHERE dt <= '2009-03-30'
)
select *
from cte

Let me know if it helps you.

Thanks,

Tejas Shah
on July 24, 2009 at 10:00 pm | Reply tcowart

I need to insert a journal entry transaction from GP GL20000 open table into GP GL30000 history table. The columns are the same. It is a multiple line journal entry. Once I get the Journal Entry inserted into the historical table I can easily delete the multiple line record in the open table.

Thank you for your assistance.
on August 7, 2009 at 4:05 pm | Reply Bozy

I need a insert to enter 368 values into a table:
a column CRT must have a incremental value starting with 1. an example : i need 1,2,3 etc…or something 36801, 36802 etc…can you help me with this?
thanks in advance!
on August 7, 2009 at 11:07 pm | Reply eliezer

If you do something like

insert into table (autoincrement, name) values (”, ‘eliezer cazares’);

your autoincrement field will be filled by itself.

About inserting 368 rows, either way you come up with an iteration in some programming language or you copy and paste 368 times the query just like the “MySQL (so-called)-Authority” says.
on August 20, 2009 at 11:00 am | Reply Riaz

Can i insert records into cursor from multiple cursors at a time?
on August 20, 2009 at 7:49 pm | Reply Kenneth

I have a form with 2 input text boxes where i want to enter a number value in each. One input box in the start number (example: 5) and the second is the end number (example: 26). I want to be able to print each number from beginning to end incrementing by 1 example 5 6 7 8 until 26, each number on each page of paper, in total 22 pages.
Another example start number 48 end number 52; 48 49 50 51 52 total of 5 pages.
Can anyone help me or give me a tip.
Thanks in advance
on August 21, 2009 at 3:52 pm | Reply Jitendra

Hello Sir,

I want to Import or can say convert a MS-Exess
File into the SQL Sever 2005 EXPRESS.

so plz tell me how it is possible?

And can i need to put the same fields in both?

Thanks
on August 21, 2009 at 3:55 pm | Reply Jitendra

Hello Sir,

I want to Import or can say convert a MS-Exess
File into the SQL Sever 2005 EXPRESS.

so plz tell me how it is possible?

And can i need to put the same fields in both?

And i want to know the automatic Primary key allotment process.

Thanks
on August 26, 2009 at 10:32 am | Reply Karthik

Hi

I have a Insert Trigger on Table A but when i inserts multiple rows in a single insert statement the trigger fires only once.

Is there any solutions to this, plz help me to solve this problem.

I want to invoke the trigger for every row insert from the single insert statement.

Thx

Karthik
on September 3, 2009 at 5:30 pm | Reply pradeep

Hi!
i m not getting how to insert records into ms-access in ascending order using java.i hav tried order by clause.it takes data in resultset in ascending order but does not insert that data in ascending manner in access.

please give me solution.
on September 11, 2009 at 9:44 am | Reply Neelam

Hi Pinal,

Could you please answer my query?

I have a python script where in I have an insert statement in a loop. That means the insert statement executes same number of times as the loop executes.

To improve the performance I used your way of “UNION ALL” to insert 1000 rows at a time.

But when I execute this new script, for inserting 2000 rows, the performance has become lower than it was in the original script.

I insert 34 columns per row.
Please help.
Thanks in Advance.
on September 11, 2009 at 9:08 pm | Reply Brian Tkatch

@Neelam, try less records. Like 100. I’m just guessing here though.
on September 11, 2009 at 9:09 pm | Reply Brian Tkatch

@pradeep.

To ensure order, INSERT them separately.
on September 11, 2009 at 9:12 pm | Reply Brian Tkatch

@Karthik

The documentations says: An AFTER trigger is executed only after the triggering SQL statement has executed successfully. This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted.

So, it will only execute once. If you want it for every record, perhaps you can identify which records were INSERTed.
on September 14, 2009 at 2:32 pm | Reply Mikail

Hi Pinal,

I have error with code “ORA-00936: missing expression”. What am I missed? Thanks.

MERGE into cm_adm_usergrants c
using (select id, name from smart.user$ where id=223) o
on (c.userid=o.id)
when not matched then
insert (userid, pluginid) values
(select userid, pluginid from cm_adm_usergrants where userid=117)
when matched then
update set c.pluginid=(SELECT pluginid
FROM cm_adm_usergrants where userid=117);
on September 14, 2009 at 7:02 pm | Reply Brian Tkatch

@Mikail

ORA is an Oracle error, not SQL Server.
on September 15, 2009 at 3:23 pm | Reply sravan

hi this is sravan.
i want insert rows from one table to another with out duplicating the rows.
then how can i write a sql command? plz tell me

thnx
sravan
on September 15, 2009 at 5:06 pm | Reply Tony Baker

I’ve had some really interesting findings, with adding multiple rows at a time.
I’ve found that when adding around 30,000 rows of data (three columns), 20 rows at a time is optimal! 100 rows at a time is a bit slower and the more you add, the slower it goes!
I can’t explain WHY, just from my observations.

BTW, I’m also in the position of wanting to add data from a file where some of the rows need to be inserted and some updated (i.e. an overlap).

I’m creating a temporary table (based on the old table), inserting the data from the file in to the new table (using the UNION ALL technique), wiping data from the table I want to update (where there is data in the temporary table, one SQL command to do that) and inserting the data from the temporary table to the table I want to update (again, one SQL command to do that).

I really wish SQLServer had MySql’s ON DUPLICATE KEY feature!
on September 16, 2009 at 2:24 am | Reply Brian Tkatch

@sravan

If you want to restrict what goes in, use DISTINCT.
If you want to restrict if it is already there, use WHERE NOT EXISTS()
on September 16, 2009 at 2:52 am | Reply Juan Gonzalez

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
on September 16, 2009 at 12:50 pm | Reply Kumara Datta

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
on September 16, 2009 at 6:33 pm | Reply Brian Tkatch

@Kumara Datta

Are all those results from the two sample records?
on September 17, 2009 at 6:26 pm | Reply Dipti

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.
on September 17, 2009 at 9:32 pm | Reply Brian Tkatch

@dipti

INSERT INTO table1(col1, col2, col3, col4)
SELECT col1, col2, col3, @global_var FROM table2;
on September 18, 2009 at 10:26 am | Reply Dipti

@ brian tkatch

i tried it. it works. Thanx!!
on September 20, 2009 at 1:43 am | Reply Jay

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!
on September 30, 2009 at 3:46 pm | Reply Ankit

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…
on October 1, 2009 at 3:20 pm | Reply satish

sirr,

how to insert more data(for ex 10 mb) in sql serverr
on October 6, 2009 at 5:49 pm | Reply karthi

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’)
on October 13, 2009 at 2:26 am | Reply Brian Tkatch

@Ankit

I don’t understand the question. What exactly is the problem?
on October 13, 2009 at 2:27 am | Reply Brian Tkatch

@karthi

Which part is dynamic? Is this a T-SQL script?
on October 20, 2009 at 12:10 pm | Reply okello

how can append/insert records in excel format, at once, to Ms access database table using the insert sql query
on October 21, 2009 at 2:23 pm | Reply VIJAY

I AM TRYING TO INSERT MULITPLE ROW IN ONE STATEMENT ,BUT ITS DOES NOT WORK

I WROTE

insert into DEPT(DNO) VALUES (1), (2) , (3);

AND ERROR SHOW:-

Msg 102, Level 15, State 1, Line 80
Incorrect syntax near ‘,’.

PLEASE TELL ME WHAT CAN I DO ,SYNTEX IS RIGHT BT DOES NOT WORK
on October 21, 2009 at 7:29 pm | Reply Brian Tkatch

@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;
on October 23, 2009 at 4:37 pm | Reply Damodharan V.M

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’)
on October 23, 2009 at 6:57 pm | Reply Brian Tkatch

@Damodharan

Pinal has an article on three emthods here: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

The OUTPUT clause is mentioned here: http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/

Personally, i use the OUTPUT clause.
on November 11, 2009 at 10:14 am | Reply Inayat

How can i load a text file in sqlserver 2005 by using .net
on November 11, 2009 at 10:15 am | Reply Inayat

How can i insertthe values of a text file in sqlserver 2005 by using .net
on November 13, 2009 at 11:31 am | Reply Vanishree

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
on November 17, 2009 at 4:28 pm | Reply kavya

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
on November 18, 2009 at 9:09 am | Reply Imran Mohammed

@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:
http://www.sql-server-performance.com/articles/dba/isolation_levels_2005_p1.aspx

~ IM.
on November 18, 2009 at 9:15 pm | Reply Mark_#386

Very good performance increase. THANK YOU, Pinal Dave!
on November 19, 2009 at 6:25 am | Reply Padma

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!