Question

sql中使用变量出错

目的：将数据库名设为变量，方便将来修改。如下T/SQL出错：
declare @dbXFZ varchar(30)
declare@SQLString varchar(500)
select @dbXFZ="PantoSckoolXFZ.dbo."
select @SQLString="insert into XSK_Grade(NJDM,NJMC,RXNF,mark)SELECT NJDM,NJMC,RXNF,mark FROM "+@dbXFZ+"jx_Grade where NJDM not in (select NJDM from XSK_Grade)"
exec sp_executesql @SQLString

若不使用变量，则可以正常执行：
insert into
XSK_Grade
(NJDM,NJMC,RXNF,mark)
SELECT NJDM,NJMC,RXNF,mark
FROM pantoschoolxfz.dbo.jx_Grade
where
NJDM not in (select NJDM from XSK_grade)
注：此SQL代码是用来做数据库定时作业的。请高人指教。

Answer 1

declare @dbXFZ varchar(30),@SQLString varchar(500)
select @dbXFZ='PantoSckoolXFZ.dbo.'
select @SQLString='insert into XSK_Grade(NJDM,NJMC,RXNF,mark)SELECT NJDM,NJMC,RXNF,mark FROM '+@dbXFZ+'jx_Grade where NJDM not in (select NJDM from XSK_Grade)'
exec (@SQLString)

Answer 2

insert into
XSK_Grade
values(NJDM,NJMC,RXNF,mark)
as SELECT NJDM,NJMC,RXNF,mark
FROM pantoschoolxfz.dbo.jx_Grade
where
NJDM not in (select NJDM from XSK_grade)

Answer 3

把你的语句中的双引号全部改成单引号就可以了

Answer 4

select @SQLString="insert into XSK_Grade(NJDM,NJMC,RXNF,mark)SELECT NJDM,NJMC,RXNF,mark FROM "+@dbXFZ+"jx_Grade where NJDM not in (select NJDM from XSK_Grade)"
这句的双引号都改成单引号
另外
declare@SQLString varchar(500)找个长度最好设大一点，防止sql语句放不下