June 9, 2011 at 12:08 pm
Hi,
I have a staging table STAG
Col1 Col2 col3 col4....
and a main table dbo.Main
which have the same columns like stag
Col1 Col2 col3 col4....
I want to insert unique records from stag to main based on Col1,Clo2, and col3
that is if Col1,Col2,Col3 is not there in main and is present in stag then insert else dont insert.
The satging is truncated after every file is processed by SSIS.
Please help.
June 9, 2011 at 1:24 pm
I'll need to explore that Merge command myself, but I have a stored procedure already set up for a process just like this. This is my live code, and it does not reveal any proprietary data or other information. It is clunky I'm sure, but it works every time. I have not added it to the SSIS procedure yet, but the only thing missing is the main procedure call (Exec dbo.DailyList).
dbo.DailyList
USE [JaridProjectProcessing]
GO
/****** Object: StoredProcedure [dbo].[DailyList] Script Date: 06/09/2011 14:19:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Jarid Lawson
-- Create date: 10/1/10
-- Description:A 2nd look at my project tracking process
-- showed a few areas where I could impove. One of them
-- is to create this stored procedure. There are not
-- many changes to the basic process beyond this.
-- =============================================
ALTER PROCEDURE [dbo].[DailyList]
AS
BEGIN
SET NOCOUNT ON
---------------
If Not Exists
(
Select *
From SysObjects
Where ID=Object_ID('dbo.ProjectTracking_Temp')
)
Begin
Print 'The ProjectTracking_Temp table is missing. Please load the file and re-run.'
-----
Return
End
---------------
Exec dbo.DailyList_BackupCurrent
-----
Exec dbo.DailyList_Prep
-----
Exec dbo.DailyList_Prep2
-----
Exec dbo.DailyList_Main
END
dbo.DailyList_BackupCurrent
USE [JaridProjectProcessing]
GO
/****** Object: StoredProcedure [dbo].[DailyList_BackupCurrent] Script Date: 06/09/2011 14:20:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DailyList_BackupCurrent]
AS
BEGIN
SET NOCOUNT ON;
/* ------------------------------------------------------------ */
-- Backup the current data
/* ------------------------------------------------------------ */
If Exists
(
Select *
From SysObjects
Where ID=Object_ID('dbo.ProjectTracking_BU')
)
Begin
Drop Table dbo.ProjectTracking_BU
End
-----
If Exists
(
Select *
From SysObjects
Where ID=Object_ID('dbo.ProjectTracking_Temp_BU')
)
Begin
Drop Table dbo.ProjectTracking_Temp_BU
End
------------------------------
Select *
Into dbo.ProjectTracking_BU
From dbo.ProjectTracking
---------------
Select *
Into dbo.ProjectTracking_Temp_BU
From dbo.ProjectTracking_Temp
---------------
Delete
From dbo.ProjectTracking_Temp
Where Ltrim(Rtrim(AssignDate))='Top Priority'
---------------
Truncate Table dbo.ProjectTracking
END
dbo.DailyList_Prep
USE [JaridProjectProcessing]
GO
/****** Object: StoredProcedure [dbo].[DailyList_Prep] Script Date: 06/09/2011 14:20:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DailyList_Prep]
AS
BEGIN
SET NOCOUNT ON;
If Not Exists
(
Select *
From SysColumns
Where ID=Object_ID('dbo.ProjectTracking')
And Name='Oid_Record'
)
Begin
Alter Table dbo.ProjectTracking
Add Oid_Record Varchar(4000)
End
-----
If Not Exists
(
Select *
From SysColumns
Where ID=Object_ID('dbo.ProjectTracking_Temp')
And Name='Oid_Record'
)
Begin
Alter Table dbo.ProjectTracking_Temp
Add Oid_Record Varchar(4000)
End
-----
If Not Exists
(
Select *
From SysColumns
Where ID=Object_ID('dbo.ProjectTracking_Temp')
And Name='ExistsFlag'
)
Begin
Alter Table dbo.ProjectTracking_Temp
Add ExistsFlag Char(1)
End
END
dbo.DailyList_Prep2
USE [JaridProjectProcessing]
GO
/****** Object: StoredProcedure [dbo].[DailyList_Prep2] Script Date: 06/09/2011 14:21:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DailyList_Prep2]
AS
BEGIN
SET NOCOUNT ON;
Update dbo.ProjectTracking
Set Oid_Record=''
-----
Update dbo.ProjectTracking_Temp
Set Oid_Record=''
-----
Update dbo.ProjectTracking_Temp
Set ExistsFlag=''
------------------------------
/* ************************ */
-- Script modified
-- By Jarid Lawson
-- 11/4/10
--
-- Adding field 'DatabaseName' to this section to account for the occasional time
-- when I recieve 2 or more projects for the same MedNum, ServiceType, HospitalSystem,
-- HospitalName, & ResourceType on the same date, but for different types of
-- projects (i.e. CO, MM, IND, SP, etc.). This is typically used for ResourceType
-- 'New'. I didn't change to 'New - MM', 'New - IND', etc. since that would change a
-- large part of my daily record keeping. This just seemed easier.
/* ************************ */
Update dbo.ProjectTracking
Set Oid_Record=MedNum
+ '_x_'
+ ServiceType
+ '_x_'
+ ResourceType
+ '_x_'
+ FiscalYears
+ '_x_'
+ AssignDate
+ '_x_'
+ DatabaseName
-----
Update dbo.ProjectTracking_Temp
Set Oid_Record=MedNum
+ '_x_'
+ ServiceType
+ '_x_'
+ ResourceType
+ '_x_'
+ FiscalYears
+ '_x_'
+ AssignDate
+ '_x_'
+ DatabaseName
---------------
Create Table #QuickCheck
(
RecordCount Int,
Oid_Record Varchar(4000)
)
-----
Insert Into #QuickCheck
(
RecordCount,
Oid_Record
)
Select Count(*),
Oid_Record
From dbo.ProjectTracking
Group By Oid_Record
---------------
Declare @ErrorCount Int
Set @ErrorCount=0
-----
Set @ErrorCount=(Select Count(*) From #QuickCheck Where RecordCount>1)
-----
If @ErrorCount>0
Begin
Select 'Duplicate records exist on dbo.ProjectTracking. Tables reset.'
-----
Select *
From #QuickCheck
Where RecordCount>1
---------------
Drop Table dbo.ProjectTracking
-----
Drop Table dbo.ProjectTracking_Temp
---------------
Exec sp_Rename 'dbo.ProjectTracking_BU','ProjectTracking','Object'
-----
Exec sp_Rename 'dbo.ProjectTracking_Temp_BU','ProjectTracking_Temp','Object'
---------------
Return
End
------------------------------
Truncate Table #QuickCheck
-----
Set @ErrorCount=0
---------------
Insert Into #QuickCheck
(
RecordCount,
Oid_Record
)
Select Count(*),
Oid_Record
From dbo.ProjectTracking_Temp
Group By Oid_Record
---------------
Set @ErrorCount=0
-----
Set @ErrorCount=(Select Count(*) From #QuickCheck Where RecordCount>1)
-----
If @ErrorCount>0
Begin
Select 'Duplicate records exist on dbo.ProjectTracking_Temp. Tables reset.'
-----
Select *
From #QuickCheck
Where RecordCount>1
---------------
Drop Table dbo.ProjectTracking
-----
Drop Table dbo.ProjectTracking_Temp
---------------
Exec sp_Rename 'dbo.ProjectTracking_BU','ProjectTracking','Object'
-----
Exec sp_Rename 'dbo.ProjectTracking_Temp_BU','ProjectTracking_Temp','Object'
---------------
Return
End
------------------------------
Drop Table #QuickCheck
END
dbo.DailyList_Main
USE [JaridProjectProcessing]
GO
/****** Object: StoredProcedure [dbo].[DailyList_Main] Script Date: 06/09/2011 14:21:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DailyList_Main]
AS
BEGIN
SET NOCOUNT ON;
Update dbo.ProjectTracking_Temp
Set ExistsFlag=''
-----
Update dbo.ProjectTracking_Temp
Set ExistsFlag='Y'
Where Oid_Record In
(
Select Oid_Record
From dbo.ProjectTracking
)
------------------------------
Update M
Set M.CompleteDate=T.CompleteDate,
M.ProjectNotes=T.ProjectNotes,
M.CompleteFlag=T.CompleteFlag,
M.PriorityOrder=T.PriorityOrder
From dbo.ProjectTracking M Inner Join dbo.ProjectTracking_Temp T
On M.Oid_Record=T.Oid_Record
Where T.ExistsFlag='Y'
---------------
Delete
From dbo.ProjectTracking_Temp
Where ExistsFlag='Y'
/* ------------------------------------------------------------ */
-- Import _Temp table data
/* ------------------------------------------------------------ */
Insert Into dbo.ProjectTracking
(
AssignDate,
CompleteDate,
DueDate,
EstRecovery,
ETA,
ResourceType,
ServiceType,
MedNum,
HospitalSystem,
HospitalName,
DatabaseName,
FiscalYears,
ProjectNotes,
CompleteFlag,
PriorityOrder
)
Select AssignDate,
CompleteDate,
DueDate,
EstRecovery,
ETA,
ResourceType,
ServiceType,
MedNum,
HospitalSystem,
HospitalName,
DatabaseName,
FiscalYears,
ProjectNotes,
CompleteFlag,
PriorityOrder
From dbo.ProjectTracking_Temp
------------------------------
Truncate Table dbo.ProjectTracking_Temp
/* ------------------------------------------------------------ */
-- Move completed projects to archive table
/* ------------------------------------------------------------ */
Insert Into dbo.ProjectTracking_Archive
(
AssignDate,
CompleteDate,
DueDate,
EstRecovery,
ETA,
ResourceType,
ServiceType,
MedNum,
HospitalSystem,
HospitalName,
DatabaseName,
FiscalYears,
ProjectNotes,
CompleteFlag,
PriorityOrder
)
Select AssignDate,
CompleteDate,
DueDate,
EstRecovery,
ETA,
ResourceType,
ServiceType,
MedNum,
HospitalSystem,
HospitalName,
DatabaseName,
FiscalYears,
ProjectNotes,
CompleteFlag,
PriorityOrder
From dbo.ProjectTracking
Where CompleteFlag='Y'
And CompleteDate<>''
------------------------------
Delete
From dbo.ProjectTracking
Where CompleteFlag='Y'
And CompleteDate<>''
/* ------------------------------------------------------------ */
-- Run cleanup steps
/* ------------------------------------------------------------ */
Alter Table dbo.ProjectTracking
Drop Column Oid_Record
---------------
Drop Table dbo.ProjectTracking_Temp
/* ------------------------------------------------------------ */
-- Generate new report
/* ------------------------------------------------------------ */
Select *
From dbo.ProjectTracking
Order By PriorityOrder Desc,
Convert(DateTime,DueDate) Asc,
EstRecovery Desc,
Convert(DateTime,AssignDate) Asc
END
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
June 9, 2011 at 2:15 pm
Thanks everyone. Merge worked for me
June 9, 2011 at 2:19 pm
Sorry, I forgot to include example data:
Insert Into dbo.ProjectTracking_Temp
(AssignDate,
CompleteDate,
DueDate,
EstRecovery,
ETA,
ResourceType,
ServiceType,
MedNum,
HospitalSystem,
HospitalName,
DatabaseName,
FiscalYears,
ProjectNotes,
CompleteFlag,
PriorityOrder)
Select '06/02/11',
'',
'',
'10,590.00',
'',
'PCL',
'MCBD-MA',
'112233',
'',
'ACME Hospital',
'112233_ACME_CO_MA_FY0510',
'0910',
'',
'',
'G'
Union All
Select '06/07/11',
'06/09/11',
'09/03/12',
'364,352.00',
'',
'PSR',
'MCBD',
'112233',
'',
'ACME Hospital',
'112233_ACME_CO_FY0510',
'0508',
'',
'Y',
'G'
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
June 10, 2011 at 6:16 am
insert into Main
select * from stg
except
select * from main
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply