Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2010
    Posts
    153

    Smile Unanswered: inserting unique or distinct values in table

    Hi guys,
    i have a doubt. i am migrating data dynamically from excel into our sql 2005 database using sql inbuilt tool SSIS business intelligence. teh things is that i have set all teh things for migration and data is also getting migrated but when iam appending data in excel file and if suppose there are duplicate data in excel file, we should not get duolicate values in our database table.

    i want to insert distinct / unique data in database.

    Has anyone used SSIS where we can find a place to write a query which can act like a mediator for selecting / fetching only unique value (values should not repeat) and inserts values into our database table?



    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Easiest thing is to use SSIS to write to a staging table with no constraints. Use T-SQL to clean the data, do what ever logging, changes etc. required, and then finally write all cleaned, unique rows to the destination table.

    You can do everything you want in SSIS but in my experience the ROI just is not there.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2010
    Posts
    153

    Smile thanks buddy:) ill implement your idea.

    I have an another idea. We can migrate data from excel to our database using SSIS this table will be our dummy table. we will create another main table and we will fire trigegr. insertion on dummy table will insert values in the main table as well as in the mean time we can insert only unique values in that table




    Thanks guys

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would not use a trigger. I would use a procedure. Using a trigger introduces complications.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The same process that loads the staging table can fire the sproc when it is finished.
    Do not use a trigger. That will cause a lot of complications for debugging the process.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2010
    Posts
    153

    Smile thanks a lot:)

    .

    but i am pretty new to database. I don't know how to do it . I am trying my best but could you write in detais how to do it step by step . I should not have asked you to write a procedure for me but I am pretty new to database . How to wrte a procedure that fires and another table gets inserted automatically?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Worry about the "fires automatically" last. You build these things up step by step.

    I assume you have data in the staging table ready (the one you were going to put the trigger on).

    1. Write all the scripts (update, delete SQL statements) you need to clean up and verify the data in the staging table.
    2. Once complete, write an insert statement to insert distinct rows in to the final table. You might need to check if rows already exist in the final table and deal with those.
    3. Once all this is working, put your code in to a stored procedure
    4. Finally, once the procedure is tested you have it called by the SSIS package, or by whatever started the SSIS package

    Make sense?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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