테이블내 데이터를 insert 구문으로 재구성 > db

출처 : swynk.com

내용 :

테이블내 데이터를 insert구문을 이용한 재구성 가능하게 하는 SP입니다.

SQL7과 SQL2000몇개에서 테스트 해 보았으며 잘 돌아 갑니다.

MySQL등에서는 백업 방식의 일종으로 가능한데.. SQL서버는

재구성 스크립이 불가 했지요.

이를 조금더 응용하시면... 여러 다양한 처리가 가능해 지겠지요.

use pubs
go

DECLARE cur_konan_Test CURSOR FAST_FORWARD
FOR
SELECT name FROM sysobjects WHERE xtype='U'

OPEN cur_konan_Test

DECLARE @v_name VARCHAR(100)

FETCH NEXT FROM cur_konan_Test INTO @v_name

WHILE @@FETCH_STATUS = 0
BEGIN
print  char(10) + @v_name + ' 스크립팅중 ' + char(10)
print '--------------------------------------------------'
exec ('EXEC sp_generate_insert_script ''' + @v_name + '''')
FETCH NEXT FROM cur_konan_Test INTO @v_name
END

--커서 CLOSE
CLOSE cur_konan_Test

--커서 DEALLOCATE
DEALLOCATE cur_konan_Test
GO

-------------------------------------------------------------------------

결과

titles테이블 스크립팅중..

insert titles..................
insert titles..................
insert titles..................

publisher 테이블 스크립팅중..

insert publisher..............
insert publisher..............
insert publisher..............

 

식으로 전체 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 varchar(30) = 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
--------------------------------------------------------------------------------

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

  declare @tablename       varchar (128)
  declare @tablename_max   varchar (128)
  declare @tableid         int
  declare @columncount     numeric (7,0)
  declare @columncount_max numeric (7,0)
  declare @columnname      varchar (30)
  declare @columntype      int
  declare @string          varchar (30)
  declare @leftpart        varchar (8000)    /* 8000 is the longest string SQLSrv7 can EXECUTE */
  declare @rightpart       varchar (8000)    /* without having to resort to concatenation */
  declare @hasident        int

  set nocount on

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

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

  create table #columninfo
  (num      numeric (7,0) identity,
   name     varchar(30),
   usertype smallint)

  select name,
         id
    into #tablenames
    from sysobjects
   where type in ('U' ,'S')
     and name like @tablename_mask

  -- loop through the table #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

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

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

      select @columncount     = MIN (num),
             @columncount_max = MAX (num)
        from #columninfo
      while @columncount <= @columncount_max
      begin
        select @columnname = name,
               @columntype = usertype
          from #columninfo
         where num = @columncount
        if (@@rowcount <> 0)
        begin
          if (@columncount < @columncount_max)
          begin
            select @leftpart = @leftpart + @columnname + ','
          end
          else
          begin
            select @leftpart = @leftpart + @columnname + ')'
          end
        end

        select @columncount = @columncount + 1
      end

      select @leftpart = @leftpart + ' 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 = ''

      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 + '+'
            select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace(' + @columnname + ',' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6) + ')+' + replicate( char(39), 4 ) + ',''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 + '+'
            select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace(convert(varchar(1000),' + @columnname + ')' + ',' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6 ) + ')+' + replicate( char(39), 4 ) + ',''NULL'')'
          end

          else if @columntype in (58,61,111) /* datetime fields */
          begin
            select @rightpart = @rightpart + '+'
            select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+convert(varchar(20),' + @columnname + ')+'+ replicate( char(39), 4 ) + ',''NULL'')'
          end

          else   /* numeric types */
          begin
            select @rightpart = @rightpart + '+'
            select @rightpart = @rightpart + 'ISNULL(convert(varchar(99),' + @columnname + '),''NULL'')'
          end

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

        end
        select @columncount = @columncount + 1
      end

    end

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

    -- Order the select-statements by the first column so you have the same order for
    -- different database (easy for comparisons between databases with different creation orders)
    select @rightpart = @rightpart + ' order by 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