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:
[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:
102 BANGOR ME
131 PORTSMOUTH NH
152 SEARSPORT ME
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.
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
--Import Ultimate Code Insert
insert into REF_JOC_PORT_LU_TBL (port_code, port_desc)
,DENSE_RANK() OVER (PARTITION BY Ultimate_Port_Code order by Ultimate_Port_Name) AS DENSE_RANK
where Ultimate_Port_Code not in (Select distinct port_code from dbo.ref_joc_port_lu_tbl)
group by Ultimate_Port_Code,
) as us_subset
where DENSE_RANK = 1
and Ultimate_Port_Code = ''
order by Ultimate_Port_Code;