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 2008 Forums
 Transact-SQL (2008)
 stored procedure Help

Author  Topic 

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-11-16 : 16:08:39
[code]
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 [/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-16 : 17:07:05
Are there any common attributes between the tables? As in, what columns would you join the talbes on?

It looks like SID for SMaster and Slog and BatchID for Slog and SDetail. But, for SID, I don't see how it relates to more than just the one row. Can you explain?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-16 : 22:50:51
[code]
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

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-11-17 : 12:50:04
Thanks a lot visakh how do i call the call the validation functions instead of case statement in the query




Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-11-17 : 12:52:26
Thanks a lot visakh how do i call the call the validation functions instead of case statement in the query




Go to Top of Page
   

- Advertisement -