April 20, 2009 at 4:33 pm
I know that cursors is not a very good option but I haven't come with a better solution.... Not to say that I can't use SSIS because of company policies....
I have to transfer data from 13 tables. These are the tables:
select * from dbo.Fotografos -- 5 records
Select * from dbo.Vinos -- 12 records
Select * from dbo.Criterios -- 6 records
select * from dbo.Autores --44 records
select * from dbo.Notas -- 290 records
select * from dbo.TipoPlatillos -- 186 records
select * from dbo.Criterios_Notas -- 1111 records
Select * from dbo.Menus -- 414 records
select * from dbo.Recetas -- 3853 records
select * from dbo.Recetas_TipoPlatillos -- 9347 records
select * from dbo.Recetas_Notas --986 records
select * from dbo.Recetas_Menu -- 1238 records
select * from dbo.Pasos -- 4853 records
I'm using different cursors to retrieve the data of each table. For each row of the resultset I'm using an sp to insert that information in the target table.
The process takes about 9 minutes, that's why I have to think of a different way...
I'd appreciate your help..
Here's an example of the code I'm using....
DECLARE Cursor_Menus CURSOR LOCAL FAST_FORWARD FOR
Select * from dbo.Menus where fecha <= @dtmFecha
OPEN Cursor_Menus
FETCH Cursor_Menus INTO @idMenu, @strTituloM, @strDescripcionM, @strFoto1M, @strFoto2M, @idAutor, @dtmFechaM, @idFotografo
WHILE @@fetch_status = 0
BEGIN
EXEC link_server.database..Tran_Add_Menu @idMenu, @strTituloM, @strDescripcionM, @strFoto1M, @strFoto2M, @idAutor, @dtmFechaM, @idFotografo
FETCH Cursor_Menus INTO @idMenu, @strTituloM, @strDescripcionM, @strFoto1M, @strFoto2M, @idAutor, @dtmFechaM, @idFotografo
END
CLOSE Cursor_Menus
DEALLOCATE Cursor_Menus
April 20, 2009 at 4:52 pm
[font="Verdana"]Do you need to call the stored procedure to insert the data, or can you insert directly into underlying tables?[/font]
April 20, 2009 at 6:33 pm
I have to call the sp....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply