October 18, 2010 at 4:12 am
Insert duplicate records into an alternative table
I need an insert script that selects records from TempCustomer table, inserts non- duplicate cellphoneNo into Contact table, and the duplicate ones into ContactAudit. And Finally delete the records inserted into either Contact and/or ContactAudit tables from TempCustomer table.
Thank you in advance.
CREATE TABLE [dbo].[TempCustomer](
[CellphoneNo] [char](15) NULL,
[CreatedOnDt] [datetime] NULL,
[FileName] [char](20) NULL,
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_TempCustomer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
) ON [PRIMARY]
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
) ON [PRIMARY],
CREATE TABLE [dbo].[ContactAudit](
[ContactAuditID] [int] NOT NULL,
[CellphoneNo] [char](15) NULL,
[FileName] [char](20) NULL,
[CreatedOnDt] [datetime] NULL,
CONSTRAINT [PK_ContactAuditID] PRIMARY KEY CLUSTERED
(
[ContactAuditID] ASC
) ON [PRIMARY]
October 18, 2010 at 4:16 am
Please will you provide some sample data in the form of INSERT statements, and show us what you've already tried? You'll find that more people will be willing to help you if it looks as if you made some effort to solve this yourself.
Thanks
John
October 18, 2010 at 4:32 am
Hi,
Sorry about that.
Here is my sample script:
ALTER PROC spAVM_TempCustomer_I
AS
BEGIN
DECLARE @allcount INT,
@CellphoneNo VARCHAR(15),
@Filename VARCHAR(50),
@Count INT,
@ContactID INT
CREATE TABLE #TempCustomer (CellphoneNo varchar(15))
INSERT #TempCustomer
SELECT CellphoneNo FROM TempCustomer
SELECT @allcount = COUNT(*) FROM #TempCustomer
WHILE @allcount > 0
SELECT @CellphoneNo = CellphoneNo
FROM #TempCustomer
SELECT @Count = count(CellphoneNo)
FROM #TempCustomer
WHERE CellphoneNo = @CellphoneNo
IF @Count > 0
INSERT INTO Contact
(CellphoneNo, StatusInd, FileName, CreatedOnDt)
VALUES(@CellphoneNo, '', @FileName, getdate())
DELETE #TempCustomer WHERE CellphoneNo = @CellphoneNo
ELSE
INSERT INTO ContactAudit
(ContactAuditID, CellphoneNo, FileName ,CreatedOnDt)
VALUES('', @CellphoneNo, @FileName, getdate())
DELETE #TempCustomer WHERE CellphoneNo = @CellphoneNo
SELECT @allcount = COUNT(*) FROM #TempCustomer
END--WHILE
DROP TABLE #TempCustomer
October 18, 2010 at 4:45 am
DROP TABLE
dbo.TempCustomer,
dbo.Contact,
dbo.ContactAudit;
GO
CREATE TABLE [dbo].[TempCustomer](
[CellphoneNo] [char](15) NULL,
[CreatedOnDt] [datetime] NULL,
[FileName] [char](20) NULL,
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_TempCustomer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)) 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 [PK_ContactID] PRIMARY KEY CLUSTERED
(
[ContactID] ASC
)) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ContactAudit](
[ContactAuditID] [int] IDENTITY (1,1) NOT NULL,
[CellphoneNo] [char](15) NULL,
[FileName] [char](20) NULL,
[CreatedOnDt] [datetime] NULL,
CONSTRAINT [PK_ContactAuditID] PRIMARY KEY CLUSTERED
(
[ContactAuditID] ASC
)) ON [PRIMARY]
GO
INSERT dbo.TempCustomer
(CellphoneNo, CreatedOnDt, [FileName])
VALUES ('012345678901000', CURRENT_TIMESTAMP, '20 char file name'),
('012345678901001', CURRENT_TIMESTAMP, '20 char file name'),
('012345678901001', CURRENT_TIMESTAMP, '20 char file name'),
('012345678901002', CURRENT_TIMESTAMP, '20 char file name'),
('012345678901003', CURRENT_TIMESTAMP, '20 char file name'),
('012345678901004', CURRENT_TIMESTAMP, '20 char file name'),
('012345678901004', CURRENT_TIMESTAMP, '20 char file name'),
('012345678901004', CURRENT_TIMESTAMP, '20 char file name'),
('012345678901005', CURRENT_TIMESTAMP, '20 char file name'),
('012345678901006', CURRENT_TIMESTAMP, '20 char file name');
INSERT dbo.Contact
(CellphoneNo, StatusInd, [FileName], CreatedOnDt)
SELECT D.CellphoneNo,
StatusInd = 'X',
D.[FileName],
D.CreatedOnDt
FROM (
DELETE Input
OUTPUT deleted.CellphoneNo,
deleted.[FileName],
deleted.CreatedOnDt
FROM (
SELECT *,
group_count = COUNT(*) OVER (PARTITION BY CellphoneNo)
FROM dbo.TempCustomer
) Input
WHERE group_count = 1
) D;
GO
INSERT dbo.ContactAudit
(CellphoneNo, [FileName], CreatedOnDt)
SELECT D.CellphoneNo,
D.[FileName],
D.CreatedOnDt
FROM (
DELETE Input
OUTPUT deleted.CellphoneNo,
deleted.[FileName],
deleted.CreatedOnDt
FROM (
SELECT *,
group_count = COUNT(*) OVER (PARTITION BY CellphoneNo)
FROM dbo.TempCustomer
) Input
WHERE group_count > 1
) D;
GO
SELECT *
FROM dbo.Contact;
GO
SELECT *
FROM dbo.ContactAudit;
GO
Paul
October 18, 2010 at 6:09 am
Thank you very much. The script works as expected.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply