January 27, 2009 at 10:26 am
I'm getting my feet wet with T-SQL and would like a little feedback. I did NOT write this, but, wonder if it's written correctly and efficiently.
On a local SQL2K there is a Job that runs nightly. It executes a Stored Procedure. The SP that is excuted is on the local SQL Server and it gathers information from another "LINKED" SQL Server (MyLinkedSQLServer) to populate data back into the local SQL Server.
Here is that Stored Procedure:
CREATE PROCEDURE [dbo].[sp_update_data] AS
DECLARE @ID VarChar(50)
DECLARE @SFDC_Opp_ID as varChar(255)
DECLARE @StageName as varChar(255)
Delete from My_Sales_Table
INSERT INTO My_Sales_Table (Opp_ID, Opp_Name, Close_Date, Account_ID, Account_Name, Stage_Name, Lead_Source, IIS_Notes, Description, Line_Of_Business, IIS_Sales_Reigion, DM_Name, Owner, Created_By, Created_Date, OrigLeadID, LastModified, LastModifiedBy, TargetProgram, Amount, Ex_Date, DM_Code)
SELECT ID, Name, CloseDate, AccountID, Account, StageName, LeadSource, IIS_Notes__c, Description, INS_Line_Of_Business__c, IIS_Sales_region__c, IMA_DM_Name__c, Owner, CreatedBy, CreatedDate, Original_Lead_ID__c, LastModifiedDate, LastModified, Ins_Target_Program__C, Amount, IMA_Expiration_Date__c, IMA_DM_Code__c
FROM MyLinkedSQLServer.Sales_data.dbo.ins_My_Sales_Table
UPDATE dbo.My_Sales_Table
SET dbo.My_Sales_Table.FiscalWeek = dbo.view_Sales_Get_Fiscal_Week.Calender_Fiscal_Week, dbo.My_Sales_Table.FiscalYear = dbo.view_Sales_Get_Fiscal_Week.FiscalYear
FROM dbo.My_Sales_Table
iNNER JOIN dbo.view_Sales_Get_Fiscal_Week
ON dbo.My_Sales_Table.Opp_ID = dbo.view_Sales_Get_Fiscal_Week.Opp_ID
GO
Any advice is appreciated
January 27, 2009 at 12:27 pm
Seems pretty straightforward. Gathers data from the other server and stores it on yours. There's nothing glaring about this that is inefficient or wrong. There are some things you could possibly due to either speed this thing up or eliminate the need for it all together, but none that I'd say need doing if this is not a problem right now.
January 27, 2009 at 12:44 pm
Thanks, I appreciate your feedback.
January 27, 2009 at 1:11 pm
Delete from My_Sales_Table
Is it some kind of staging table?
Then consider to use temp table or - even better - table variable.
It will eliminate distributed transaction.
As for static table - consider using TRUNCATE TABLE instead of DELETE FROM.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply