Sql Server 快速获取表中数据的Insert语句,便于快速复制

EXEC usp_Tool_GetSQL @tablename='****',@condition=' WHERE ****=''****'''

EXEC usp_Tool_GetSQL @tablename='*****'

CREATE proc [dbo].[usp_Tool_GetSQL] @tablename sysname,  @aimtablename  sysname = '',  @condition  nvarchar(4000)  = '',  @showSql char(1)   = 'N'  ,@columnnames nvarchar(4000) ='*'ASbegin   declare @sqlName varchar(max)   declare @sqlValues varchar(max)   declare @strCondition varchar(1000)     SET NOCOUNT ON      select @sqlName =' ('   select @sqlValues = 'VALUES (''+'   if(@aimtablename = '')    select @aimtablename = @tablename   select @sqlValues = @sqlValues + ColValue + ' + '','' + ' ,@sqlName = @sqlName + '[' + ColName + '],' from    (select case       when xtype in (48,52,56,59,60,62,104,106,108,122,127) --数字类型                                      then 'case when ['+ name +'] is null then ''NULL'' else ' + 'cast(['+ name + '] as varchar)'+' end'      when xtype in (58,61) --smalldatetime datetime      --then 'case when ['+ name +'] is null then ''NULL'' else '+''''''''' + ' + 'cast(['+ name +'] as varchar)'+ '+'''''''''+' end'      then 'case when ['+ name +'] is null then ''NULL'' else '+''''''''' + ' + 'convert(nvarchar(24),['+ name +'],121)'+ '+'''''''''+' end'    --  when xtype in (167,175)--(var)char      --     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+''''''''' + ' end'      when xtype in (167,175)--(var)char       then 'case when ['+ name +'] is null then ''NULL'' else '+'''N'''''' + ' + 'replace(['+ name+'],'''''''','''''''''''')' + '+''''''''' + ' end'      when xtype in (231,239)--(nvar)char       then 'case when ['+ name +'] is null then ''NULL'' else '+'''N'''''' + ' + 'replace(['+ name+'],'''''''','''''''''''')' + '+''''''''' + ' end'      else '''NULL'''     end as ColValue,name as ColName    from syscolumns      where id = object_id(@tablename)   AND (@columnnames='*' or CHARINDEX(','+name+',',','+@columnnames+',')>0)   --and autoval is null --当该栏位为自增型int时，会出现autoval不为null的情况。    ) T    --不同的DB计算出来的长度可能不一样，所以最后一个是逗号的话，多减去一个字符的长度  select @sqlValues = left(@sqlValues,len(@sqlValues)-4)   if left(reverse(@sqlValues),1) = ','  select @sqlValues = left(@sqlValues,len(@sqlValues)-1)   if(@showSql='Y')   BEGIN    print '--SQL1 - GenColoums:'    print '--select ''INSERT INTO ['+ @aimtablename + ']' + left(@sqlName,len(@sqlName)-1)+') ' + @sqlValues + ')'' AS INSERTSQL from '+@tablename + space(1) + @condition    print '--SQL2 - Not GenColoums:'    print '--select ''INSERT INTO ['+ @aimtablename + '] ' +  @sqlValues + ')'' AS INSERTSQL from '+@tablename  + space(1) + @condition    END   select @sqlName = left(@sqlName,len(@sqlName)-1)   select @strCondition=replace(@condition,'''','''''')   /*   --from table to table   exec ('SELECT ''--['+@tablename+']-->['+@aimtablename+']''  as [ ]')   --delete existed records    exec('select ''DELETE FROM'+ @tablename + ' ' + @strCondition+''' as [--Delete SQL]')   */   --get insert sql exec('SELECT ''--['+@tablename+']-->['+@aimtablename+']''  as [ ] UNION ' +     'SELECT ''DELETE FROM ['+ @tablename + '] ' + @strCondition+''' as [ ] UNION ' +     'SELECT ''INSERT INTO ['+ @aimtablename + ']' + @sqlName +') '+ @sqlValues + ')'' as [ ]  from '+ @tablename  + ' '  + @condition)     SET NOCOUNT OFF  end