从一张带自增长列的表中按条件导出数据，并将结果重新编号，插入到另外一张表的sql语句

 
   
    
   -- 
   -------------------------------------------- 
   
-- 
   -说明: 从一张带自增长列的表中按条件导出数据， 
   
-- 
   -并将结果重新编号，插入到另外一张表 
   
-- 
   -日期：2010-07-13 
   
-- 
   -作者：sixiweb 
   
-- 
   -------------------------------------------- 
   
 
   CREATE 
     
   TABLE 
    #tmptb
(
     
   [ 
   ID 
   ] 
     
   [ 
   int 
   ] 
     
   IDENTITY 
   ( 
   1 
   , 
   1 
   )  
   NOT 
     
   NULL 
   ,
     
   [ 
   Fixing_Sn 
   ] 
     
   [ 
   int 
   ] 
     
   NULL 
   ,
     
   [ 
   DataType 
   ] 
     
   [ 
   nvarchar 
   ] 
   ( 
   20 
   ) COLLATE Chinese_PRC_CI_AS  
   NULL 
   ,
     
   [ 
   UnDealData 
   ] 
     
   [ 
   decimal 
   ] 
   ( 
   18 
   ,  
   2 
   )  
   NULL 
   ,
     
   [ 
   DealedData 
   ] 
     
   [ 
   decimal 
   ] 
   ( 
   18 
   ,  
   2 
   )  
   NULL 
   ,
     
   [ 
   DataDateTime 
   ] 
     
   [ 
   datetime 
   ] 
     
   NULL 
   ,
     
   [ 
   IsOK 
   ] 
     
   [ 
   nvarchar 
   ] 
   ( 
   1 
   ) COLLATE Chinese_PRC_CI_AS  
   NULL 
   ,
     
   [ 
   Memo 
   ] 
     
   [ 
   nvarchar 
   ] 
   ( 
   768 
   ) COLLATE Chinese_PRC_CI_AS  
   NULL 
   ,
     
   CONSTRAINT 
     
   [ 
   PK_tmptb 
   ] 
     
   PRIMARY 
     
   KEY 
     
   CLUSTERED 
    
        (
             
   [ 
   ID 
   ] 
     
   ASC 
   
        )  
   ON 
     
   [ 
   PRIMARY 
   ] 
   
    ) 
 
   ON 
     
   [ 
   PRIMARY 
   ] 
   

 
   INSERT 
     
   INTO 
    #tmptb
(
    Fixing_Sn,
    DataType,
    UnDealData,
    DealedData,
    DataDateTime,
    IsOK,
    Memo
)
 
   SELECT 
     
   TOP 
     
   1000 
       
   [ 
   Fixing_Sn 
   ] 
   
      , 
   [ 
   DataType 
   ] 
   
      , 
   [ 
   UnDealData 
   ] 
   
      , 
   [ 
   DealedData 
   ] 
   
      , 
   [ 
   DataDateTime 
   ] 
   
      , 
   [ 
   IsOK 
   ] 
   
      , 
   [ 
   Memo 
   ] 
   
     
   FROM 
    DataInfo di 
     
   WHERE 
    di.Fixing_Sn  
   IN 
   (
         
   SELECT 
    FixingSn  
   FROM 
    Fixing f  
   WHERE 
    f.PhysicsAddress  
   IN 
    ( 
   ' 
   02 
   ' 
   , 
   ' 
   03 
   ' 
   , 
   ' 
   04 
   ' 
   , 
   ' 
   05 
   ' 
   )
    )
     
   AND 
    di.IsOK  
   = 
     
   ' 
   0 
   ' 
   


 
   SELECT 
     
   * 
     
   FROM 
    #tmptb


 
   DROP 
     
   TABLE 
    #tmptb