June 7, 2011 at 3:29 pm
bopeavy (6/7/2011)
I do agree with you Sean a select distinct will always work assuming that a user has not entered data Incorrectly. My example was assuming that the 3 rows I provided was indeed the same record but entered incorrectly that making them distinct from one another. But with out some sample data of of the issue we are all guessing to help this person.
Maybe I am splitting hairs but 3 rows can't be the same record. There are 3 rows and in the case you are discussing the contents are not the same. If there are 3 rows in a table there are 3 rows. It is not conditional. And distinct will always 1 and only 1 row for each row of unique information period. There are no conditionals of when distinct will or will not work. It always works exactly the same way.
In the case of this scenario distinct doesn't solve the problem because the original data is not unique.
_______________________________________________________________
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/
June 8, 2011 at 5:32 am
Hey sean I understand and said I agree with you about the distinct. I was pointing out the fact of that people make mistakes and if the 3 rows i gave was suppose to be the same record but got entered wrong it was not about distinct at all but about data being corrupted be human error.
June 9, 2011 at 8:48 am
I personally prefer the NOT EXISTS clause:
INSERT INTO invoices (INVNUM, PROVIDER, DIVISION, BA, AGE, SEX, TES_CRE_DT,
INV_CRE_DT, INV_SER_DT, measure)
SELECT A.invoice, A.Provider, A.Division, A.BA, A.AGE_AT_DOS, A.SEX, A.TES_CRE, A.INV_CRE_DT,
A.INV_SER_DT, A.Measure
SELECT A.invoice, A.Provider, A.Division, A.BA, A.AGE_AT_DOS, A.SEX, A.TES_CRE, A.INV_CRE_DT,
A.INV_SER_DT, A.Measure
FROM IDXRaw A
WHERE NOT EXISTS (SELECT * FROM invoices WHERE A.INVOICE = INVNUM AND A.MEASURE = MEASURE)
June 9, 2011 at 8:52 am
the problem with using the Exists clause in this case is that there are duplicates in the source. Using the Not exists will eliminate those in the source that are already in the destination but will retrieve the combo in the source (including duplicates) that do not yet exist. That is why he said it pulled duplicates the first run but then it did not on the second. In essence he needed to use both the not exists in the destination and a distinct on the source.
_______________________________________________________________
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/
Viewing 4 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply