Insert unique records from staging

  • 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.

  • Try the MERGE command!

  • 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]

  • Thanks everyone. Merge worked for me

  • 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]

  • 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