I have a staging table which has unprocessed records per batch..

I need a query which should validate the unprocess records and log the records into another table which is not valid records and also need to log the passed and failed count.


staging Table: smaster

Id      Sid         name         status   validated  BatchID   createddt		  created_by 
---     -------   -----------   -------   --------   -------   ---------------------       -------------
931     100.2.4    Siteipname     New       0         NULL     2012-11-16 14:00:40.800       Test
932	           Siteppname     New       0         NULL     2012-11-16 14:00:40.800       Test 
933     100.2.3    Siteipname     Modify    0         NULL     2012-11-16 14:00:40.800       Test 
934     100.2.5			  Modify    0         NULL     2012-11-16 14:00:40.800       Test


Expected output



Table:smaster

Id      Sid         name         status   validated  BatchID   createddt		   created_by 
---     -------   -----------   -------   --------   -------   ---------------------       -------------
931     100.2.4    Siteipname     New       1         1	       2012-11-16 14:00:40.800       Test
932		   Siteppname     New       1         1        2012-11-16 14:00:40.800       Test 
933     100.2.3    Siteipname     Modify    1         1        2012-11-16 14:00:40.800       Test 
934     100.2.5			  Modify    1         1        2012-11-16 14:00:40.800       Test



Table:SDetail

BatchID   passedcount  failedcount   createddt			created_by 
-------   -----------  -----------  -----------------------   	----------
1        2             2           2012-11-16 14:00:40.800       Test


Table:Slog

ErrorID       SID       Msg									createddt                  created_by    Batchid  
--------      ---      ---------------------------------------					--------------------       ------------   --------
1             0         invalid  Sid is required field						2012-11-16 14:00:40.800     Test           1
2	     100.2.5    Sitename is required and cannot be null 				2012-11-16 14:00:40.800     Test           1 



thanks for your help

DECLARE @BatchID int
SELECT @BatchID= COALESCE(MaxBatchID,0) + 1
FROM (SELECT MAX(BatchID) AS MaxBatchID FROM SDetail)t


UPDATE smaster
SET BatchID = @BatchID
WHERE BatchID IS NULL

INSERT Slog
(SID,
Msg,
createddt,
created_by,
BatchId
)
SELECT COALESCE(SID,0),
CASE WHEN Sid IS NULL THEN 'invalid  Sid is required field'
     WHEN name IS NULL THEN 'Sitename is required and cannot be null'
...
END,
createddt,
created_by,
BatchID
FROM smaster
WHERE BatchID = @BatchID

INSERT SDetail
SELECT s.BatchID,
(SELECT COUNT(1) 
FROM Smaster
WHERE BatchID = s.BatchID) - COUNT(1) ,
COUNT(1),
MIN(createddt),
MIN(created_by)
FROM Slog s
WHERE BatchID = @BatchID