Dump Table Data (T-SQL)

最新推荐文章于 2022-05-16 17:03:55 发布

EricYeung

最新推荐文章于 2022-05-16 17:03:55 发布

阅读量1.3k

点赞数

分类专栏： SQL 文章标签： table insert manager jobs numbers output

本文链接：https://blog.csdn.net/EricYeung/article/details/724235

版权

SQL 专栏收录该内容

54 篇文章 0 订阅

订阅专栏

/*
    dump table data
*/
use pubs
go

declare @table sysname
select @table = 'jobs'

-- select * from systypes
declare @insert_stmt nvarchar(4000), @titles_stmt nvarchar(4000), @values_stmt nvarchar(4000),
    @fields nvarchar(4000), @values nvarchar(4000)
select @insert_stmt = '', @titles_stmt = '', @values_stmt = '', @fields = '', @values = ''

select @fields = @fields + ',' + name,
    @values = @values + ' + ' +
        case when xtype in (231, 239) /* nchar, nvarchar */ then ' '' N'''''' ' else ' '' '''''' ' end +
        '+ISNULL(RTRIM(' +
        case when xtype in (167, 175, 231, 239) then name -- char, nchar, varchar, nvarchar
            when xtype in (61) then  'CONVERT(VARCHAR,' + name + ', 121)' -- datetime
            when xtype in (58) then  'CONVERT(VARCHAR,' + name + ', 120)' --smalldatetime
            else 'CONVERT(VARCHAR,' + name + ')' --uniqueidentifier and numbers
        end  + '),'''')+'''''','''
from syscolumns
where id = object_id(@table) and xtype not in(98, 189, 34, 35, 99) -- exclude sql_variant, timestamp, image, text, ntext

--print @values
select @fields = stuff(@fields, 1, 1, ''), @values = stuff(@values, 1, 1, '')
select @values = stuff(@values, len(@values)-1, 1, '')
--print @values

select @insert_stmt = 'print ''insert into ' + @table + '(' + @fields + ')'''
select @titles_stmt = 'print ''select ' + @fields + ' from ' + @table + ' where 1=2'''
select @values_stmt = 'select ''union all select '' ' +@values+ ' from ' + @table
-- print @insert_stmt
-- print @titles_stmt
-- print @values_stmt
exec(@insert_stmt + @titles_stmt + @values_stmt)

/* output
insert into jobs(job_id,job_desc,min_lvl,max_lvl)
select job_id,job_desc,min_lvl,max_lvl from jobs where 1=2
                                                                                                                                                                             
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
union all select  '1', 'New Hire - Job not specified', '10', '10'
union all select  '2', 'Chief Executive Officer', '200', '250'
union all select  '3', 'Business Operations Manager', '175', '225'
union all select  '4', 'Chief Financial Officier', '175', '250'
union all select  '5', 'Publisher', '150', '250'
union all select  '6', 'Managing Editor', '140', '225'
union all select  '7', 'Marketing Manager', '120', '200'
union all select  '8', 'Public Relations Manager', '100', '175'
union all select  '9', 'Acquisitions Manager', '75', '175'
union all select  '10', 'Productions Manager', '75', '165'
union all select  '11', 'Operations Manager', '75', '150'
union all select  '12', 'Editor', '25', '100'
union all select  '13', 'Sales Representative', '25', '100'
union all select  '14', 'Designer', '25', '100'
*/