테이블DATA를 INSERT문으로 만들어주는 SP 수정본 > db

use master
go

if exists (select name from sysobjects where name = 'sp_generate_insert_script')
begin
drop proc sp_generate_insert_script
print 'old version of sp_generate_insert_script dropped'
end
go

create procedure sp_generate_insert_script
 @tablename_mask nvarchar(30) = NULL
, @whereClause nvarchar(4000) = NULL
, @datetimeStyle int = NULL
as
begin
--------------------------------------------------------------------------------

-- Stored Procedure:  sp_generate_insert_script
-- Language:          Microsoft Transact SQL (7.0)
-- Author:            Inez Boone (inez.boone@xs4al.nl)
--                    working on the Sybase version of & thanks to:
--                    Reinoud van Leeuwen (reinoud@xs4all.nl)
-- Version:           1.4
-- Date:              December 6th, 2000
-- Description:       This stored procedure generates an SQL script to fill the
--                    tables in the database with their current content.
-- Parameters:        IN: @tablename_mask : mask for tablenames
-- History:           1.0 October 3rd 1998 Reinoud van Leeuwen
--                      first version for Sybase
--                    1.1 October 7th 1998 Reinoud van Leeuwen
--                      added limited support for text fields; the first 252
--                      characters are selected.
--                    1.2 October 13th 1998 Reinoud van Leeuwen
--                      added support for user-defined datatypes
--                    1.3 August 4 2000 Inez Boone
--                      version for Microsoft SQL Server 7.0
--                      use dynamic SQL, no intermediate script
--                    1.4 December 12 2000 Inez Boone
--                      handles quotes in strings, handles identity columns
--                    1.5 December 21 2000 Inez Boone
--                      Output sorted alphabetically to assist db compares,
--                      skips timestamps
--        1.6 August 16 2005 Younggun Kim
--   add uid when selecting tables from sysobjects,
--   quotename() on columns,
--   adding @whereclause parameter to filter the result on specified table,
--   adding @datetimeStyle parameter to decorate the datetime columns,
--   order by clustered indexed columns if there is
--------------------------------------------------------------------------------

-- NOTE: If, when executing in the Query Analyzer, the result is truncated, you can remedy
--       this by choosing Query / Current Connection Options, choosing the Advanced tab and
--       adjusting the value of 'Maximum characters per column'.
--       Unchecking 'Print headers' will get rid of the line of dashes.

declare @tablename       nvarchar (128)
declare @tablename_max   nvarchar (128)
declare @tableid         int
declare @columncount     numeric (7,0)
declare @columncount_max numeric (7,0)
declare @columnname      nvarchar (30)
declare @columntype      int
declare @string          nvarchar (30)
declare @leftpart        nvarchar (4000)    /* 8000 is the longest string SQLSrv7 can EXECUTE */
declare @rightpart       nvarchar (4000)    /* without having to resort to concatenation */
declare @hasident        int
declare @columeList  nvarchar(4000)
declare @keys  nvarchar(1000)
declare @i  int
declare @thiskey nvarchar(100)
declare @keyCheck bit

set nocount on

-- take ALL tables when no mask is given (!)
if (@tablename_mask is NULL)
begin
 select @tablename_mask = '%'
end

if(@whereClause is not null and @tablename_mask is null)
begin
 print 'if you use where clause, please choose the table first.'
 return
end
else if(lower(left(ltrim(@whereClause), 5)) != N'where')
begin
 set @whereClause = 'where ' + @whereClause
end

-- create table variable columninfo now, because it will be used several times

CREATE TABLE #columninfo
(num      int identity,   --change numeric to int
name     varchar(30),
usertype smallint)

-- change temp table to table variable.
DECLARE @tableNames TABLE
(
 id int PRIMARY KEY
, name sysname
)

-- add uid to distinguish the table has same name
insert into @tableNames
select id, USER_NAME(uid) + '.' + name
from sysobjects
where type in ('U' ,'S')
and name like @tablename_mask

-- loop through the table variable @tablenames
select @tablename_max  = MAX (name), @tablename = MIN (name)
from @tablenames

while @tablename <= @tablename_max
begin
 select @tableid = id
 from @tablenames
 where name = @tablename

 if (@@rowcount <> 0)
 begin
  -- Find out whether the table contains an identity column
  select @hasident = max( status & 0x80 )
  from syscolumns
  where id = @tableid

  truncate table #columninfo

  insert into #columninfo (name,usertype)
  select name, type
  from syscolumns C
  where id = @tableid and type <> 37            -- do not include timestamps
  order by id, colid

  -- Fill @leftpart with the first part of the desired insert-statement, with the fieldnames

  select @leftpart = N'select ''insert into '+@tablename
  select @leftpart = @leftpart + '('

  select @columeList = COALESCE(@columeList + N',', N'') + QUOTENAME(name)
  from #columninfo
  order by num

  set @leftpart = @leftpart + @columeList + N')'

  select @leftpart = @leftpart + N' values('''

  -- Now fill @rightpart with the statement to retrieve the values of the fields, correctly formatted

  select @columncount = MIN (num), @columncount_max = MAX (num)
  from #columninfo

  select @rightpart = N''

  while @columncount <= @columncount_max
  begin
   select @columnname = name, @columntype = usertype
   from #columninfo
   where num = @columncount

   if (@@rowcount <> 0)
   begin

     if @columntype in (39,47) /* char fields need quotes (except when entering NULL);
          *  use char(39) == ', easier readable than escaping
          */
     begin
       select @rightpart = @rightpart + N'+'
       select @rightpart = @rightpart + N'ISNULL(' + replicate( char(39), 4 ) + N'+replace(' +
      @columnname + N',' + replicate( char(39), 4 ) + N',' + replicate( char(39), 6) + N')+' + replicate( char(39), 4 ) + N',''NULL'')'
     end
     else if @columntype = 35 /* TEXT fields cannot be RTRIM-ed and need quotes*/
         /* convert to VC 1000 to leave space for other fields */
     begin
       select @rightpart = @rightpart + N'+'
       select @rightpart = @rightpart + N'ISNULL(' + replicate( char(39), 4 ) + N'+replace(convert(nvarchar(1000),' +
      @columnname + N')' + N',' + replicate( char(39), 4 ) + N',' + replicate( char(39), 6 ) + N')+' + replicate( char(39), 4 ) + N',''NULL'')'
     end
     else if @columntype in (58,61,111) /* datetime fields */
     begin
       select @rightpart = @rightpart + N'+'
       select @rightpart = @rightpart + N'ISNULL(' + replicate( char(39), 4 ) + N'+convert(nvarchar(20),' + @columnname + COALESCE(', ' + CONVERT(NVARCHAR(10), @datetimeStyle), '') + N')+'+ replicate( char(39), 4 ) + N',''NULL'')'
     end
     else   /* numeric types */
     begin
       select @rightpart = @rightpart + N'+'
       select @rightpart = @rightpart + N'ISNULL(convert(nvarchar(99),' + @columnname + N'),''NULL'')'
     end

     if ( @columncount < @columncount_max)
     begin
       select @rightpart = @rightpart + N'+'','''
     end

   end
   select @columncount = @columncount + 1
  end

 end

 select @rightpart = @rightpart + N'+'')''' + N' from ' + @tablename

 if(@whereClause IS NOT NULL)
  set @rightpart = @rightpart + N' ' + @whereClause

 -- Order the select-statements by the clustered indexed column.
 set @keys = NULL
 set @keyCheck = 0

 select @keys = index_col(@tablename, 1, 1), @i = 2
 if (indexkey_property(object_id(@tablename), 1, 1, 'isdescending') = 1)
  select @keys = @keys  + ' DESC'
 if(@keys is not null) set @keyCheck = 1

 while (@keyCheck = 1)
 begin
  select @thiskey = index_col(@tablename, 1, @i)
  if(@thiskey is null)
   set @keyCheck = 0
  else
  begin
   if ((@thiskey is not null) and (indexkey_property(object_id(@tablename), 1, @i, 'isdescending') = 1))
    select @thiskey = @thiskey + ' DESC'
   select @keys = @keys + ', ' + @thiskey, @i = @i + 1
  end
 end

 select @rightpart = @rightpart + N' order by ' + COALESCE(@keys, '1')

 -- For tables which contain an identity column we turn identity_insert on
 -- so we get exactly the same content

 if @hasident > 0
 select 'SET IDENTITY_INSERT ' + @tablename + ' ON'

 exec ( @leftpart + @rightpart )

 if @hasident > 0
 select 'SET IDENTITY_INSERT ' + @tablename + ' OFF'

 select @tablename = MIN (name)
 from @tablenames
 where name > @tablename
end
end