Dynamic SQL and Cursors - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic SQL and Cursors

Author

Topic

majolamp
Starting Member

15 Posts

Posted - 2008-03-07 : 09:21:27

Hi All

I wrote the following script. Its gets list of tables left joint to compare data between 2 table and insert the difference.
My problem is its taking for ever to Run.

What could be the problem.

CREATE PROCEDURE [dbo].[Update_D_Tables_InBlake_DBN12]

AS
BEGIN
DECLARE @TableName VARCHAR(255)
DECLARE @Sql VARCHAR(MAX)
DECLARE @Column VARCHAR(1000)
DECLARE @ID VARCHAR(255)

DECLARE TableCursor CURSOR FOR
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name in ('D_Address',
'D_AddressBankaccount',
'D_AddressCustomer',
'D_AddressEmployer',
'D_Application',
'D_ApplicationCustomerPri',
'D_ApplicationCustomerSec',
'D_ApplicationDetail',
'D_ApplicationDetailStatusHistory',
'D_ApplicationDetailUpdateHistory',
'D_ApplicationHistory',
'D_ApplicationQuestions',
'D_ApplicationSendReports',
'D_ApplicationStatusHistory',
'D_BankAccount',
'D_BlackHorseBranch',
'D_BlackHorseResponses',
'D_BrokerReferralInfo',
'D_BrokerWorkflow',
'D_Calls',
'D_CallTable',
'D_CCJs',
'D_CitiResponses',
'D_Contact',
'D_ContactCustomer',
'D_ContactEmployer',
'D_Customer',
'D_DataExtracts',
'D_DNCList',
'D_DuplicateApps',
'D_DuplicateMatches',
'D_EDLResponses',
'D_Employer',
'D_ExistingCredit',
'D_FinancialCustomer',
'D_FinancialStatements',
'D_HomeownerDetails',
'D_IncompleteResults',
'D_IncompleteRules',
'D_LenderOtherDetail',
'D_LenderOwnerRoles',
'D_LenderPlans',
'D_LenderResponse',
'D_LenderSender_Report',
'D_LenderStatusRole',
'D_LenderStatusRoleAssign',
'D_LoanDetails',
'D_Mortgage',
'D_Occupations',
'D_OccupationsCustomer',
'D_PhoneLogin',
'D_PhoneLogin_Skills',
'D_ProvidentResponses',
'D_ReportAllianceLeicHistory',
'D_SkillSet',
'D_Tenancy',
'D_Users',
'D_WelcomeResponses'
)

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @Line VARCHAR(1000)

SET @Line = ''
SET @ID = ''

SELECT top 1 @ID = @ID + QUOTENAME(c.Column_Name)
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON t.Table_Name = c.Table_Name
WHERE t.Table_Name = @TableName

SELECT @Line = @Line + 'b.'+ QUOTENAME(c.Column_Name) + ' , '
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON t.Table_Name = c.Table_Name
WHERE t.Table_Name = @TableName

SELECT @Column = SUBSTRING(@Line, 1, LEN(@Line) -1)
SELECT @Sql = SUBSTRING(@Line, 1, LEN(@Line) -1)
SELECT @Sql = 'INSERT'+ ' '
SELECT @Sql = @Sql + 'INTO Staging.dbo.'+ @TableName +' ' + 'SELECT' + ' ' + @Column
SELECT @Sql = @Sql + ' '+ 'FROM MyAPOLLO_101.dbo.'
SELECT @Sql = @Sql + @TableName + ' ' +'B' + ' '
SELECT @Sql = @Sql + 'LEFT JOIN' + ' '
SELECT @Sql = @Sql + 'Staging.dbo.'
SELECT @Sql = @Sql + @TableName + ' ' +'C'+ ' '
SELECT @Sql = @Sql + 'ON' + ' '
SELECT @Sql = @Sql + 'B.'+ @ID + ' '+ '=' + ' ' + 'C.'+ @ID + ' '
SELECT @Sql = @Sql + 'WHERE ' + ' ' + 'C.'+ @ID + ' ' + 'IS NULL' --Edited 04/03

EXEC (@Sql)
PRINT @TableName
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor
END

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 09:39:28

Maybe we can help you if explain why you are doing this with dynamic SQL AND a cursor?

I am sure there is a way to approach this using a more SET based method...

Poor planning on your part does not constitute an emergency on my part.

majolamp
Starting Member

15 Posts

Posted - 2008-03-07 : 10:18:49

quote:
Originally posted by dataguru1971

Maybe we can help you if explain why you are doing this with dynamic SQL AND a cursor?

I am sure there is a way to approach this using a more SET based method...

Poor planning on your part does not constitute an emergency on my part.

I'm trying to move data from one server sql 2005 live to a sql server 200 wich will be used for reporting.

Yes I should impliment replication but my live server has limited resources. I did try SSIS but I cann't loop through 200 tables. I only manage to do it with one table.

Yep that were I'm at the moment ended up writting the above script.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-07 : 10:32:56

I am not sure you can move the 2005 data to a 2000 server....not sure if forward compatibility exists like that.

For sure there is a better way to tackle this without doing dynamic SQL AND Cursors....you surely don't need to loop through every table every table..

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources