场景; 将数据从一个表导入到另一个表, 除了直接复制粘贴外, 命令行实现.
其他可能: Excel导入导出, 链接数据库等方式.
本案例, 适合小规模执行, 方便, 容易查错.
用法:
EXEC dbo.sp_OutputData @tablename = 'W1ArchiveItem', @Where = 'ArchiveItemName IN (''工龄调整事项'',''工龄调整'',''员工属性'')', -- varchar(4000) @OrderBy = '', -- varchar(4000) @IfNotExistsColumn = '' -- varchar(4000)
产生结果;
过程明细;
/*----------------------------------------------------------*/ /* [sp_OutputData] */ /*----------------------------------------------------------*/ IF EXISTS ( SELECT 1 FROM sys.objects o WHERE object_id = object_id( N'[sp_OutputData]' ) AND OBJECTPROPERTY( object_id, N'IsProcedure') = 1 ) DROP PROCEDURE [sp_OutputData] GO CREATE PROC sp_OutputData ( @tablename sysname, @Where VARCHAR(4000)='', --where条件语句 @OrderBy VARCHAR(4000)='', --order by 语句 @IfNotExistsColumn VARCHAR(4000)='' --生成 IF NOT EXISTS(SELECT 1 FROM @tablename WHERE @IfNotExistsColumn=@columndata) ) AS begin set nocount on declare @column varchar( 4000 ) declare @columndata varchar( 4000 ) declare @sql varchar( max ) declare @xtype tinyint declare @name sysname declare @objectId int declare @objectname sysname declare @ident INT DECLARE @IfNotExistsColumnData VARCHAR(4000) DECLARE @IsTempTable BIT --是否临时表 1:是 0:否 set nocount on IF LEFT(@tablename, 1)='#' AND EXISTS(SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE ID=OBJECT_ID('TEMPDB..'+@tablename) AND XTYPE='U') SET @IsTempTable=1 ELSE SET @IsTempTable=0 IF @IsTempTable=0 set @objectId = object_id( @tablename ) ELSE set @objectId = object_id( 'TEMPDB..'+@tablename ) if( @objectId is null )-- 判斷對象是否存在 begin print 'The object not exists' return end IF @IsTempTable=0 BEGIN set @objectname = rtrim( object_name( @objectId ) ) if( @objectname is null or charindex( @objectname, @tablename ) = 0 )--此判断不严密 begin print 'object not in current database' return end if( OBJECTPROPERTY( @objectId,'IsTable' ) < > 1 ) -- 判斷對象是否是table begin print 'The object is not table' return end select @ident = status & 0x80 from syscolumns where id = @objectid and status & 0x80 = 0x80 if( @ident is not null ) begin print 'SET IDENTITY_INSERT '+@TableName+' ON' END END IF @Where<>'' AND LEFT(@Where, LEN('WHERE'))<>'WHERE' SET @Where='WHERE '+@Where IF @OrderBy<>'' AND LEFT(@OrderBy, LEN('ORDER BY'))<>'ORDER BY' SET @OrderBy='ORDER BY '+@OrderBy declare syscolumns_cursor cursor for SELECT a.name,a.xtype FROM ( select c.name,c.xtype, c.colid from syscolumns c where c.id = @objectid AND @IsTempTable=0 UNION select c.name,c.xtype, c.colid from TEMPDB..syscolumns c where c.id = @objectid AND @IsTempTable=1 ) a order by a.colid open syscolumns_cursor set @column = '' set @columndata = '' fetch next from syscolumns_cursor into @name,@xtype while( @@fetch_status <> -1 ) begin if( @@fetch_status <> -2 ) begin if @xtype not in( 189,34,35,99,98 ) --timestamp不需处理,image,text,ntext,sql_variant 暂时不处理 begin set @column = @column+case when len( @column ) = 0 then '' else ',' end + '[' + @name + ']' set @columndata = @columndata + case when len( @columndata ) = 0 then '' else ','','',' end +case when @xtype in( 167, 175 ) then ''''''''' + ' + @name + ' + ''''''''' --varchar,char when @xtype in(241 ) then ''''''''' + convert(varchar(max), ' + @name + ') + ''''''''' --xml when @xtype in( 231,239 ) then '''N'''''' + ' + @name + ' + ''''''''' --nvarchar,nchar when @xtype = 61 then ''''''''' + convert( char( 23 ),' + @name + ',121 ) + ''''''''' --datetime when @xtype = 40 then ''''''''' + convert( char( 10 ),' + @name + ',121 ) + ''''''''' --datetime when @xtype = 58 then ''''''''' + convert( char( 16 ),' + @name + ',120 ) + ''''''''' --smalldatetime when @xtype = 36 then ''''''''' + convert( char( 36 ),' + @name + ' ) + ''''''''' --uniqueidentifier else @name end IF @IfNotExistsColumn<>'' AND @name=@IfNotExistsColumn BEGIN SET @IfNotExistsColumnData=case when @xtype in( 167, 175 ) then ''''''''' + ' + @name + ' + ''''''''' --varchar,char when @xtype in(241 ) then ''''''''' + convert(varchar(max), ' + @name + ') + ''''''''' --xml when @xtype in( 231,239 ) then '''N'''''' + ' + @name + ' + ''''''''' --nvarchar,nchar when @xtype = 61 then ''''''''' + convert( char( 23 ),' + @name + ',121 ) + ''''''''' --datetime when @xtype = 40 then ''''''''' + convert( char( 10 ),' + @name + ',121 ) + ''''''''' --datetime when @xtype = 58 then ''''''''' + convert( char( 16 ),' + @name + ',120 ) + ''''''''' --smalldatetime when @xtype = 36 then ''''''''' + convert( char( 36 ),' + @name + ' ) + ''''''''' --uniqueidentifier else 'CAST('+@name+' AS VARCHAR)' end END end end fetch next from syscolumns_cursor into @name,@xtype end close syscolumns_cursor deallocate syscolumns_cursor IF @IfNotExistsColumn='' BEGIN set @sql = 'set nocount on select ''insert [' + @tablename + '] ( ' + @column + ' ) values( ''as ''--'',' + @columndata + ','' )'' from ' + @tablename END ELSE BEGIN set @sql = 'set nocount on'+CHAR(13)+CHAR(10)+ 'select'+CHAR(13)+CHAR(10)+ '''IF NOT EXISTS(SELECT 1 FROM [' + @tablename + '] WHERE '+@IfNotExistsColumn+'=''+'+@IfNotExistsColumnData+'+'') insert [' + @tablename + '] ( ' + @column + ' ) values( ''as ''--'',' + @columndata + ','' )'' from ' + @tablename END IF @where<>'' SET @sql=@sql+CHAR(13)+CHAR(10)+@where IF @orderby<>'' SET @sql=@sql+CHAR(13)+CHAR(10)+@orderby print '--'+@sql exec( @sql ) if( @ident is not null ) begin print 'SET IDENTITY_INSERT '+@TableName+' OFF' end set nocount off end GO