Commit Question

[Jimreaper]

Hello all,
What is the proper SQL to use for this senario:
Insert all records into table1 from table2 and commit after every 10000 rows until complete.

Thanks for helping a beginner.
Jim

 

[qyllr]

hello,

i think it would be faster if you bcp out the data from tableA and bcp in the data to tableB with -b option for the batch size...

hth,
q.

 

[qyllr]

hello,

here is the sql batch if you decide to go that route...

---
use databaseName
go

select * from table2
into tempTableName
go

set rowcount 10000
go

while exists(select * from tempTableName)
begin
insert table1 select * from tempTableName
delete tempTableName
end
go

drop table tempTableName
go
---

watch out you may run out of tempdb space depending on the size of table2 and tempdb size...

here is the bcp syntax that you can run...

---bcp Out---
$SYBASE/bcp databaseName..table2 out $result_dir/table2.dat -UuserName -Ppassword -SserverName -c -t "|" -r "\n"

---bcp In---
$SYBASE/bcp databaseName..table1 in $result_dir/table2.dat -UuserName -Ppassword -SserverName -b10000 -c -t "|" -r "\n"

hth,
q.

 

[mikewoodhouse]

The solution proposed looks absolutely fine - unless your initial problem was tempdb space, in which case you're just as likely to get the problem when the temporary table is loaded.

Is there any value that you can use to divide the source table into (approximately) equal chunks? For example, I had to grab 1.4M rows from one table to another to build a test set. Fortunately there's an ID number, so I was able to grab the rows by their trailing digit (i.e. mod 10) using
"Where ID - 10 * Floor(ID / 10) = 0".

If that's no good, what about dates? Or a character column? Anything that you can use to create useful subdivisions.

HTH,

Mike