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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Inserting Records

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 SELECT

INSERT table
SELECT DISTINCT ....
FROM #temptable


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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...
Go to Top of Page

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 and
SELECT DISTINCT * FROM aaa_Dump_Table also returns 9777 records...
Go to Top of Page

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
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -