Mike Eghtebas
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,',','),COU NT(DISTINC T ') + ')--,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.
select @sql = '
INSERT INTO ' + @tblTarget + '
SELECT COUNT(DISTINCT ' +REPLACE(@where,',','),COU
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.
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'
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)
it produces:
INSERT INTO YourTable SELECT '<' + CAST(COUNT(DISTINCT a) + COUNT(DISTINCT b) + COUNT(DISTINCT c) as VARCHAR) + '>' FROM YourTable
ASKER
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,',','),COU NT(DISTINC T ') + ')--,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
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,',','),COU
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?
INSERT INTO MyTable ([field1]) VALUES ((SELECT '<' + CAST(COUNT(DISTINCT a) + COUNT(DISTINCT b) + COUNT(DISTINCT c) as VARCHAR) + '>' as field1 FROM MyTable));
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
;
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
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Open in new window