Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
SouthSideRob
Starting Member
22 Posts |
Posted - 2013-07-22 : 08:07:55
|
I have setup a script that inserts all of the records from a temp table to its permanent table. The table I'm working on now has 2 records that are completely identical. In my script, what is the best way to make sure I only insert one of the two duplicate records? Any help on this would be greatly appreciated. Thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 08:09:23
|
use DISTINCT in SELECTINSERT tableSELECT DISTINCT ....FROM #temptable ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
SouthSideRob
Starting Member
22 Posts |
Posted - 2013-07-22 : 08:13:42
|
I should have also explained that there are many records that are duplicated except 2 columns which is what I am using to make sure I insert only 1 record for every child in this case. Among the table of records, there are 2 columns that are identical in the 2 fields that are unique for all the other situations. If I use select distinct, I will still get duplicates from the other situations where there are duplicated fields... |
|
|
SouthSideRob
Starting Member
22 Posts |
Posted - 2013-07-22 : 08:44:41
|
Do i have to list the columns/fields in the select statement or can I just use a wild card?Right now, SELECT * FROM aaa_Dump_Table returns 9777 records andSELECT DISTINCT * FROM aaa_Dump_Table also returns 9777 records... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 10:04:41
|
[code]SELECT *FROM(SELECT ROW_NUMBER() OVER (PARTITION BY <all columns where value is same here> ORDER BY uniquevaluedcolumn) AS Seq,*FROM table)tWHERE Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
SouthSideRob
Starting Member
22 Posts |
Posted - 2013-07-22 : 12:05:26
|
Thanks for the help. I will try the last procedure but in using distinct first and a 2nd temp table, I was able to get the results I was looking. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 12:20:47
|
quote: Originally posted by SouthSideRob Thanks for the help. I will try the last procedure but in using distinct first and a 2nd temp table, I was able to get the results I was looking.
you dont need temporary table usage if you use last approach. it will do it in single step.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|