Link to home
Start Free TrialLog in
Avatar of Mike Eghtebas
Mike EghtebasFlag for United States of America

asked on

T-SQL help... short script

As part of a stored procedure I have:

select @sql = '
INSERT INTO ' + @tblTarget + '
SELECT  COUNT(DISTINCT ' +REPLACE(@where,',','),COUNT(DISTINCT ') + ')--,0
FROM ' + @tblTarget
exec(@sql)

This counts the number of entries and puts in the table @tblTarget like:

tblTarget
============
field1
--------
3     <-- The number of entries are 3 counting A, B and C)
A
B
C

but, I want to have it like:
tblTarget
============
field1
--------
<3>
A
B
C

I know that I have to conncatinate '<' and '>' at either end and possibly use CAST to change the count() as varchar(3) for example.

After a couple of tries, I see that I need some help in the syntax.

Question, How can I change the counts to read like <3> instead of just 3.

Thank you.
Avatar of Surendra Nath
Surendra Nath
Flag of India image

try this

select @sql = 'INSERT INTO ' + @tblTarget 
+ 'SELECT  ' + '''' + '<' + '''' + ' + ' + ' CAST(COUNT(DISTINCT ' +REPLACE(@where,',','),COUNT(DISTINCT ') + ') as VARCHAR) ' + ' + ' + '''' + '>' + '''' + ' FROM ' + @tblTarget
exec(@sql)

Open in new window

Avatar of Mike Eghtebas

ASKER

Thank you for the post.

I get an error:

Incorrect syntax near '<'
--------------------

     
    v-----I notice the missing space here
 + ' SELECT  ' + '''' + '<' + '''' + ' + ' + ' CAST(COUNT(DISTINCT '

After adding the required space I ran but got the followng message:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'
SELECT @sql = 'INSERT INTO ' + @tblTarget + ' SELECT  ' + '''<''' + ' + ' + ' CAST( ' + REPLACE(@where, ',' , ') + COUNT(DISTINCT ') + ') as VARCHAR) + ' + '''>''' + ' FROM ' + @tblTarget

SELECT @sql = replace(@sql,'CAST( ) + ','CAST(')

EXEC (@sql)

Open in new window


it produces:

INSERT INTO YourTable SELECT '<' + CAST(COUNT(DISTINCT a) + COUNT(DISTINCT b) + COUNT(DISTINCT c) as VARCHAR) + '>' FROM YourTable
Thank you for the post. When I tried it, I got:

Msg 1035, Level 15, State 10, Line 1
Incorrect syntax near 'CAST', expected 'AS

FYI, before testing, I replaced:

select @sql = '
INSERT INTO ' + @tblTarget + '
SELECT  COUNT(DISTINCT ' +REPLACE(@where,',','),COUNT(DISTINCT ') + ')--,0
FROM ' + @tblTarget
exec(@sql)

with your solution:

SELECT @sql = 'INSERT INTO ' + @tblTarget + ' SELECT  ' + '''<''' + ' + ' + ' CAST( ' + REPLACE(@where, ',' , ') + COUNT(DISTINCT ') + ') as VARCHAR) + ' + '''>''' + ' FROM ' + @tblTarget

SELECT @sql = replace(@sql,'CAST( ) + ','CAST(')
EXEC (@sql)

Mike
try this?
declare @sql nvarchar(max), @tblTarget varchar(30), @where varchar(30)
set @tblTarget = 'MyTable'
set @where = ',a,b,c'

SELECT @sql = 'INSERT INTO ' 
            + @tblTarget 
            + '  ([field1]) VALUES ((SELECT  ' + '''' 
            + '<' + '''' 
            + ' + ' 
            + ' CAST( ' + REPLACE(@where, ',' , ') + COUNT(DISTINCT ') + ') as VARCHAR) + ' 
            + '''>''' 
            + ' as field1 FROM ' 
            + @tblTarget
            + '));'

select @sql = replace(@sql,'CAST( ) + ','CAST(')

exec @sql
;

Open in new window

it produces:

INSERT INTO MyTable ([field1]) VALUES ((SELECT '<' + CAST(COUNT(DISTINCT a) + COUNT(DISTINCT b) + COUNT(DISTINCT c) as VARCHAR) + '>' as field1 FROM MyTable));
if this does not work, plese provide the table definition you are attempting to count, and to insert into
Hi PortletPaul,

Thank you for the time.

Error I got:
Msg 203, Level 16, State 2, Procedure abcd, Line 62
The name 'INSERT INTO MyTable  ([field1]) VALUES ((SELECT  '<' +  CAST(COUNT(DISTINCT a) + COUNT(DISTINCT b) + COUNT(DISTINCT c) as VARCHAR) + '>' as field1 FROM MyTable));' is not a valid identifier.

The entire code see:  http://sqlfiddle.com/#!3/74c24/2

Mike
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you very much. I will post two new questions:

1. Add <tbd> row like:
tbaTarget
========
f1       f2
-------    --------
<3>      <2>
<tbd>   <tbd>
A             P
D             X
E

2. Asking experts somewhat some comments explaning how this stored procedure works. There are some parts is not clear to me.

I will post first one shortly with a link here.

Mike