Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: REF TBL Merge Process

    OK guys I have a simple one that I cannot get my head around.

    I am in SQL Server 2008 SSIS. I have a ref table that looks like this:

    Code:
    [dbo].[REF_JOC_PORT_LU_TBL](
    	[PORT_ID] [int] IDENTITY(100,1) NOT NULL,
    	[PORT_Code] [int] NOT NULL,
    	[PORT_Desc] [varchar](13) NULL,
    	[Insert_Date]  AS (getdate()),
    	[Update_Date] [datetime] NULL,
     CONSTRAINT [PK_REF_JOC_PORT_LU_TBL] PRIMARY KEY CLUSTERED ) ON [PRIMARY]
    I will be loading this table monthly. I have no problems figureing out what new records in my source to insert into the table.

    My problem is that the data is entered free hand, so I can and do have duplicates. The look like this:

    Code:
    PORT_CODE PORT_DESC
    102	BANGOR ME    
    131	PORTSMOUTH NH
    132	BELFAST      
    152	SEARSPORT ME 
    152	SEARSPORT
    Because of this duplication I cannot use the new MERGE code...it tries to update more than one row and dies. I would love to use an SSIS stage for this, but im not sure what to try now. I have been staring at this for a while and I need a new set of eyes.

    Any help will be greatly apreceated.

    Thanks
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do you want to include separate rows for each of the PORT_DESC values, or just one PORT_DESC for a given PORT_CODE? If only one PORT_DESC, how do you decide which one to use?

    -PatP

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Quote Originally Posted by Pat Phelan
    Do you want to include separate rows for each of the PORT_DESC values, or just one PORT_DESC for a given PORT_CODE? If only one PORT_DESC, how do you decide which one to use?

    -PatP

    I can only have one. It can eather be the first one i come to or the second one can overwrite the first. It does not mater
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    The Answer

    I am taking advantage of a new function in SQL Server 2008, Dense Rank. This is how I am getting past this issue. Thanks for the help


    Code:
    --Import Ultimate Code Insert
    insert into REF_JOC_PORT_LU_TBL (port_code, port_desc)
    select Ultimate_Port_Code
    	,Ultimate_Port_Name
    From (
    select Ultimate_Port_Code
    	,Ultimate_Port_Name
    	,DENSE_RANK() OVER (PARTITION BY Ultimate_Port_Code order by Ultimate_Port_Name)     AS DENSE_RANK
    from dbo.STG_JOC_IMPORTS_TBL 
    where Ultimate_Port_Code not in (Select distinct port_code from dbo.ref_joc_port_lu_tbl)
    group by Ultimate_Port_Code, 
    	Ultimate_Port_Name
    ) as us_subset
    where DENSE_RANK = 1
    	and Ultimate_Port_Code = ''
    order by Ultimate_Port_Code;
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •