How to Capture metrics in a SQL script

I am using the code below to capture metrics from a sql script whereby the count of duplicate records is written out to a metrics file.

Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]  
   Values(Current_timestamp, 'Extract', 'Purch Site', 'Number of Purch Site records extracted', 
	(select count(*) from [dbo].[WorkItemPurch] ) ) ;
Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]  
Values(Current_timestamp, 
	'Extract', 
	'Purch Site', 
	'Duplicates found', 
	(SELECT COUNT(*) FROM ( select count(*) from [dbo].[WorkItemPurch]
	                      group by ItemID   
						  having count(*) > 1  ) ))					    

Open in new window


the problem is that I am apparently incorrect on my syntax... as the second to last closing quote has an error showing in the sytax checker,  ' Incorrect syntax near ')' .  Expecting AS, ID or QUOTED ID.

if I change the code to the following I get a new error:

Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]  
   Values(Current_timestamp, 'Extract', 'Purch Site', 'Number of Purch Site records extracted', 
	(select count(*) from [dbo].[WorkItemPurch] ) ) ;
Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]  
Values(Current_timestamp, 
	'Extract', 
	'Purch Site', 
	'Duplicates found', 
	(SELECT COUNT(*) FROM ( select count(*) from [dbo].[WorkItemPurch]
	                      group by ItemID   
						  having count(*) > 1  ) as i ))					    
;	

Open in new window


error : Msg 8155, Level 16, State 2, Line 13 No column name was specified for column 1 of 'i'.

A co-worker used this code fine in AS400 SQL and he wants me to adapt it to sql server. I am trying but not successfully.   I understand that the syntax is not exactly the same between the two  sql flavors but I need help.
Can someone help me to fix this syntax?
metrics-file-layout.docx
LeogalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
You need an alias name for the sub-query:

INSERT  INTO [INDY2].[C205105V].[VD_GPL].[AX__METRICS]
VALUES  
	( 
		CURRENT_TIMESTAMP, 
		'Extract', 
		'Purch Site', 
		'Duplicates found', 
        ( SELECT    COUNT(*)
          FROM      ( SELECT    COUNT(*)
                      FROM      [dbo].[WorkItemPurch]
                      GROUP BY  ItemID
                      HAVING    COUNT(*) > 1
                    ) Q
        ) );		

Open in new window

0
Scott PletcherSenior DBACommented:
Use SELECT to provide the INSERT source rather than the VALUES clause:

Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]  
   Select Current_timestamp, 'Extract', 'Purch Site', 'Number of Purch Site records extracted',
      (select count(*) from [dbo].[WorkItemPurch] )  ;
Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]  
Select Current_timestamp,
      'Extract',
      'Purch Site',
      'Duplicates found',
      (SELECT COUNT(*) FROM ( select count(*) from [dbo].[WorkItemPurch]
                            group by ItemID  
                                      having count(*) > 1  ) as i )
;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anoo S PillaiCommented:
A simple change in second INSERT would resolve the issue -

CHANGE -  
select count(*) from [dbo].[WorkItemPurch]
TO
select count(*) AS CountVal from [dbo].[WorkItemPurch]

Anoo
0
Anoo S PillaiCommented:
What ScottPletcher provided is a solution, I agree 100%.

Out of curiosity, did you try the solution that I provided and if so did you face any issues on that ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.