Click here to Skip to main content
15,509,806 members
Home / Discussions / Database
   

Database

 
Questionsql server insert into select + in select part if else Pin
Karan_TN7-Aug-14 1:16
Karan_TN7-Aug-14 1:16 
AnswerRe: sql server insert into select + in select part if else Pin
Mycroft Holmes7-Aug-14 1:55
professionalMycroft Holmes7-Aug-14 1:55 
QuestionHow to change multiple date formats in Sql server Pin
AzeeM_R6-Aug-14 3:51
professionalAzeeM_R6-Aug-14 3:51 
AnswerRe: How to change multiple date formats in Sql server Pin
Corporal Agarn6-Aug-14 4:38
professionalCorporal Agarn6-Aug-14 4:38 
AnswerRe: How to change multiple date formats in Sql server Pin
Richard Deeming6-Aug-14 4:50
mveRichard Deeming6-Aug-14 4:50 
AnswerRe: How to change multiple date formats in Sql server Pin
Mycroft Holmes6-Aug-14 15:16
professionalMycroft Holmes6-Aug-14 15:16 
SuggestionRe: How to change multiple date formats in Sql server Pin
AzeeM_R11-Aug-14 9:14
professionalAzeeM_R11-Aug-14 9:14 
QuestionHandling Duplicate records Pin
Ambertje5-Aug-14 5:35
Ambertje5-Aug-14 5:35 
Hello everyone,

I came across a problem when I loaded my records from SSIS to SQL DB.
My source file is a txt file.

I use 5 fields to make my records unique but unfortunately there are some records that can't be made unique.
Even the records that aren't unique must be loaded into my table although in SSIS the duplicate records are written to an error log and are excluded out of the load into the DB.

This is a small example of a few records:
record 1 and 2 are unique, 3 and 4 not.

CSS
HEADER  WH  DATE_CREATED    TIME_CREATED    PO  RECEIPT ARTICLE QTY_PBS CW  CODE_DATE_RECEIVED  SYSTEM_FLAG QTY_PBL FLOW_RECEIVING_FLAG FLOW_DEMAND_FLAG    VENDOR_ID   SHIPPING_UNIT   WH_FROM AANT_PALLETS    SSCC_NR EMPTY_FACTURATION_FORM
1010    3   30/07/2014  11:49:24    861819693   71663   1610570 4   36507   18/08/2014  S   0   N   N   B986        77000   0   7   054001090102087337
1010    3   30/07/2014  11:49:24    861819693   71663   6750    1   0   18/08/2014  S   0   N   N   B986        77000   0   7   054001090102087337  I
1010    3   30/07/2014  11:49:24    861819693   71663   6681    4   0   18/08/2014  S   0   N   N   B986        77000   0   7   054001090102087337  I
1010    3   30/07/2014  11:49:24    861819693   71663   6681    16  0   18/08/2014  S   0   N   N   B986        77000   0   7   054001090102087337  I




In SQL I use a View to transform the data and load only the data I need from the txt file:
SQL
ALTER VIEW [dbo].[F_Received_Colli_VW]
AS
SELECT
     CAST(NULL AS Numeric(8, 0))                                                                AS DT_Start_Dates
    , CAST(NULL AS NVARCHAR(6))                                                                 AS TI_Start_Times
    , CAST(NULL AS Numeric(8, 0))                                                               AS DT_Code_Dates
    , NULL                                                                                      AS FK_Article
    , NULL                                                                                      AS FK_FlowType

    , CAST(CAST(RIGHT(dbo.AD.DATE_CREATED, 4) + SUBSTRING(dbo.AD.DATE_CREATED, 4, 2)
        + LEFT(dbo.AD.DATE_CREATED, 2) AS int) AS Numeric(8, 0))                                AS CREATE_DATE

    , CAST(dbo.LPAD(LEFT(dbo.AD.TIME_CREATED, 2) + SUBSTRING(dbo.AD.TIME_CREATED, 4, 2)
        + RIGHT(dbo.AD.TIME_CREATED, 2) , 6, '0') AS NVARCHAR(6))                               AS CREATE_TIME

    , CAST(CAST(RIGHT(dbo.AD.CODE_DATE_RECEIVED, 4) + SUBSTRING(dbo.AD.CODE_DATE_RECEIVED, 4, 2)
    + LEFT(dbo.AD.CODE_DATE_RECEIVED, 2) AS int) AS Numeric(8, 0))                              AS CODE_DATE

    , CAST(dbo.AD.VENDOR_ID AS nvarchar(255))                                                   AS Vendor_ID_Cd
    , CAST(CASE WHEN dbo.AD.WH IS NULL THEN 0 ELSE dbo.AD.WH END AS nvarchar(255))              AS WH_Cd
    , dbo.AD.PO                                                                                 AS BK_PO_Cd
    , dbo.AD.RECEIPT                                                                            AS BK_Receipt_Cd

    , CAST(dbo.Devide_Shipping_Unit(dbo.AD.SHIPPING_UNIT, dbo.AD.ARTICLE) AS Nvarchar (255))    AS BK_Shipping_Unit_Cd

    , dbo.AD.SSCC_NR                                                                            AS BK_License_Plate_Cd
    , dbo.AD.ARTICLE                                                                            AS BK_Article_Cd

    , CAST(dbo.Devide_Shipping_Unit(dbo.AD.SHIPPING_UNIT, dbo.AD.ARTICLE) AS Nvarchar (255))    AS SHIPPING_UNIT

    , dbo.AD.ARTICLE

    , CASE DWH.dbo.D_Article.FlowType WHEN 'SAP' THEN ISNULL(CAST(dbo.AD.QTY_PBS AS int), 0)
        ELSE ISNULL(CAST(dbo.AD.QTY_PBL AS int), 0) END                                         AS M_Nr_Of_Colli

    , CAST(ISNULL(dbo.AD.CW, 0) AS numeric(15,2)) / 100                                         AS M_Received_Weight
    , 1                                                                                         AS M_Nr_of_Pallets

FROM
(SELECT        dbo.GETRESTARTTIMESTAMP(N'F_Received_Colli') AS restartTS) AS tmp CROSS JOIN
            dbo.AD LEFT OUTER JOIN
            DWH.dbo.D_Article ON (dbo.AD.ARTICLE = DWH.dbo.D_Article.BK_Article_Cd) and CAST(dbo.Devide_Shipping_Unit(dbo.AD.SHIPPING_UNIT, dbo.AD.ARTICLE) AS int) = Cast(DWH.dbo.D_Article.BK_Unit_Ship_Case_Cd AS int) 
WHERE    (dbo.AD.HEADER = '1010')


The fieldNames starting with BK_ are the fields I'm using to make a record unique. So I have 5 BK fields to make a record unique in the View:
1) BK_PO_Cd
2) BK_Receipt_Cd
3) BK_License_Plate_Cd
4) BK_Article_Cd
5) BK_Shipping_Unit_Cd

I think I can solve this by sorting on the BK's and then checking if the record exists.
If the record exists then the amount of field "QTY_PBS" should be added to the first record.
In case of my example it would be 1 record with an amount of 20 instead of 2 records with amount 4 and 16.

I tried to create a SP to resolve this issue but I'm failing misserably (lack of knowledge).

Can somebody please help me with this???

USE [DWH]
GO

/****** Object:  StoredProcedure [dbo].[SP_Check_Duplicates]    Script Date: 5/08/2014 16:19:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE Check_Duplicates

(
@License_Plate VARCHAR(18),
@Article VARCHAR(18),
@PO VARCHAR(20),
@Receipt VARCHAR(5),  
@M_Nr_Of_Colli Int  
)

AS

BEGIN

Set @License_Plate = '054001090102087337'        
Set @Article = '0006681'
Set @PO = '0861819693'
Set @Receipt = '71663'
Set @M_Nr_Of_Colli = 16

      IF EXISTS (SELECT [M_Nr_of_Colli] FROM F_Received_Colli_VW 
					WHERE [BK_License_Plate_Cd]=@License_Plate AND BK_Article_Cd = @Article)

      BEGIN

            INSERT INTO F_Received_Colli_VW VALUES(@License_Plate,@Article,@PO, @Receipt, @M_Nr_Of_Colli)

            PRINT 'New Record Insert Successfuly'

      END

      ELSE

      BEGIN

            PRINT 'Id All Ready Exist'

      END
END



Kind regards,
Ambertje
Questionsplit datatable column using c# Pin
Jaimin H. Soni4-Aug-14 23:31
Jaimin H. Soni4-Aug-14 23:31 
QuestionRe: split datatable column using c# Pin
Eddy Vluggen5-Aug-14 3:35
professionalEddy Vluggen5-Aug-14 3:35 
AnswerRe: split datatable column using c# Pin
Jaimin H. Soni6-Aug-14 2:12
Jaimin H. Soni6-Aug-14 2:12 
GeneralRe: split datatable column using c# Pin
Eddy Vluggen6-Aug-14 2:56
professionalEddy Vluggen6-Aug-14 2:56 
GeneralRe: split datatable column using c# Pin
Jaimin H. Soni6-Aug-14 3:00
Jaimin H. Soni6-Aug-14 3:00 
GeneralRe: split datatable column using c# Pin
Eddy Vluggen6-Aug-14 3:30
professionalEddy Vluggen6-Aug-14 3:30 
QuestionHelp with XML Pin
byka31-Jul-14 7:36
byka31-Jul-14 7:36 
GeneralRe: Help with XML Pin
PIEBALDconsult31-Jul-14 8:01
professionalPIEBALDconsult31-Jul-14 8:01 
GeneralRe: Help with XML Pin
byka31-Jul-14 8:04
byka31-Jul-14 8:04 
AnswerRe: Help with XML Pin
Richard Deeming31-Jul-14 8:29
mveRichard Deeming31-Jul-14 8:29 
QuestionCannot create a foreign key reference (probably my fault) Pin
Richard MacCutchan31-Jul-14 1:38
mveRichard MacCutchan31-Jul-14 1:38 
AnswerRe: Cannot create a foreign key reference (probably my fault) Pin
Simon_Whale31-Jul-14 2:27
professionalSimon_Whale31-Jul-14 2:27 
GeneralRe: Cannot create a foreign key reference (probably my fault) Pin
Richard MacCutchan31-Jul-14 2:36
mveRichard MacCutchan31-Jul-14 2:36 
AnswerRe: Cannot create a foreign key reference (probably my fault) Pin
Eddy Vluggen31-Jul-14 2:28
professionalEddy Vluggen31-Jul-14 2:28 
GeneralRe: Cannot create a foreign key reference (probably my fault) Pin
Richard MacCutchan31-Jul-14 2:39
mveRichard MacCutchan31-Jul-14 2:39 
GeneralRe: Cannot create a foreign key reference (probably my fault) Pin
Eddy Vluggen31-Jul-14 12:08
professionalEddy Vluggen31-Jul-14 12:08 
QuestionInsert into temptable ( #tmp1 ) that execute result from 1 procedure ( return 2 results ) Pin
LTMKH30-Jul-14 23:30
LTMKH30-Jul-14 23:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.