chain together calls to the stored proc in a single string:
exec store_proc @arg1=1, @arg2=2
exec store_proc @arg1=1, @arg2=3
exec store_proc @arg1=1, @arg2=4
exec store_proc @arg1=1, @arg2=5
exec store_proc @arg1=2, @arg2=1
exec store_proc @arg1=2, @arg2=2
etc....
Write this as a single block of SQL and call it in one go. Not this can do as many calls as the length string your db can handle - you may need to write some looping VB code to create as many batches as necessary.
e.g.
dim i as long
rs.movefirst -- you could be iterating over an array or collection here - up to you.
do while not rs.eof
sql = sql & "exec stored_proc @arg1=" & rs("one") & ",@arg2=" & rs("two") & vbCrLf
if i >= MAX_BATCH_SIZE then
RunBatchAgainstDB sql
i = 1
else
i=i+1
end if
rs.movenext
loop
This isn't entirely transaction safe however. If you want to preserve transactions I'd create a staging table in your database, use the above method to populate it (and catch errors during the stored proc calls to clear it). When everything is correctly uploaded call another stored proc to transfer the changes into the main table in a single transaction (and clear down the staging table again).
You haven't mentioned you database vendor but if you are using SQL server of Sybase you can isolate the batches of changes by either using a userid column in your staging table (ok, but not ideal) or an int for @@spid which is the process ID of the current connection - this would allow concurrent updates by multiple users, although I'd also suggest having a batch process to clear these up overnight because if a connection fails the spid will be stale. I'd also suggest 'delete from staging_table where spid = @@spid' before you run you uploads to avoid duplicates etc.
Main Topics
Browse All Topics
by: imrancsPosted on 2007-02-12 at 05:24:45ID: 18514590
Hi
try following script
BULK INSERT [Database Name].[Owner Name].[Table Name]
FROM 'c:\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
thanks,
ahmad