日期:2014-05-18  浏览次数:20438 次

如何动态的插入数据select * into @表名(动态生成)from
DECLARE   @Airline   NVARCHAR(10)
DECLARE   @sql   NVARCHAR(1000)

DECLARE   Split_Airline   CURSOR   FOR  
SELECT   count(Airline),Airline   FROM   FilterTable   GROUP   BY   Airline
ORDER   BY   Airline

OPEN   Split_Airline

FETCH   NEXT   FROM   Split_Airline  
INTO   @Airline

WHILE   @@FETCH_STATUS   =   0
BEGIN
                  --
IF   OBJECTPROPERTY   (object_id( '@Airline '), 'ISTABLE ')   =   1    

BEGIN

--SET   sql   =   'SELECT   field   INTO   V_A   FROM '   ||   tablename;
SET   @sql   = 'SELECT   *   INTO   '+   @Airline   +   '   FROM   FilterTable   where   Airline=@Airline '
select     @sql

--PREPARE   s1   FROM   @sql
--EXECUTE   sql(@sql)

--exec   sql   (SELECT   *   INTO   ' '+   @Airline   +   ' '   FROM   FilterTable   where   Airline=@Airline)

exec   s1
END  
   
FETCH   NEXT   FROM   Split_Airline  
INTO   @Airline

END
CLOSE   Split_Airline
DEALLOCATE   Split_Airline

1,如何动态的判断已经建立的表存在
IF   OBJECTPROPERTY   (object_id( '@Airline '), 'ISTABLE ')   =   1    
@Airline是我要动态建立的表名,这样写对吗?
2,如何select   *     INTO   @Airline   from   FilterTable   where   Airline=@Airline
@Airline是从游标中动态获得的,要作为表名插入。

谢谢!



------解决方案--------------------
1,
IF EXISTS select * from sysobjects where object_id( ' ' ' '+ @Airline+ ' ' ' ')=ID
2,

DECLARE @SQL NVARCHAR(4000)
SET @SQL= 'select * INTO '+@Airline + ' from FilterTable where Airline= ' ' '+@Airline+ ' ' ' '
EXEC(@SQL)

------解决方案--------------------
1,如何动态的判断已经建立的表存在
IF OBJECTPROPERTY (object_id( '@Airline '), 'ISTABLE ') = 1
@Airline是我要动态建立的表名,这样写对吗?
-----------------------------------------------
IF OBJECTPROPERTY (object_id(@Airline), 'ISTABLE ') = 1


2,如何select * INTO @Airline from FilterTable where Airline=@Airline
@Airline是从游标中动态获得的,要作为表名插入。
-----------------------------------------------
exec( 'select * INTO '+@Airline+ ' from FilterTable where Airline= ' ' '+@Airline+ ' ' ' ')

------解决方案--------------------
--变量两侧不要加 ' '

IF OBJECTPROPERTY (object_id(@Airline), 'ISTABLE ') = 1
------解决方案--------------------
1,应该正确的

2,用动态sql即可