Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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
52326 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  
 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