DynDeveloper.com

DynDeveloper.com

Sign Up Now!Log In
finlyte 8/6/2019 3:38:32 PM

Need some SQL help with a CURSOR

Hello,

I'm working on a data migration from GP to NetSuite.  I have 16 companies to migrate data from and I created a table in each company db with an account mapping master table called "dbo.GL00105ToNetSuite".  I'm trying to build a cursor to backup these tables into a separate database called Finlyte, because I have to restore over the original databases to pull a second set of data for migration.  So here is what I have:

DECLARE @db VARCHAR(50) --- Database Interid
DECLARE @S_TABLE VARCHAR(50) --- Source table
DECLARE @D_TABLE VARCHAR (50) ---- Backup table

DECLARE db_curser CURSOR FOR 
SELECT INTERID FROM DYNAMICS.dbo.SY01500 
WHERE DYNAMICS.dbo.SY01500.CMPANYID BETWEEN 1 AND 17


OPEN db_curser
FETCH NEXT FROM db_curser into @db

WHILE @@FETCH_STATUS = 0

BEGIN
SET @S_TABLE = @db + '.dbo.GL00105ToNetSuite' -- appends the source company database onto the table name
SET @D_TABLE = 'FINLYTE.dbo.' + @db + '_GL00105TONetSuite' -- adds the source company database to the table name in the Finlyte database.
EXEC('SELECT * INTO ' + @D_TABLE + ' FROM ' + @S_TABLE)  --executes the Select * into backup_db_table from source_db_table
FETCH NEXT FROM db_cursor INTO @db



END

CLOSE db_cursor
DEALLOCATE db_cursor

When I run it, it backs up the first database table correctly and then it crashes.  Below are the error messages that I get.  Help would be greatly appreciated.

Msg 16915, Level 16, State 1, Line 12
A cursor with the name 'db_curser' already exists.
Msg 16905, Level 16, State 1, Line 16
The cursor is already open.

(15127 rows affected)
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'db_cursor' does not exist.
Msg 16916, Level 16, State 1, Line 31
A cursor with the name 'db_cursor' does not exist.
Msg 16916, Level 16, State 1, Line 32
A cursor with the name 'db_cursor' does not exist.

 

 

 

Version: GP 2015, GP 2013, GP 2010, GP 10, GP 9, GP 8
Section: SQL Scripts