各位大神帮我看看这个程序问题
小弟刚学SQL数据库方面的知识,今天在调试一个程序,老是出现错误,请各位大神给小弟指出错误
declare @s varchar(max)
set @s = 'create table test2(a varchar(20)'
select @s = @s + ',' + convert(varchar,ID) + ' varchar(100)' from BOOK.DBO.BOOKS
set @s = @s + ')'
exec(@s)
print @s
--借助中间表实现行列转换
declare @name varchar(8000)
declare t_cursor cursor for
select name from book..syscolumns
where id=object_id('BOOK.DBO.BOOKS') and colid > 1 order by colid
open t_cursor
fetch next from t_cursor into @name
while @@fetch_status = 0
begin
exec('select ' + @name + ' as t into test3 from BOOK.DBO.BOOKS')
set @s='insert into test2 select ''' + @name + ''''
select @s = @s + ',''' + rtrim(t) + '''' from test3
exec(@s)
exec('drop table test3')
fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor
--查看行列互换处理结果
--select * from BOOK.DBO.BOOKS
select * from test2
错误:消息 102,级别 15,状态 1,第 1 行
'123' 附近有语法错误。
create table test2(a varchar(20),123 varchar(100),124 varchar(100),125 varchar(100),126 varchar(100),127 varchar(100))
(5 行受影响)
消息 208,级别 16,状态 1,第 1 行
对象名 'test2' 无效。
查了很久不明白怎么回事,求各位大神给小弟指导指导
------解决方案--------------------
SQL code
declare @s varchar(max)
set @s = 'create table test2(a varchar(20)'
select @s = @s + ', ['+ convert(varchar,ID) + '] varchar(100)' from BOOK.DBO.BOOKS
set @s = @s + ')'
exec(@s)
print @s