从数据表中导出数据并生插入SQL语句

USE DB

DECLARE @tablename sysname 
DECLARE @RowCount int
DECLARE @whereClause nvarchar(200) 
DECLARE @KeyColumn sysname 

SET @tablename = 'dbo.Tab'  --这是你要导出的表名
SET @RowCount = 20 --导出的记录数
SET @KeyColumn = 'TransactionNumber' --排序字段
SET @whereClause = ' Where InNumber IN (1100239184)'

DECLARE @column varchar(MAX) 
DECLARE @columndata varchar(MAX) 
DECLARE @sql varchar(max) 
DECLARE @xtype tinyint 
DECLARE @name sysname 
DECLARE @objectid int 
DECLARE @objectname sysname 
DECLARE @ident int 

SET nocount ON 
SET @objectid=OBJECT_ID(@tablename) 
IF @objectid is null -- 判定对象是否存在 
begin 
    select 'the object not exists' 
    return 
end 

SET @objectname=rtrim(object_name(@objectid)) 
IF @objectname is null or charindex(@objectname,@tablename)=0 --此判定不严密 
begin 
    select 'object not in current database' 
    return 
end 

if objectproperty(@objectid,'istable') <> 1 AND objectproperty(@objectid,'isview') <> 1 -- 判定对象是否是table 
begin 
    select 'the object is not table& view' 
    return 
end 

SELECT 
    @ident=status&0x80 
FROM syscolumns 
WHERE 
    id=@objectid 
    AND status&0x80=0x80 

--IF @ident is not null 
--    PRINT 'SET IDENTITY_INSERT '+@tablename+' ON' 
--

DECLARE syscolumns_cursor CURSOR 
FOR SELECT 
        c.name
       ,c.xtype 
    FROM syscolumns c 
    WHERE 
        c.id=@objectid 
    ORDER BY c.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 +char(10)
        SET @columndata=@columndata+CASE WHEN len(@columndata)=0 THEN '' ELSE '+'',' end 
            +case    when @xtype in(167,175) then '''+ISNULL(''''''''+convert(varchar(max),replace(rtrim('+@name+'),'''''''',''''''''''''''''''''))+'''''''',''NULL'')' --varchar,char 
                    when @xtype in(231,239) then '''+ISNULL(''N''''''+convert(nvarchar(max),replace(rtrim('+@name+'),'''''''',''''''''''''''''''''))+'''''''',''NULL'')' --nvarchar,nchar 
                    when @xtype=61 then '''+ISNULL(''''''''+convert(varchar(23),'+@name+',121)+'''''''',''NULL'')' --datetime 
                    when @xtype=58 then '''+ISNULL(''''''''+convert(varchar(16),'+@name+',120)+'''''''',''NULL'')' --smalldatetime 
                    when @xtype=36 then '''+ISNULL(''''''''+convert(varchar(36),'+@name+')+'''''''',''NULL'')' --uniqueidentifier 
                    else '''+ISNULL(convert(varchar(23),'+@name+'),''NULL'')'
            end 
            +'+''     --'+@name+'''+char(10)'+char(10)
    end 
end 
FETCH NEXT FROM syscolumns_cursor INTO @name
                                      ,@xtype 
END 

close syscolumns_cursor 
deallocate syscolumns_cursor 
declare @Result nvarchar(max)
--set @Result = 'SET NOCOUNT ON '+CHAR(10)+'
set @Result = 'SELECT TOP '+cast(@RowCount as varchar(20))+ ''' INSERT  INTO '+@tablename+'('+@column+') VALUES('--+'+@columndata+','')'' from '+@tablename +' WHERE TrackingNumber = ''51000272846201'''

set @Result = @Result +char(10)+ @columndata +'+'')'''
set @Result = @Result +char(10)+  ' FROM '+ @tablename
if (@whereClause IS NOT NULL)
begin
    set @Result = @Result +char(10)+  @whereClause
end
if (@KeyColumn IS NOT NULL)
begin
   set @Result = @Result +char(10)+  ' ORDER BY '+@KeyColumn+' Desc'
end
--select @Result
exec sp_executesql @Result
--IF @ident IS NOT NULL PRINT 'SET IDENTITY_INSERT '+@tablename+' OFF'