Error Populating Table - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Error Populating Table

Author

Topic

elstond
Starting Member

4 Posts

Posted - 2008-05-14 : 05:38:59

Hi all,

I have a table that looks something like this: -

county fname sport
------ ----- -----
surrey tara squash
surrey tara hockey
surrey tara tennis
kent tom tennis
kent tom football
kent tom rugby

I want to read through the sport table and create a distinct list of sports which can be used to create a new table that would look like: -

County fname squash hockey tennis football rugby
------ ----- ------ ------ ------ -------- -----
surrey tara YES YES YES
kent tom YES YES YES

I am using the following code: -
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = 'create table ey_report_temp (county nvarchar(100),fname nvarchar(100), ' +
STUFF((SELECT DISTINCT ',[' + sport + '] nvarchar(100) '
FROM ey_report FOR XML PATH('')), 1, 1, '') + ')'

SELECT @sql
exec sp_executesql @sql

SELECT * from ey_report_temp

--------------------------------------------

DECLARE @county nvarchar(max)
DECLARE @fname nvarchar(max)
DECLARE @sport nvarchar(max)

DECLARE merge_cursor CURSOR FAST_FORWARD
FOR SELECT county, fname, sport from ey_report

OPEN merge_cursor
FETCH NEXT FROM merge_cursor INTO @county, @fname, @sport
WHILE @@FETCH_STATUS = 0
BEGIN

select @sql = N'
update ey_report_temp
set ' + @sport + ' = ''YES''
where county = ''' + @county + '''
and fname = ''' + @fname + ''''
print @sql
exec sp_executesql @sql
if @@ROWCOUNT = 0

begin
select @sql = N'
insert into ey_report_temp (
county, fname, ' + @sport + '
) values ( ' + @county + ', ' + @fname + ', ' + @sport + ')'

exec sp_executesql @sql

end
FETCH NEXT FROM merge_cursor INTO @county, @fname, @sport
END
CLOSE merge_cursor
DEALLOCATE merge_cursor
select * from ey_report_temp

drop table ey_report_temp

This creates the new table fine however, when it trys to poulate I get the following EM, can anybody help? thanks in anticipation

(1 row(s) affected)

(0 row(s) affected)

update ey_report_temp
set squash = 'YES'
where county = 'surrey'
and fname = 'tara'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set hockey = 'YES'
where county = 'surrey'
and fname = 'tara'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set tennis = 'YES'
where county = 'surrey'
and fname = 'tara'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set tennis = 'YES'
where county = 'kent'
and fname = 'tom'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set football = 'YES'
where county = 'kent'
and fname = 'tom'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set rugby = 'YES'
where county = 'kent'
and fname = 'tom'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

(0 row(s) affected)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 05:43:33

Seems to be a problem with your ANSI settings.
The value 'KENT' seems to be treated as a column reference rather than a string value.

E 12°55'05.25"
N 56°04'39.16"

elstond
Starting Member

4 Posts

Posted - 2008-05-14 : 05:47:18

Thanks for the quick response any idea where I could look to investigate further?

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 05:53:09

I think the ANSI setting is named QUOTED_IDENITFIER

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 05:54:36

[code]declare @sport table
(
county varchar(30),
fname varchar(30),
sport varchar(30)
)
------ ----- -----

insert into @sport
select 'surrey', 'tara', 'squash'
union all
select 'surrey', 'tara', 'hockey'
union all
select 'surrey', 'tara', 'tennis'
union all
select 'kent', 'tom', 'tennis'
union all
select 'kent', 'tom', 'football'
union all
select 'kent', 'tom', 'rugby'

select t.county,t.fname,
case when t.squash = 1 then 'yes' else 'no' end as squash,
case when t.hockey = 1 then 'yes' else 'no' end as hockey,
case when t.tennis = 1 then 'yes' else 'no' end as tennis,
case when t.football = 1 then 'yes' else 'no' end as football,
case when t.rugby = 1 then 'yes' else 'no' end as rugby
from
(
select * from
(select * from @sport) m
pivot (count(sport) for sport in ([squash],[hockey],[tennis],[football],[rugby]))p
)t

output
-----------------------------------------------------------
county fname squash hockey tennis football rugby
------------------------------ ------------------------------ ------ ------ ------ -------- -----
surrey tara yes yes yes no no
kent tom no no yes yes yes[/code]

elstond
Starting Member

4 Posts

Posted - 2008-05-14 : 06:04:37

Thanks for the reposnse visakh16 however, I am expecting the first table to grow therefor I was looking for a dynamic solution

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 06:23:57

[code]create table #sport
(
county varchar(30),
fname varchar(30),
sport varchar(30)
)

insert #sport
select 'surrey', 'tara', 'squash' union all
select 'surrey', 'tara', 'hockey' union all
select 'surrey', 'tara', 'tennis' union all
select 'kent', 'tom', 'tennis' union all
select 'kent', 'tom', 'football' union all
select 'kent', 'tom', 'rugby'

declare @sql varchar(max)

SET @SQL = '
select p.*
from (
select county,
fname,
sport,
''yes'' as peso
from #sport
) AS m
pivot (
max(peso)
for m.sport in (' + STUFF((SELECT DISTINCT ', ' + QUOTENAME(Sport) FROM #Sport ORDER BY ', ' + QUOTENAME(Sport) FOR XML PATH('')), 1, 2, '') + ')
) AS p
'
exec (@sql)

drop table #sport[/code]

E 12°55'05.25"
N 56°04'39.16"

elstond
Starting Member

4 Posts

Posted - 2008-05-14 : 06:42:11

That worked a treat many thanks for your quick response

Kind Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources