Unanswered: inserting unique or distinct values in table
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?
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.
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
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?
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