SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Inserting Records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SouthSideRob
Starting Member

USA
22 Posts

Posted - 07/22/2013 :  08:07:55  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/22/2013 :  08:09:23  Show Profile  Reply with Quote
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

USA
22 Posts

Posted - 07/22/2013 :  08:13:42  Show Profile  Reply with Quote
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

USA
22 Posts

Posted - 07/22/2013 :  08:44:41  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/22/2013 :  10:04:41  Show Profile  Reply with Quote

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


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

SouthSideRob
Starting Member

USA
22 Posts

Posted - 07/22/2013 :  12:05:26  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/22/2013 :  12:20:47  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000