SQL Server 2005 - 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

SSIS and Import/Export (2005)

SQL Server 2005

Author

Topic

Dev@nlkss

134 Posts

Posted - 2009-01-21 : 09:24:49

Hi All,
I am writing stored procedure for transfering data from 4 staging tables to 4 master tables using sql server 2005 which are in same db and same server.
I have following requirements
-->I will first check the count in both staging and master tables
-->if staging table count is greater than master table count then
-->truncate master table records.
-->then insert query executes which fetches records from staging to master table.

For each staging to master table i am writnig individual transaction
begin try
begin transaction
code goes here
commit transaction
end try
begin catch
rollback transaction
end catch

like for for 4 staging tables to 4 master table

But the problem is when i am executing this sql server getting very slow almost getting struck.
Is this the correct way which i am following if not please suggest me the write way.
its really urgent.
Thanks in advance

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 09:30:40

are all four transfers dependent?

Dev@nlkss

134 Posts

Posted - 2009-01-21 : 09:38:13

Visakh thanks for ur reply
First 2 are not dependent
but after inserting from third staging to master table i will get one of the columns from third master table and insert into fourth master table
i.e last two are dependent

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 09:40:51

then why putting them in seperate tranbsactions? put dependent ones in same tran

Dev@nlkss

134 Posts

Posted - 2009-01-21 : 09:46:39

does it mean dependent tables are in single transaction

Satya

Dev@nlkss

134 Posts

Posted - 2009-01-21 : 09:57:29

Thanks visakh
I am posting my sql script
please go throgth once and guide me if i am going wrong.

BEGIN
SET NOCOUNT ON;
--Load the Records from Staging tables to master table
BEGIN TRY
BEGIN TRANSACTION;
IF((SELECT COUNT(VendorID) FROM [dbo].[Vendor_Stage]) > (SELECT COUNT(VendorID) FROM [dbo].[Vendor]))
BEGIN
TRUNCATE TABLE [dbo].[Vendor]
INSERT INTO [dbo].[Vendor]
SELECT VendorID,VendorName,VendorType,CircleID FROM [dbo].[Vendor_stage]
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH

BEGIN TRY
BEGIN TRANSACTION;
IF((SELECT COUNT(DeptID) FROM [dbo].[DeptInfo_HRMSPark]) > (SELECT COUNT(DeptID) FROM [dbo].[DeptInfo]))
BEGIN
TRUNCATE TABLE [dbo].[DeptInfo]
INSERT INTO [dbo].[DeptInfo]
SELECT DeptID,DeptDesc FROM [dbo].[DeptInfo_HRMSPark]
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH

BEGIN TRY
BEGIN TRANSACTION;
IF((SELECT COUNT(DesgID) FROM [dbo].[Designations_HRMSPark]) > (SELECT COUNT(DesgID) FROM [dbo].[Designations]))
BEGIN
TRUNCATE TABLE [dbo].[Designations]
INSERT INTO [dbo].[Designations]
SELECT DesgID,DesgName FROM [dbo].[Designations_HRMSPark]
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH

BEGIN TRY
BEGIN TRANSACTION;
IF((SELECT COUNT(DISTINCT EmpID) FROM [dbo].[empdetails_hrmspark]) > (SELECT COUNT(DISTINCT EmpID) FROM [dbo].[Employees]))
BEGIN
TRUNCATE TABLE [dbo].[Employees]
INSERT INTO [dbo].[Employees]
SELECT DISTINCT ED.EmpID,ED.EmpCode,CL.Display AS DisplayName,NULL,NULL,Gender,CL.BirthDay AS DOB,DOJ,DelFlag AS HRMSStatus,ED.CircleID,Designation,DeptID
FROM [dbo].[empdetails_hrmspark] ED
INNER JOIN [dbo].[contactslist_hrmspark] CL
ON ED.EmpID=CL.ContactID

INSERT INTO [dbo].[ITOEngineerList](EmpID)
SELECT EmpID
FROM [dbo].[Employees]
WHERE EmpID NOT IN(SELECT EmpID FROM [dbo].[EngineerList])
END
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH

SET NOCOUNT OFF
END

Satya

Dev@nlkss

134 Posts

Posted - 2009-01-22 : 00:25:34

Hi
please look into the above script.

Satya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources