April 26, 2010 at 11:53 am
What I have is two database's, one is the live the other is archive. I need to pull any records out of the calllog table in the live, HEAT_test, db and insert them into the Heat_archive_test DB if the callID doesnt all ready exist in the heat_archive_test.calllog table based on callstatus = closed and closedate is over 2 years old. This is what I got and of course get the Subquery returned more than 1 value error. Any help or points in the right direction would be appreciated greatly.
Insert into heat_archive_test.dbo.calllog
Select *
FROM heat_test.dbo.calllog
WHERE not exists (select * from heat_archive_test.dbo.Calllog
where heat_archive_test.dbo.calllog.callID = heat_test.dbo.calllog.callid heat_archive_test.dbo.calllog.callstatus = 'closed' and heat_archive_test.dbo.calllog.closeddate < DATEADD(day, -730, CURRENT_TIMESTAMP))
April 27, 2010 at 4:20 am
It seemed to be mostly correct, apart from you missed out an AND operator on your DML statement and it required a little rejig
IF OBJECT_ID(N'Tempdb..#CallLog', N'U') IS NOT NULL
DROP TABLE #CallLog
GO
IF OBJECT_ID(N'Tempdb..#CallLog_Archive', N'U') IS NOT NULL
DROP TABLE #CallLog_Archive
GO
CREATE TABLE #CallLog
(
CallID INT,
CallStatus CHAR(6),
ClosedDate DATETIME
);
INSERT INTO #CallLog (CallID, CallStatus, ClosedDate)
SELECT 1, 'Closed', '20050101' UNION ALL
SELECT 2, 'Open', NULL UNION ALL
SELECT 3, 'Open', NULL UNION ALL
SELECT 4, 'Closed', '20090101' UNION ALL
SELECT 5, 'Closed', '20070218' UNION ALL
SELECT 6, 'Closed', '20080427';
CREATE TABLE #CallLog_Archive
(
CallID INT,
CallStatus CHAR(6),
ClosedDate DATETIME
);
INSERT INTO #CallLog_Archive (CallID, CallStatus, ClosedDate)
SELECT 1, 'Closed', '20050101';
--Quick Test Data Check
SELECT * FROM #CallLog;
SELECT * FROM #CallLog_Archive;
--This should only Insert IDs 5 & 6 as they satisfy the criteria
INSERT INTO #CallLog_Archive (CallID, CallStatus, ClosedDate)
SELECT
CL.CallID,
CL.CallStatus,
CL.ClosedDate
FROM #CallLog CL
WHERE
NOT EXISTS (
SELECT 1 FROM #CallLog_Archive CLA
WHERE CLA.CallID = CL.Callid
)
AND CL.Callstatus = 'Closed'
AND CL.Closeddate < DATEADD(YEAR, -2, CURRENT_TIMESTAMP);
SELECT * FROM #CallLog_Archive;
Pre Insert
CallID CallStatus ClosedDate
1 Closed 2005-01-01 00:00:00.000
Post Insert
CallID CallStatus ClosedDate
1 Closed 2005-01-01 00:00:00.000
5 Closed 2007-02-18 00:00:00.000
6 Closed 2008-04-27 00:00:00.000
Another thing of note was:
SELECT
DATEADD(DAY, -1460, CURRENT_TIMESTAMP) AS MDay,
DATEADD(YEAR, -4, CURRENT_TIMESTAMP) AS MYear
Using a fixed number of days may cause some issues with Leap years, please correct me if I'm wrong!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply