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://www.SQLAuthority.com), SQL SERVER - Union vs. Union All - Which is better for performance?

Posted in Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 263 Comments

263 Responses to “SQL SERVER - Insert Multiple Records Using One Insert Statement - Use of UNION ALL”

on June 20, 2007 at 1:52 am 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 MrSnipey

In response to question 9:
Try ‘Bulk Insert’ to load data from a file.
on August 1, 2007 at 12:57 am 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 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 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 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 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 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 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 suvarna

Hello ,

Really this is very useful site for me too…
on August 5, 2007 at 11:11 pm 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 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 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 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 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 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 pinaldave

Hi,
Did you change yourDB to “your databasename” example like adventureworks?
Regards,
Pinal
on August 15, 2007 at 8:11 am 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 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 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 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 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 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 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 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 Mam

Hi Dhayanethi,

I think u r missing “GO” in the script.
on August 31, 2007 at 4:12 am 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 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 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 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 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 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 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 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 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 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 pinaldave

Hi Sathish,

It is not possible.

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

Awesome !

What an increase in performance.Thank you pinaldave.Thank you Very Much.
on September 24, 2007 at 2:41 am 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 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 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 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 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 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 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 Shreyas

Chiranjeevi, did you try giving semicolon (;) at the end?
on October 23, 2007 at 10:36 pm 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 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 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 Sawan

Is Union All works with Oracle
on November 3, 2007 at 9:03 am 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 Hitesh Agja

I got the solution…faster then ever you have seen….bye guys….
on December 12, 2007 at 12:16 pm 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 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 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 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 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 Dipak

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

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

how to use massage command in sp
on January 7, 2008 at 9:29 am 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 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 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 Ryan F

Thank alot! This really helped…
on January 11, 2008 at 6:58 am 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 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 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 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 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 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 srikanth

Can we apply the same for multiple update statements
on January 26, 2008 at 10:51 pm 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 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 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 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 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 Manoj K Joshi

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

gud stuff
on February 5, 2008 at 8:27 pm 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 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 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 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 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 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 Murugesh

Hi Pinal,
gone thru ur blog…ur blog z highly useful… thx a lot man…
on February 12, 2008 at 8:15 pm 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 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 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 Piyush

Very helpfull and performance improving article.
Thanks
on February 15, 2008 at 4:00 pm 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 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 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 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 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 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 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 Agnel J

Very useful Query for Developers and DBA’s.
on March 7, 2008 at 8:06 pm 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 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 adhi

Dear all,

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

Regards
Adhi
on March 17, 2008 at 7:24 pm 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 Ragu

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

Your blog is very usefull!
Can u explain the difference between Union and Union All?
on May 8, 2008 at 5:11 pm 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 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 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

Journey to SQL Authority with Pinal Dave

Notes of a SQL Server MVP and Database Administrator

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

263 Responses to “SQL SERVER - Insert Multiple Records Using One Insert Statement - Use of UNION ALL”