March 20, 2012 at 12:31 pm
There are two tables A and B with the same data structure.(about 15 columns)
There are may have duplicate records in A.
B is empty table.
How to select all unique records from A to insert into B?
I tried to use distinct, group statement, but none of them is working.
March 20, 2012 at 12:36 pm
When you say duplicate, does it mean that duplicate values exist for all columns or for certain columns ? Depending upon number of columns having duplicate values, those columns should be placed in group by clause to get the list of those rows.
March 20, 2012 at 12:40 pm
There is a [Member ID] column will be set as primary key.
There may have duplicate [Member ID] in A but I only need to select one.
March 20, 2012 at 12:41 pm
Please provide DDL for the tables, sample data, and DML of your query for us to help.
Jared
CE - Microsoft
March 20, 2012 at 12:45 pm
The GROUP BY technique advised before should work as expected for you. Did you try it?
You could include all columns in the grouping set if needed.
Otherwise, please post some more information.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
March 20, 2012 at 12:46 pm
How to use DDL and DML?
March 20, 2012 at 12:49 pm
adonetok (3/20/2012)
How to use DDL and DML?
Please look at the article in my signature by Jeff Moden.
Jared
CE - Microsoft
March 20, 2012 at 12:49 pm
DDL is the code for the creation of your tables/views that are involved in your question.
See if this helps:
INSERT INTO TableB (Col1, Col2, Col3, Col4)
SELECT Col1, Col2, Col3, Col4
FROM TableA
GROUP BY Col1, Col2, Col3, Col4;
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
March 20, 2012 at 12:50 pm
The GROUP BY technique advised before should work as expected for you. Did you try it?
You could include all columns in the grouping set if needed.
Why would you use both DISTINCT and GROUP BY? It is redundant for results if you are searching for true duplicates.
EDIT: Sorry, you did not mention DISTINCT in your response. Sorry!
Jared
CE - Microsoft
March 20, 2012 at 12:56 pm
SQLKnowItAll (3/20/2012)
Why would you use both DISTINCT and GROUP BY? It is redundant for results if you are searching for true duplicates.EDIT: Sorry, you did not mention DISTINCT in your response. Sorry!
Strangely enough I've seen code using both before. 😀
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
March 20, 2012 at 1:02 pm
codebyo (3/20/2012)
SQLKnowItAll (3/20/2012)
Why would you use both DISTINCT and GROUP BY? It is redundant for results if you are searching for true duplicates.EDIT: Sorry, you did not mention DISTINCT in your response. Sorry!
Strangely enough I've seen code using both before. 😀
It makes for redundant duplicate removal. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 20, 2012 at 1:03 pm
Sean Lange (3/20/2012)
codebyo (3/20/2012)
SQLKnowItAll (3/20/2012)
Why would you use both DISTINCT and GROUP BY? It is redundant for results if you are searching for true duplicates.EDIT: Sorry, you did not mention DISTINCT in your response. Sorry!
Strangely enough I've seen code using both before. 😀
It makes for redundant duplicate removal. 😀
I guess they just want to be really really sure. 😀
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
March 20, 2012 at 2:18 pm
What is the query ? Post it and let's finish the discussion
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply