none
refrence table

    Question

  • hi,

    in my master table i have 1 column which has values 1,2

    example :

    type

    1

    2,

    now i have referenct table

    type code

    1                  employee

    2                 department

    i am loading my data into master table thorugh ssis,

    hwo to i check for values that should be from reference table only.

    Wednesday, November 21, 2012 2:45 PM

Answers

  • Just do two hops: one to pick only those matching records through an inner join and the other to flag off these that did not match (no x-ref to id).

    You are trying to sync tables, one approach is using the T-SQL MERGE clause, quite easy IMHO, or you can go with a pure SSIS approach:

    http://www.sqlis.com/sqlis/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx


    Arthur My Blog

    • Marked as answer by Eileen Zhao Wednesday, November 28, 2012 2:17 AM
    Wednesday, November 21, 2012 6:15 PM
    Moderator

All replies

  • Not entirely sure what your question is. But if you're trying to only load data into the master table where the type already exists in the reference table, then you have multiple options. If the data is all in SQL Server already, just use an Execute T-SQL to do a MERGE or INSERT (with a JOIN) statement. The below code will auto filter out anything that doesn't match in reference.

    INSERT INTO dbo.MasterTable (Type)
    SELECT Type
    FROM dbo.MyOtherTable mot
    INNER JOIN dbo.Reference r
    ON mot.Type = r.Type

    If you're loading from a file or a non-SQL Server source, in your Data Flow Task you can use a Conditional Split Transformation to send the good data to SQL Server and either send the bad data to an error table or ignore the bad data or do "other things" with it. Some people use the Lookup transformation, but I'm not a fan of it since it gives such a performance hit (or does in 2005, anyway). I haven't worked with Term Lookup, so I'm not sure if that would work or not.

    Lastly, you could just load the file into a staging table and use an Execute T-SQL Task with a stored procedure / view / above JOIN code to do your final insert.


    MCITP:DBA, MCDBA, MCSA

    Wednesday, November 21, 2012 3:07 PM
  • Lookup transformation.  http://msdn.microsoft.com/en-us/library/ms141821.aspx

    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, November 21, 2012 3:07 PM
  • do an ignore failure  in look up when no match to refrence .entire data will be inserted to ur destination.just do

    select * from [table name] where id_column is null. this will give u set of records where u did  nt had values to reference.

    or

    u can redirect the rows on lookup no match to flat file.these will b records which are not in reference. and hence inserted records will be from reference


    • Edited by Nishink Wednesday, November 21, 2012 5:18 PM
    Wednesday, November 21, 2012 5:17 PM
  • this is not what i want

    i have 2 table

    master

    empid  empname

    1          smith

    2          john

    3             rob

    reference for empid will be

    emp_ref

    empid empdesc

    1              it

    2             civil

    3             designer

    now in my master table how i write check constraint which also accept null

    Wednesday, November 21, 2012 5:29 PM
  • check constraint on master table for checking the ids of emp_ref table?
     

    if u want ur master table to have only those values which are there in ur reference then u can create a Pk for id in reference table

    and foreign key constraint on master table .so u have to have a value in reference table then only u can enter the value in master table.

    but then u cannot enter nulls.



    • Edited by Nishink Wednesday, November 21, 2012 6:13 PM
    Wednesday, November 21, 2012 6:05 PM
  • i have done pk->fk

    but now in my master which has fk ,some of the values coming from file are blank,

    which failed my package.

    i am allowing foreign key to have null values

    Wednesday, November 21, 2012 6:15 PM
  • Just do two hops: one to pick only those matching records through an inner join and the other to flag off these that did not match (no x-ref to id).

    You are trying to sync tables, one approach is using the T-SQL MERGE clause, quite easy IMHO, or you can go with a pure SSIS approach:

    http://www.sqlis.com/sqlis/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx


    Arthur My Blog

    • Marked as answer by Eileen Zhao Wednesday, November 28, 2012 2:17 AM
    Wednesday, November 21, 2012 6:15 PM
    Moderator
  • Since u dont want to use look up.

    what u can try is .create a dummy value in ur refernce table for null say 100 (or some value.Best is populate 1st value for null)so that 100 signifies for null.

    and when u load the data via ssis u can make all the null records as 100 and insert.(its a workaround just for not failing the package)


    • Edited by Nishink Wednesday, November 21, 2012 6:32 PM
    Wednesday, November 21, 2012 6:32 PM
  • A check constraint is different than actually inserting the values.

    Here are articles on the concepts of check constraints and on how to create check constraints. Note that NULLS can override a check constraint.


    MCITP:DBA, MCDBA, MCSA

    Monday, November 26, 2012 11:57 AM