Reinsert Records into Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Reinsert Records into Table

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-11-19 : 21:38:07

This is the Permenent table (tblPermTask) where all Tasks are kept until closed out. I have a Temp table called (tblTask) that is the
same except for 2 fields the PermId and BitOVerride are only in PermTask.

PermID TaskID AssingedID FilterID PositionId TaskName dtFound dtCompleted BitOVerride
284 976 128 32 8230 MOS MISMATCH 2009-11-19 13:22:56.320 2009-11-19 14:38:12.000 NULL
285 977 100 33 8229 GR MIS-MATCH 2009-11-19 13:22:56.320 2009-11-19 14:38:12.000 1
286 978 100 33 8230 GR MIS-MATCH 2009-11-19 13:22:56.323 NULL NULL
288 980 100 33 8232 GR MIS-MATCH 2009-11-19 13:22:56.323 NULL NULL
289 981 115 34 8246 SECURITY 2009-11-19 13:22:56.327 2009-11-19 14:38:12.000 NULL
290 982 103 38 8231 DATE PRIOR TO M + 45 2009-11-19 13:22:56.327 NULL NULL
297 989 121 43 8243 CURRENTLY DEP 2009-11-19 13:22:56.337 NULL NULL
298 990 109 46 8228 FIX MEDICAL ISSUE 2009-11-19 13:22:56.340 NULL NULL
306 998 125 48 8241 ATTEND INITIAL SRP 2009-11-19 13:22:56.343 NULL NULL
307 999 125 48 8246 ATTEND INITIAL SRP 2009-11-19 13:22:56.347 NULL NULL
308 1000 126 49 8242 ASI MISMATCH 2009-11-19 13:22:56.347 NULL NULL
309 1001 113 51 8232 COMMISS PROGRAMS 2009-11-19 13:22:56.350 2009-11-19 14:38:12.000 1
310 1003 128 32 8230 MOS MISMATCH 2009-11-19 14:38:12.753 NULL NULL

So what happens is the a Stored Procedure runs that finds problems and puts them in the temp table. from here it compares the 2 tables and decides
which ones are new and puts them into the permenent table. But i need to go one step further and add any tasks that have been closed but have shown up agian.
You can see in table that the 1st and last records are teh same records but with different TaskIds, dtfound. As long as teh record is not closed
it does not reinsert but if the record is completed and the bitOverRide is not turned on I need the record to reinsert agian. I cut a bunch of fields out
of teh table above because of personnel info, that is reason query is much bigger than what information you see.

The code below reinserts like I want it too but it keeps reinserting day after day, because the filters will find anything that is wrong everyday. But if its
not closed I don't wnat it to reinsert and it does. How can I keep this from happening.

INSERT INTO tblPermTask (
intTaskID, intAssignedId,
intFilterId, intPositionId,
strTaskName, dtFound,
dtCompleted, strStatus,
dtStatus, strSSN,
strName, strPositionData,
strSoldierData, strNotes,
strProponent, dtNotification,
dtCompleteTask)

SELECT t.intTaskID, t.intAssignedId,
t.intFilterId, t.intPositionId,
t.strTaskName, t.dtFound,
t.dtCompleted, t.strStatus,
t.dtStatus, t.strSSN,
t.strName, t.strPositionData,
t.strSoldierData, t.strNotes,
t.strProponent, t.dtNotification,
t.dtCompleteTask
FROM tblTask as t INNER JOIN
tblPermTask as p on p.intassignedId = t.intAssignedId AND
p.intFilterID = t.intFilterId AND
p.intPositionId = t.intPositionId and
p.strSSN = t.strSSN
WHERE p.dtCompleted is not null and (p.bitOverRide is NULL or p.bitOverride = 0)
EXCEPT
SELECT p.intTaskID, p.intAssignedId,
p.intFilterId, p.intPositionId,
p.strTaskName, p.dtFound,
p.dtCompleted, p.strStatus,
p.dtStatus, p.strSSN,
p.strName, p.strPositionData,
p.strSoldierData, p.strNotes,
p.strProponent, p.dtNotification,
p.dtCompleteTask
FROM tblPermTask p
Where p.dtCompleted is not null and (p.bitOverRide is NULL or p.bitOverride = 0)

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-19 : 21:59:40

Hi

Give me 1 sentence about what you want to do

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-11-20 : 08:14:18

IF a record is found agian and the dtCompleted is Not Null I want it to reisnert agian, but if bitOVerride is on I don't want it to reinsert agian.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources