자바블로그

ms-sql서버 작업시 신 버전에서(2005이상) 에서 * 등의 쿼리문을 사용 하려고 하면

호환성 레벨을 80이하로 맞춰야 한다는 문구가 나오는 경우가 있다

물론 LEFT OUTER JOIN등의 쿼리문을 사용해야 하겠지만 기존 소스 유지보수 혹은

부득이한 경우 아래의 쿼리문을 싱행해 준다

EXEC sp_dbcmptlevel '디비네임', '80';

MS-SQL에서 INSERT문을 출력 해야 하는 경우 쿼리문을 이용해서 특정 테이블의 INSERT문을 출력 할 수 있습니다.

(SQL MENAGEMENT STUDIO의 데이터베이스별 태스크>데이터내보내기 로 가지고 올 수도 있습니다.)

프로시저를 생성 하고

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 

-- 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       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

해당 계정으로 아래 내용을 변경후 실행

use TEST_DATABASE(DB이름)

go

DECLARE cur_konan_Test CURSOR FAST_FORWARD

FOR

SELECT 'template'(테이블 이름)

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

ms-sql 형변환

2가지 메소드중에 하나를 선택 한다.

보동 TO_CHAR/TO_NUMBER등의 ORACLE 함수와 대응하는 함수로 CAST()

를 사용하고

DATE형태의 형변환에 CONVERT를 사용해서 처리 하고 있다.

CAST()

- CAST(바꿀 데이터 AS 자료형)

- CAST(123 AS CHAR(10)) -> 숫자형을 CHAR(10)의 형태로 변경

- CAST('123' AS INT) -> 문자형을 INT의 형태로 변경

CONVERT()

- CONVERT(바뀐자료형[길이], 원본자료, 유형)

- CONVERT(VARCHAR(20), GETDATE(), 101)

CONVERT 에서 사용하는 유형의 종류는 아래와 같다

SQL SERVER 를 Management Studio 에서 작업시 테이블 수정을 하려고 할때

[테이블을 다시 만들어야 하는 변경 내용 저장 사용 안함] 이라는 메지가 나오는 경우

설정을 변경해 주면 된다.

도구> 옵션> Designer> 테이블 및 데이터베이스 디자이너 로 들어가서

테이블 다시 반들어야 하는 변경 내용 저장 사용 안 함의 체크를 빼주면 된다.