Inserting Records 5000 At A Time

[mark1110]

Hi,

I have 2 tables, Address and Temp_Address. I need to insert all records in Address into Temp_Address. However, I don't know how many records I have in the Address table and I need to insert them 5000 records at a time then do a commit. Has anyone done this or is it not doable?


Thanks,

Mark

 

[fredericofonseca]

what database are you using?

if SQL Server you can use a DTS package, data pump task, and specify what is the insert batch size

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[mark1110]

I am using Sybase

 

[ManivannanD]

Hey mark1110,

You can do it with out using DTS.
If the both tables are in same tables,
Insert Into Address Select * From Temp_Address;

If the both tables are in same server & different Database,

Insert Into [DataBaseName]..Address Select * From [DataBaseName]..Temp_Address;

If the tables are in different Server; then use DTS or Linked Server

 

[ManivannanD]

Hey Mark I froget to add few Steps in my posting,

Wrap those Insert Statements with Begin Transaction; & Commit/Rollback Transaction;

 

[mark1110]

Hi,

How would I insert 5000 records then commit, insert the next 500 records, commit etc.

Mark

 

[fredericofonseca]

Manivan,

Mark is not using SQL Server, so DTS is out of question.
And the main point is to insert in BATCHES of 5k records.


2 options here. (and you should probably ask on the correct forum, not here).

1- Use BCP to copy the original table to a flat file, then BCP again to insert into the other table with your specified batch size. This may be the fastest and easiest way to do.

2- If you table has a key you can use to select groups of records to copy, then you can also do it within a loop.

That field should have information that allows you for example to say

where field between 1000 and 6000
or
where field between AA and AZ.

This last option may not give you a fixed number of records, but will still work in some kind of batch.

I do prefer option 1 as its really easy to do.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com