变通一下 如果要多次用到类似这样的SQL语句,字段特别多,只有个别字段不用显示,写起来够麻烦,那么你就声明一个全局字符串变量,使用的时候用replace处理一下.dim strFld as string strFld="a,b,c,d,e,f,g....."dim sql string sql="select "& replace(strFld,",c",vbnullstring) &" from tb where ......"
用动态SQL(Dynamic SQL) Declare @Col_table1 nvarchar(4000) ,@Col_table2 nvarchar(4000) ,@DySQL nvarchar(4000) SELECT @Col_table1='',@Col_table2='' SELECT @Col_table1=@Col_table1+COLUMN_NAME+',' From INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='table1' AND COLUMNPROPERTY(Object_ID('table1'),COLUMN_NAME,'IsIdentity')=0 ORDER BY ORDINAL_POSITION SELECT @Col_table2=@Col_table2+COLUMN_NAME+',' From INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='table2' AND COLUMNPROPERTY(Object_ID('table2'),COLUMN_NAME,'IsIdentity')=0 ORDER BY ORDINAL_POSITION SELECT @Col_table1=LEFT(@Col_table1,LEN(@Col_Table1)-1),@Col_Table2=LEFT(@Col_Table2,LEN(@Col_Table2)-1) SET @DySQL= ' INSERT INTO table1 (' +@Col_Table1+') SELECT '+@Col_Table2 +' From table2 '
如果要多次用到类似这样的SQL语句,字段特别多,只有个别字段不用显示,写起来够麻烦,那么你就声明一个全局字符串变量,使用的时候用replace处理一下.dim strFld as string
strFld="a,b,c,d,e,f,g....."dim sql string
sql="select "& replace(strFld,",c",vbnullstring) &" from tb where ......"
SELECT @Col_table1='',@Col_table2=''
SELECT @Col_table1=@Col_table1+COLUMN_NAME+','
From INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='table1'
AND COLUMNPROPERTY(Object_ID('table1'),COLUMN_NAME,'IsIdentity')=0
ORDER BY ORDINAL_POSITION SELECT @Col_table2=@Col_table2+COLUMN_NAME+','
From INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='table2'
AND COLUMNPROPERTY(Object_ID('table2'),COLUMN_NAME,'IsIdentity')=0
ORDER BY ORDINAL_POSITION SELECT @Col_table1=LEFT(@Col_table1,LEN(@Col_Table1)-1),@Col_Table2=LEFT(@Col_Table2,LEN(@Col_Table2)-1)
SET @DySQL=
' INSERT INTO table1 (' +@Col_Table1+')
SELECT '+@Col_Table2 +' From table2 '
EXEC (@DySQL)