MSSQL 临时表的相关内容

临时表增加任意列并插入值得算法

 

CREATE TABLE #TB_YEAR

(

 ID INT PRIMARY KEY

)  --建立临时表

 

INSERT INTO #TB_YEAR (ID) VALUES (1) --插入值为1

ALTER TABLE #TB_YEAR ADD NAME VARCHAR(20)  -T-SQL语句 增加一列

--增加任意列

DECLARE @FY VARCHAR(4)

DECLARE @TY VARCHAR(4)

 

DECLARE @SSQL NVARCHAR(MAX)

 

SET @FY = '2015'

SET @TY = '2018'

 

WHILE @FY <= @TY

BEGIN

             SET @SSQL = ''

             SET @SSQL += N'ALTER TABLE #TB_YEAR ADD D' + @FY + 'D VARCHAR(5) NULL '+ CHAR(13)

             EXEC SP_EXECUTESQL @SSQL

            

             SET @SSQL= ''

            SET @SSQL += N'UPDATE #TB_YEAR SET D' + @FY + 'D = ''500'' '+ CHAR(13)           

             EXEC SP_EXECUTESQL @SSQL

--          SELECT @SSQL

            

             SET @FY = CAST(@FY AS INT) + 1

             -

END

 

SELECT * FROM  #TB_YEAR  -查询结果

DROP TABLE #TB_YEAR  删除表