动态创建临时表

 

-------------------------------------
--create table
-------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cellinf]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[cellinf]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rowinf]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rowinf]
GO

CREATE TABLE [dbo].[cellinf] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [cell] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rowinf] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [row] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[cellinf] WITH NOCHECK ADD
 CONSTRAINT [PK_cellinf] PRIMARY KEY  CLUSTERED
 (
  [id]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[rowinf] WITH NOCHECK ADD
 CONSTRAINT [PK_rowinf] PRIMARY KEY  CLUSTERED
 (
  [id]
 )  ON [PRIMARY]
GO

-----------------------------------------------------------------
--insert data
-----------------------------------------------------------------
insert into cellinf(cell) values ('姓名')
insert into cellinf(cell) values ('项一')
insert into cellinf(cell) values ('项二')
insert into cellinf(cell) values ('项三')

insert into rowinf(row) values ('1行')
insert into rowinf(row) values ('2行')
insert into rowinf(row) values ('3行')
insert into rowinf(row) values ('4行')
-----------------------------------------------------------------
--create procedure
-----------------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mytable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mytable]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE mytable AS
DECLARE @create_sql varchar(8000)
SET @create_sql = ''
DECLARE pxReg_Cursor CURSOR
 FOR SELECT cell FROM cellinf

DECLARE @itemname varchar(500)

OPEN pxReg_Cursor

FETCH NEXT FROM pxReg_Cursor INTO @itemname

WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @create_sql =  @create_sql + @itemname + ' varchar(500),'
 FETCH NEXT FROM pxReg_Cursor INTO @itemname
END

CLOSE pxReg_Cursor
DEALLOCATE pxReg_Cursor
SELECT @create_sql=LEFT(@create_sql, LEN(@create_sql)-1)

SELECT @create_sql= 'CREATE TABLE #reg('+@create_sql+')  '

DECLARE @insert_sql varchar(8000)
SET @insert_sql=' INSERT INTO #reg(姓名) SELECT row as 姓名 FROM rowinf '

DECLARE @sel_sql varchar(8000)
SET @sel_sql=' SELECT * FROM #reg drop table #reg '

--print @create_sql
EXEC (@create_sql + @insert_sql + @sel_sql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

EXEC mytable