SET @COLNAME=ISNULL(@COLNAME,'')+'SELECT RTRIM(LTRIM(A.'+@K+'))+RTRIM(LTRIM(B.'+@K+'))
FROM
(SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM '+@TB+' WHERE RTRIM(LTRIM(CAST(F1 AS VARCHAR(MAX))))=''Arrival'')A
INNER JOIN
(SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM '+@TB+' RTRIM(LTRIM(CAST(F1 AS VARCHAR(MAX))))=''Airport'')B
ON
A.ID=B.ID ' 这个语句可以得到一个值下面需要将得到的这个值插入到表TB中 应该怎么做?意思就是INSERT INTO TB(上面的查询语句得到的值) SELECT F1 FROM TB1
BEGIN
DECLARE @J INT,@K NVARCHAR(10),@SQL1 NVARCHAR(MAX),@COLNAME NVARCHAR(MAX)
SET @J=1
WHILE @J<=(select COUNT(name) from syscolumns where id=object_id(''+@TB+''))
--PRINT 1
BEGIN
SET @K='F'+LTRIM(@J)
SET @COLNAME=ISNULL(@COLNAME,'')+'SELECT RTRIM(LTRIM(A.'+@K+'))+RTRIM(LTRIM(B.'+@K+'))
FROM
(SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM '+@TB+' WHERE RTRIM(LTRIM(CAST(F1 AS VARCHAR(MAX))))=''Arrival'')A
INNER JOIN
(SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM '+@TB+' RTRIM(LTRIM(CAST(F1 AS VARCHAR(MAX))))=''Airport'')B
ON
A.ID=B.ID '
SET @SQL1=ISNULL(@SQL1,'')+
'IF EXISTS('+@COLNAME+' WHERE RTRIM(LTRIM(A.'+@K+'))+RTRIM(LTRIM(B.'+@K+')) IN(SELECT NAME FROM syscolumns WHERE [ID]=OBJECT_ID(''TB'')))
INSERT INTO TB('+@COLNAME+') SELECT '+@K+' FROM '+@TB+'
'
SET @J=@J+1
END
END
FROM
(SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM '+@TB+' WHERE RTRIM(LTRIM(CAST(F1 AS VARCHAR(MAX))))=''Arrival'')A
INNER JOIN
(SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM '+@TB+' RTRIM(LTRIM(CAST(F1 AS VARCHAR(MAX))))=''Airport'')B
ON
A.ID=B.ID '
-- 只要你确保@COLNAME这个语句运行结果是有返回一个值INSERT INTO TB(上面的查询语句得到的值)
exec (@COLNAME)
如:
RTRIM(LTRIM(CAST(F1 AS VARCHAR(MAX))))=''Arrival''F1是每个表都有吗?还有
IN(SELECT NAME FROM syscolumns WHERE [ID]=OBJECT_ID(''TB''))
里面的TB也是应该用变量代替
里面的@COLNAME是通过查询语句动态生成的 应该怎么弄?
FROM
(SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM TB1 WHERE RTRIM(LTRIM(CAST(F1 AS VARCHAR(MAX))))=''Arrival'')A
INNER JOIN
(SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM TB1 RTRIM(LTRIM(CAST(F1 AS VARCHAR(MAX))))=''Airport'')B
ON
A.ID=B.ID '现在我要的是把得到的这个@COLNAME填入到INSERT INTO TB()这个括号里面 明白了吗?应该怎么填?谢谢
INSERT INTO TB('+@COLNAME+') SELECT '+@K+' FROM '+@TB+'这样拼的话PRINT出来的值是 INSERT INTO TB(SELECT RTRIM(LTRIM(A.F1))+RTRIM(LTRIM(B.F1))
FROM
(SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM TB1 WHERE RTRIM(LTRIM(CAST(F1 AS VARCHAR(MAX))))='Arrival')A
INNER JOIN
(SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM TB1 RTRIM(LTRIM(CAST(F1 AS VARCHAR(MAX))))='Airport')B
ON
A.ID=B.ID ) SELECT F1 FROM TB1这样就不对了 我想插入的红色这段的得到值,而不是这条语句
你的意思是从外部传进来一个表名,通过系统表syscolumns自动得到列名@COLNAME,
然后 insert into @TB(@COLNAME)
这样对吗?2.
WHERE RTRIM(LTRIM(CAST(F1 AS VARCHAR(MAX))))=''Arrival''
这句这样写,代表所有传入的表都有F1字段,你确定吗?
这样写不行的,
我可以先提供给你,由表名得到字段名的sql语句
declare @s varchar(8000)
set @s=''
select @s=@s+name+','
from syscolumns
where id=object_id('表名') order by colid
print left(@s,len(@s)-1)
set @TB='AppUser' -->这个参数是外部传过来的declare @COLNAME varchar(8000)
set @COLNAME=''
select @COLNAME=@COLNAME+name+','
from syscolumns
where id=object_id(@TB) order by colid
set @COLNAME= left(@COLNAME,len(@COLNAME)-1)declare @SQL1 NVARCHAR(MAX)
set @SQL1='INSERT INTO '+@TB+'('+@COLNAME+') SELECT '+@COLNAME+'
FROM '+@TB+' WHERE CAST(F1 AS VARCHAR)=''Arrival'''EXEC (@SQL1)注明:
里面你用ROW_NUMBER()OVER(ORDER BY GETDATE())产生行号,你产生的行号是用做什么的?
然后如果它与其他字段组合生成内容,再插入到哪个字段,你自己判断
按你的意思,我只能写出其中一部分
DECLARE @J INT,@K NVARCHAR(10),@SQL1 NVARCHAR(MAX),@COLNAME NVARCHAR(MAX)
SET @J=1
WHILE @J<=(select COUNT(name) from syscolumns where id=object_id(''+@TB+''))
--PRINT 1
BEGIN
SET @K='F'+LTRIM(@J)
SET @COLNAME=ISNULL(@COLNAME,'')+'SELECT RTRIM(LTRIM(A.'+@K+'))+RTRIM(LTRIM(B.'+@K+'))
FROM
(SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM '+@TB+' WHERE RTRIM(LTRIM(CAST(F1 AS VARCHAR(MAX))))=''Arrival'')A
INNER JOIN
(SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM '+@TB+' RTRIM(LTRIM(CAST(F1 AS VARCHAR(MAX))))=''Airport'')B
ON
A.ID=B.ID '
SET @SQL1=ISNULL(@SQL1,'')+
'IF EXISTS('+@COLNAME+' WHERE RTRIM(LTRIM(A.'+@K+'))+RTRIM(LTRIM(B.'+@K+')) IN(SELECT NAME FROM syscolumns WHERE [ID]=OBJECT_ID(''TB'')))
INSERT INTO TB('+@COLNAME+') SELECT '+@K+' FROM '+@TB+'
'
SET @J=@J+1
ENDdeclare @col varchar(100)
exec sp_executesql @sql1,'@col varchar(100) output',@col output
declare @sql2 nvarchar(max)
set @sql2='
INSERT INTO TB('+@COLNAME+') SELECT '+@K+' FROM '+@TB
exec sp_executesql @sql2楼主的代码有点难看懂啊,增加了sp_executesql的代码,不知道能不能满中楼主的要求。