sql2000的环境下,
一般插入数据都是字段名对应变量,例如
INSERT INTO Tc_Pomain_shed_bydate (ID,dateshed1)
VALUES (@ID,@dateshed1)现在我碰到的问题是假如一组字段名是动态的,怎样插入数据呢?
示意如下:
INSERT INTO Tc_Pomain_shed_bydate (ID,@listname1,@listname2,@listname3,......)
VALUES (@ID,@dateshed1,@dateshed1,@dateshed1,......)当然上面的语句在sql2000里是执行不成功的,如何解决这样的问题呢?
sql动态字段动态字段名
一般插入数据都是字段名对应变量,例如
INSERT INTO Tc_Pomain_shed_bydate (ID,dateshed1)
VALUES (@ID,@dateshed1)现在我碰到的问题是假如一组字段名是动态的,怎样插入数据呢?
示意如下:
INSERT INTO Tc_Pomain_shed_bydate (ID,@listname1,@listname2,@listname3,......)
VALUES (@ID,@dateshed1,@dateshed1,@dateshed1,......)当然上面的语句在sql2000里是执行不成功的,如何解决这样的问题呢?
sql动态字段动态字段名
'INSERT INTO Tc_Pomain_shed_bydate (ID,'+@listname1+','+@listname2+','+@listname3+
') VALUES ('+
RTRIM(@ID)+','+RTRIM(@dateshed1)+','+RTRIM(@dateshed2)+','+RTRIM(@dateshed3)+')'EXEC(@sql)
declare @sql nvarchar(4000)
set @sql=''
set @sql='INSERT INTO Tc_Pomain_shed_bydate (ID,'+@listname1+','+@listname2+','+@listname3',......)
VALUES ('+@ID+','+@dateshed1+','+@dateshed1+','+@dateshed1+','+......)''
print @sql
exec @sql
注意如果是字符串类型,改加单引号
set @sql=''
SET @row = 2
SET @listname = 'dateshed' + RTRIM(CAST(@row AS VARCHAR(2)))
SET @ID = 2
SET @dateshed2 = 2.2set @sql='INSERT INTO Tc_Pomain_shed_bydate (ID,'+@listname+')
VALUES ('+@ID+','+@dateshed2+')'
print @sql
exec @sql报错:
将 varchar 值 'INSERT INTO Tc_Pomain_shed_bydate (ID,dateshed2)
VALUES (' 转换为数据类型为 int 的列时发生语法错误。明知道应该是引号的位置错了,或者使用的引号错了,真是非常抱歉,还有请教你。同时也对你深表感谢!
dateshed2,decimal型;
set @sql=''
SET @row = 2
SET @listname = 'dateshed' + RTRIM(CAST(@row AS VARCHAR(2)))
SET @ID = 2
SET @dateshed2 = 2.2set @sql='INSERT INTO Tc_Pomain_shed_bydate (ID,'+@listname+')
VALUES ('+cast(@ID as varchar)+','+cast(@dateshed2 as varchar)+')'
print @sql
exec @sql
DECLARE @listname1 NVARCHAR(255), @listname2 NVARCHAR(255), @listname3 NVARCHAR(255)
DECLARE @ID INT, @dateshed1 VARCHAR(100)DECLARE @sql NVARCHAR(MAX)
SET @sql=N'
INSERT INTO Tc_Pomain_shed_bydate (ID,{listname1},{listname2},{listname3})
VALUES (@ID,@dateshed1,@dateshed1,@dateshed1)
'
SET @sql = REPLACE(@sql, '{listname1}', @listname1)
SET @sql = REPLACE(@sql, '{listname2}', @listname2)
SET @sql = REPLACE(@sql, '{listname3}', @listname3)
--print @sql
EXEC sp_executesql @sql, N'@ID int,@dateshed1 varchar(100)', @dateshed1
set @sql=''
SET @row = 2
SET @listname = 'dateshed' + RTRIM(CAST(@row AS VARCHAR(2)))
SET @ID = 2
SET @dateshed2 = 2.2
set @sql='INSERT INTO Tc_Pomain_shed_bydate (ID,'+@listname+')
VALUES ('+cast(@ID as varchar)+','+cast(@dateshed2 as varchar)+')'
print @sql
exec (@sql);贴一下我最终测试的结果,你给的语句中在我的环境中测试还少了一个(),但还是非常感谢你!