fetching recodrs from cursors batchwise

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

fetching recodrs from cursors batchwise

Author

Topic

isha
Starting Member

2 Posts

Posted - 2008-12-31 : 04:19:22

Hi..
using C# bulkinsert imported the data in a dump_table, and from dump_table using cursors we are importing the data to the required tables.
some times the data may be very big like above 5lakh records, so to import it is taking more than 7 to 8 hrs.
i want to import from the dump_table to the required tables batch wise.
please can anbody help me out in this regard.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 04:20:46

why are using cursor if you want to do it batch wise? cant you go for set based approachj. just use series of INSERT...SELECT statements to populate each of your tables.

isha
Starting Member

2 Posts

Posted - 2008-12-31 : 06:25:34

Thank u Visakh for u r immediate reply.
But I am using the stored procedure to iomport the data into different tables. but to fetch the records from dump_table i used cursor.below is the code snippet to understand the same.

DECLARE Offdata CURSOR FOR
SELECT F22,F15,F2,F4,F8,F5,F6 FROM Dump_Table
OPEN Offdata

FETCH NEXT FROM Offdata
INTO @prod_code,@prod_name,@cust_code ,@cust_name,@cost ,@qty,@weight
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
set @cust_id=(Select top (1) cust_id from customerwhere [name]=@cust_name)
if(@manufacturer_id is null)
Begin
INSERT INTO [customer] ([code],[name],[country_id],[deleted]) VALUES(@cust_code,@cust_name,'-2','false')
Set @cust_id = @@Identity
COMMIT TRANSACTION
END TRY
--------------------------------------------------------------------------------------------
now my concern is if the dump_table is having huge records say 5lakhs it is taking lot of time to complete the execution, so i want to send the batch of records till the last record.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 06:37:47

i still cant understand need of cursor here. where does @manufacturer_id come from?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-31 : 07:06:23

Replace all of your posted code with this

INSERT		customer
		(
			code,
			name,
			country_id,
			deleted
		)
SELECT DISTINCT	dt.f2,
		dt.f4,
		'-2',
		'false'
FROM		Dump_Table AS dt
LEFT JOIN	customer AS c ON c.name = dt.f4
WHERE		c.name IS NULL

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources