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.