October 22, 2010 at 2:02 am
insert into Contact if CellphoneNo is not duplicated in TempCustomer,
If CellphoneNo is duplicated, insert into Contact Audit.
But if CellphoneNo exists in Contact, it should be seen as a duplicate, hence inserted into ContactAudit table.
USE [MobiAVM]
GO
CREATE TABLE [dbo].[TempCustomer](
[CellphoneNo] [char](15) NULL,
[CreatedOnDt] [datetime] NULL CONSTRAINT [DF_TempCustomer_CreatedOnDt] DEFAULT (getdate()),
[FileName] [char](20) NULL,
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_TempCustomer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Contact](
[ContactID] [int] IDENTITY(1,1) NOT NULL,
[CellphoneNo] [char](15) NOT NULL,
[StatusInd] [char](1) NULL,
[FileName] [char](20) NULL,
[CREATEdOnDt] [datetime] NULL CONSTRAINT [DF_Contact_Date] DEFAULT (getdate()),
CONSTRAINT [PK_ContactID] PRIMARY KEY CLUSTERED
(
[ContactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[CellphoneNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ContactAudit](
[ContactAuditID] [int] NOT NULL,
[ContactID] [int] NOT NULL,
[CellphoneNo] [char](15) NULL,
[FileName] [char](20) NULL,
[CreatedOnDt] [datetime] NULL CONSTRAINT [DF_ContactAudit_Date] DEFAULT (getdate()),
CONSTRAINT [PK_ContactAuditID] PRIMARY KEY CLUSTERED
(
[ContactAuditID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ContactAudit] WITH CHECK ADD CONSTRAINT [FK_ContactAudit_Contact] FOREIGN KEY([ContactID])
REFERENCES [dbo].[Contact] ([ContactID])
GO
ALTER TABLE [dbo].[ContactAudit] CHECK CONSTRAINT [FK_ContactAudit_Contact]
--SP
ALTER PROC spAVM_TempCustomer_I4
--@FileName char(15)
AS
BEGIN
INSERT dbo.Contact (CellphoneNo, CreatedOnDt, FileName, StatusInd)
SELECT c.CellphoneNo,getdate(), FileName , 'X'
FROM TempCustomer as tc
INNER JOIN (SELECT CellphoneNo
FROM TempCustomer
GROUP BY CellphoneNo
HAVING COUNT(*) = 1) AS c
ON c.CellphoneNo = tc.CellphoneNo
AND FileName IS NOT NULL
INSERT dbo.ContactAudit(CellphoneNo, ContactID, CreatedOnDt, FileName)
SELECT c.CellphoneNo,ct.ContactID, getdate(), tc.FileName
FROM TempCustomer as tc
INNER JOIN (SELECT CellphoneNo
FROM TempCustomer
GROUP BY CellphoneNo
HAVING COUNT(*) > 1) AS c
ON c.CellphoneNo = tc.CellphoneNo
--AND tc.FileName IS NOT NULL
--LEFT OUTER JOIN Contact ct
--ON ct.CellphoneNo = tc.CellphoneNo
END
October 22, 2010 at 3:50 am
Is there a question here?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 25, 2010 at 10:07 am
I don't even understand the poll.
October 27, 2010 at 2:48 am
Hi,
I hope it makes sense now: i need an sp that does the following:
Condition 1: If an entry is duplicated in TempCustomer, insert into ContactAudit table.
Condition 2: If not, insert into Contact
Condition 3: If an entry is not duplicated in TempCustomer, but already exists in Contact, also insert into ContactAudit table. The record should be linked to the existing record in Contact table
NB. There is a primary/ foreign key relationship between Contact and ContactAudit table.
At the moment the script fails with message: “Table Cannot insert the value NULL into column 'ContactAuditID', table 'dbo.ContactAudit'; column does not allow nulls. INSERT fails.”
Thank you,
October 27, 2010 at 3:46 am
Maina-456685 (10/27/2010)
Hi,I hope it makes sense now: i need an sp that does the following:
Condition 1: If an entry is duplicated in TempCustomer, insert into ContactAudit table.
An entry from what? Duplicated on what columns? Insert how many rows into ContactAudit table, all dupes of the row or just one?
Condition 2: If not, insert into Contact
Condition 3: If an entry is not duplicated in TempCustomer, but already exists in Contact, also insert into ContactAudit table. The record should be linked to the existing record in Contact table
NB. There is a primary/ foreign key relationship between Contact and ContactAudit table.
At the moment the script fails with message: “Table Cannot insert the value NULL into column 'ContactAuditID', table 'dbo.ContactAudit'; column does not allow nulls. INSERT fails.”
Thank you,
Can this requirement be rephrased like so:
Using table TempCustomer as source table and column CellphoneNo as reference or match column, check the source table for dupes and also for rows which exist in the Contact table.
Insert one row of each dupeset into table ContactAudit
Insert rows which match on CellphoneNo to the Contact table into table ContactAudit, capturing ContactID
Other rows from the source should be inserted into the Contact table
Your current query is quite close, it fails because column ContactAuditID hasn't been defined as an identity column. Since ContactID is an identity column, it would be consistent to make ContactAuditID an identity column also. It will also fail on duplicated rows from TempCustomer because the column ContactID in table ContactAudit is specified NOT NULL. Duplicated rows may not have a match in the Contact table.
ROW_NUMBER() OVER(PARTITION BY CellphoneNo) will almost certainly be more efficient than joining to a (derived) aggregated table.
Try again. If you get stuck, post back.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 27, 2010 at 4:26 am
Maina-456685 (10/27/2010)
Condition 1: If an entry is duplicated in TempCustomer, insert into ContactAudit table.....
Condition 3: If an entry is not duplicated in TempCustomer, but already exists in Contact, also insert into ContactAudit table. The record should be linked to the existing record in Contact table
Define not duplicated but already exists? What already exists? How can it exist but not be duplicated?
October 27, 2010 at 4:37 am
A few minor nitpicks that might not matter in your current environment but could cause problems for you later. (FYI: Grain of salt moment. These just happen to be two of my pet peeves.)
Don't use Keywords for column names (FileName) and don't do a Count(*). Count(cellphoneno) should work just fine and won't cause performance issues or fail if something drastically changes.
The biggest issue I see with your proc is that you're trying to match stuff on CellPhoneNo and that value in TempCustomer is allowed to be NULL. In a situation like this, your source column should not have NULLable capability. You're shooting yourself in the foot before you even start.
I'm not sure I get Chris's comment of "It will also fail on duplicated rows from TempCustomer because the column ContactID in table ContactAudit is specified NOT NULL." I hope he'll explain why a NOT NULL column would cause issues with duplicate rows being entered when that column isn't even part of a clustered index primary key. So far as I know, this shouldn't be an issue.
EDIT: Now that I think about it, he may be referring to the fact that ContractAuditID is set NOT NULL but is not an identity, does not have a default, and is not populated with the Proc. All of which means the INSERT will basically fail because you can't insert a record with a NULL value for a NOT NULL column.
October 27, 2010 at 5:09 am
Brandie Tarvin (10/27/2010)
I'm not sure I get Chris's comment of "It will also fail on duplicated rows from TempCustomer because the column ContactID in table ContactAudit is specified NOT NULL." I hope he'll explain why a NOT NULL column would cause issues with duplicate rows being entered when that column isn't even part of a clustered index primary key. So far as I know, this shouldn't be an issue.
Hi Brandie
It's because the tempcustomer table doesn't contain the column ContactID, and dupes in tempcustomer may not match a row in ContactID on CellPhoneNo.
Attempting to insert such a row with no value for ContactID into ContactAudit would fail.
It's a rather vague spec and I'm probably way out - this is only one of several feasible interpretations!
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 27, 2010 at 5:21 am
Ah. I did not catch that fact. Chris, you are correct. So even if the ContractAuditID problem is corrected, the code will still fail.
October 27, 2010 at 5:24 am
I just noticed another problem. At no time in this scenario, Maina-456685, are you actually checking dbo.Contract for duplicates. You only ever check your staging table, dbo.TempCustomer for duplicates.
So what happens when TempCustomer is truncated (or deleted) and new data is loaded that contains a "new" record for Contract?
Because you're not checking Contract, you'll still get duplicates loaded into Contract despite your best intentions.
October 27, 2010 at 7:36 am
Hi,
Thank you very much.
Your advices were helpful in resolving the primary/ foreign key problem
To answer some of the questions,
The TempCustomer table gets truncated every day, and new data loaded. Some entries that were loaded previously might be loaded again.
Condition 1: If an entry is duplicated in TempCustomer, insert into ContactAudit table.
An entry from what? Duplicated on what columns? Insert how many rows into ContactAudit table, all dupes of the row or just one?
•An entry in TempCustomer( source table).
•Duplicated on CellphoneNo column,
•Just one duplicate to be inserted into ContactAudit table
I have changed the column ContactID in table ContactAudit to NULL and ContactAuditID to identity column.
Now I am only battling with condition:
If row from TempCustomer (source) already exists in Contact table, it should be inserted into the ContactAudit table.
I have difficulties with checking duplicates for multiple rows.
I do understand how to check duplicates for 1 record. E.g
IF NOT EXISTS (SELECT * FROM ContactAudit where coloun1 = ‘value’ ) ---????
Script below:
CREATE TABLE [dbo].[TempCustomer](
[CellphoneNo] [char](15) NOT NULL,
[CreatedOnDt] [datetime] NULL,
[FileName] [char](20) NULL,
[CustomerID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
--2
CREATE TABLE [dbo].[Contact](
[ContactID] [int] IDENTITY(1,1) NOT NULL,
[CellphoneNo] [char](15) NOT NULL,
[StatusInd] [char](1) NULL,
[FileName] [char](20) NULL,
[CREATEdOnDt] [datetime] NULL,
CONSTRAINT [PK_ContactID] PRIMARY KEY CLUSTERED
(
[ContactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[CellphoneNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contact] ADD CONSTRAINT [DF_Contact_Date] DEFAULT (getdate()) FOR [CREATEdOnDt]
GO
--3
CREATE TABLE [dbo].[ContactAudit](
[ContactAuditID] [int] IDENTITY(1,1) NOT NULL,
[ContactID] [int] NULL,
[CellphoneNo] [char](15) NULL,
[FileName] [char](20) NULL,
[CreatedOnDt] [datetime] NULL,
CONSTRAINT [PK_ContactAuditID] PRIMARY KEY CLUSTERED
(
[ContactAuditID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ContactAudit] WITH CHECK ADD CONSTRAINT [FK_ContactAudit_Contact] FOREIGN KEY([ContactID])
REFERENCES [dbo].[Contact] ([ContactID])
GO
ALTER TABLE [dbo].[ContactAudit] CHECK CONSTRAINT [FK_ContactAudit_Contact]
GO
ALTER TABLE [dbo].[ContactAudit] ADD CONSTRAINT [DF_ContactAudit_Date] DEFAULT (getdate()) FOR [CreatedOnDt]
GO
ALTER PROC spAVM_TempCustomer
AS
BEGIN
IF NOT EXISTS (SELECT * FROM Contact ) ---????
INSERT dbo.Contact (CellphoneNo, StatusInd, FileName, CreatedOnDt)
SELECT tc.CellphoneNo,'','Filename',GETDATE()
FROM TempCustomer as tc
LEFT OUTER JOIN Contact c
ON tc.CellphoneNo = c.CellphoneNo
WHERE c.CellphoneNo IS NULL
GROUP BY tc.CellphoneNo
HAVING COUNT(tc.CellphoneNo)=1
IF NOT EXISTS (SELECT * FROM ContactAudit) ---????
INSERT dbo.ContactAudit (CellphoneNo,FileName ,CreatedOnDt)
SELECT tc.CellphoneNo, 'Filename',GETDATE()
FROM TempCustomer as tc
LEFT OUTER JOIN ContactAudit ca
ON tc.CellphoneNo = ca.CellphoneNo
WHERE ca.CellphoneNo IS NULL
GROUP BY tc.CellphoneNo
HAVING COUNT(tc.CellphoneNo) > 1
IF NOT EXISTS (SELECT * FROM ContactAudit) ---????
INSERT INTO dbo.ContactAudit(ContactID ,CellphoneNo,FileName,CreatedOnDt)
SELECT c.ContactID ,tc.CellphoneNo, 'Filename',GETDATE()
FROM TempCustomer as tc
INNER JOIN Contact c
ON tc.CellphoneNo = c.CellphoneNo
LEFT OUTER JOIN ContactAudit ca
ON tc.CellphoneNo = ca.CellphoneNo
WHERE ca.CellphoneNo IS NULL
GROUP BY c.ContactID,tc.CellphoneNo
HAVING COUNT(tc.CellphoneNo) = 1
END
October 27, 2010 at 7:42 am
I'm fairly certain you don't need the GROUP BY and HAVING on the Contract insert. Test it to verify, but everything after the c.CellphoneNo IS NULL should be able to be deleted.
Add a left outer join to Contract on the ContractAudit insert (the first one). WHERE ca.CellphoneNo IS NULL and c.CellphoneNo IS NOT NULL, then drop the GROUP BY and HAVING statements on that. This should take care of only entering one duplicate on ContractAudit.
October 27, 2010 at 7:44 am
Maina-456685, Could you provide sample data so Chris and I can verify solutions before we post them?
October 27, 2010 at 8:12 am
Brandie Tarvin (10/27/2010)
Maina-456685, Could you provide sample data so Chris and I can verify solutions before we post them?
Thanks Brandie. Unfortunately my eval version of 2k8 has just expired so I've no server to test against. Something like this should do the trick though:
;WITH FlaggedData AS (
SELECT
c.ContactID,
tc.CellphoneNo,
FileName = 'Filename',
CreatedOnDt = GETDATE(),
rn = ROW_NUMBER() OVER (PARTITION BY tc.CellphoneNo)
FROM TempCustomer as tc
LEFT JOIN Contact c
ON tc.CellphoneNo = c.CellphoneNo)
INSERT INTO dbo.ContactAudit (ContactID, CellphoneNo, FileName, CreatedOnDt)
SELECT ContactID, CellphoneNo, FileName, CreatedOnDt
FROM FlaggedData
WHERE rn = 2 OR ContactID IS NOT NULL
;WITH FlaggedData AS (
SELECT
c.ContactID,
tc.CellphoneNo,
FileName = 'Filename',
CreatedOnDt = GETDATE(),
Dupes = COUNT(*) OVER (PARTITION BY tc.CellphoneNo)
FROM TempCustomer as tc
LEFT JOIN Contact c
ON tc.CellphoneNo = c.CellphoneNo)
INSERT INTO dbo.Contact (ContactID, CellphoneNo, FileName, CreatedOnDt)
SELECT ContactID, CellphoneNo, FileName, CreatedOnDt
FROM FlaggedData
WHERE Dupes = 1 AND ContactID IS NULL
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 27, 2010 at 8:52 am
Hi,
Pleasea find the sample data below:
0763111334 2010-10-27 14:31:18.860Filename 2
0763789334 2010-10-27 14:31:18.860Filename 3
73789376 2010-10-27 14:31:18.860Filename 4
82789334 2010-10-27 14:31:18.860Filename 9
0733199122 2010-10-27 14:31:18.860Filename 15
0733199122 2010-10-27 14:31:18.860Filename 16
0733199122 2010-10-27 14:31:18.860Filename 17
0733199122 2010-10-27 14:31:18.860Filename 18
0763199122 2010-10-27 14:31:18.860Filename 19
0743199122 2010-10-27 14:31:18.860Filename 20
0733199122 2010-10-27 14:31:18.860Filename 21
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply