June 3, 2011 at 2:39 pm
I have 2 tables:
IDXRaw and INVOICES. Both tables have fields INVNUM and MEASURE. IDXRaw will have multiple records with the same INVNUM and MEASURE values. I want to INSERT records from IDXRaw into INVOICES but exclude duplicates. I have a UNIQUE KEY with these 2 fields on the INVOICES table. What would be the correct syntax for this? There are other fields I am inserting but these are the 2 I need to check for duplicates. Not worried about values of other fields.
Sure this is simple but I am just beginning to wet my feet here.
Thanks,
Lee
June 3, 2011 at 3:18 pm
lgoolsby 86333 (6/3/2011)
I have 2 tables:IDXRaw and INVOICES. Both tables have fields INVNUM and MEASURE. IDXRaw will have multiple records with the same INVNUM and MEASURE values. I want to INSERT records from IDXRaw into INVOICES but exclude duplicates. I have a UNIQUE KEY with these 2 fields on the INVOICES table. What would be the correct syntax for this? There are other fields I am inserting but these are the 2 I need to check for duplicates. Not worried about values of other fields.
Let me show you a generic solution...
INSERT INTO TargetTable (column_name1, column_name2, ...)
SELECT column_nameA, column_nameB, ...
FROM SourceTable
WHERE SourceTable.PK NOT IN (SELECT B.PK
FROM TargetTable B
WHERE SourceTable.PK = B.PK)
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 6, 2011 at 8:28 am
lgoolsby 86333 (6/3/2011)
I have 2 tables:IDXRaw and INVOICES. Both tables have fields INVNUM and MEASURE. IDXRaw will have multiple records with the same INVNUM and MEASURE values. I want to INSERT records from IDXRaw into INVOICES but exclude duplicates. I have a UNIQUE KEY with these 2 fields on the INVOICES table. What would be the correct syntax for this? There are other fields I am inserting but these are the 2 I need to check for duplicates. Not worried about values of other fields.
Sure this is simple but I am just beginning to wet my feet here.
Thanks,
Lee
try this, also functional
insert into TableA(columnA, ColumnB ....)
select distinct columnA, columnC ....
from TableC
June 6, 2011 at 8:32 am
rfr.ferrari (6/6/2011)
lgoolsby 86333 (6/3/2011)
I have 2 tables:IDXRaw and INVOICES. Both tables have fields INVNUM and MEASURE. IDXRaw will have multiple records with the same INVNUM and MEASURE values. I want to INSERT records from IDXRaw into INVOICES but exclude duplicates. I have a UNIQUE KEY with these 2 fields on the INVOICES table. What would be the correct syntax for this? There are other fields I am inserting but these are the 2 I need to check for duplicates. Not worried about values of other fields.
Sure this is simple but I am just beginning to wet my feet here.
Thanks,
Lee
try this, also functional
insert into TableA(columnA, ColumnB ....)
select distinct columnA, columnC ....
from TableC
forget what I said! will only work if the table is empty!! i did wrong interpretation!!!!
PaulB is correct!!!
June 6, 2011 at 8:33 am
This is functional but does NOT prevent duplicate data from getting into the destination as Paul's script does.
_______________________________________________________________
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 6, 2011 at 8:49 am
Sean Lange (6/6/2011)
This is functional but does NOT prevent duplicate data from getting into the destination as Paul's script does.
this prevent duplicate, if target table is empty!!!!! I read the question and i answered with another solution that no prevent if the target table to have data!
as it is not possible to delete a post, I said to ignore my solution!
June 6, 2011 at 8:52 am
rfr.ferrari (6/6/2011)
Sean Lange (6/6/2011)
This is functional but does NOT prevent duplicate data from getting into the destination as Paul's script does.this prevent duplicate, if target table is empty!!!!! I read the question and i answered with another solution that no prevent if the target table to have data!
as it is not possible to delete a post, I said to ignore my solution!
Must have posted at the same time I was. 🙂
_______________________________________________________________
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 6, 2011 at 8:56 am
Must have posted at the same time I was. 🙂
forgive me, inconvenient!!!
June 6, 2011 at 9:03 am
Pablo,
Thanks for the response but this does not seem to work. I am doing the following and still getting duplicated recoreds with same INVNUM and MEASURE.
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
FROM IDXRaw A
WHERE A.INVOICE NOT IN (SELECT B.INVNUM
FROM invoices B
WHERE A.INVOICE = B.INVNUM
AND A.MEASURE = B.MEASURE)
This is driving me insane! Thanks for any additional advise you might have.
June 6, 2011 at 9:12 am
Does using a left join work better for this? something like
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
left join invoices B on A.INVOICE = B.INVNUM AND A.MEASURE = B.MEASURE
where A.INVOICE is null
_______________________________________________________________
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 6, 2011 at 9:12 am
So I must not understand how SQL SERVER does inserts in this kind of script. Whe table is empty and I run the previous script to insert 700,000 records from SOURCE table into TARGET table duplicates are added. However, if I try and run the script a second time ZERO records are added because they are all duplicated based on my INVNUM and MEASURE fields. Why does it not see those when doing the INSERT against an empty target table?
Lee
June 6, 2011 at 9:15 am
well it will insert any records that in your select statement. It would only make sense that after they are inserted (on the first run) that they won't be inserted again because those records are now in your destination.
_______________________________________________________________
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 6, 2011 at 9:18 am
except there are duplicates from the 1st run. The SOURCE table has duplicates. So when record 1 is inserted into TARGET table and then record 3 which is a duplicated, shouldn't it be skipped since record 1 is already inserted? Or does SQL SERVER not really place them in TARGET table until the script completes?
June 6, 2011 at 9:37 am
Given your explanation that when you run it the first time is insert duplicates your original script is not doing what you want it to do. In other words, the script you are using to insert your records is not correctly identifying what you consider to be duplicates.
_______________________________________________________________
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 6, 2011 at 12:01 pm
I am guessing it is not seeing the duplicates from this mass INSERT because they are not yet committed? If so, is there a work around to that?
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply