--------------------------------------------------------------------------------------------------
출처 : http://sqler.pe.kr
DBMS : Sql Server
※ 스토어드 프로시저(Stored Procedure)를 등록하고 실행하면,
DB 테이블에 있는 데이터들을 Insert문으로 출력해준다. 그 결과를 파일로 저장하면 된다.
(아래에 첨부 파일이 있으니 다운로드 받아서 사용하세요~)
--------------------------------------------------------------------------------------------------
use LibraryProject
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 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
--------------------------------------------------------------------------------------------------
/* [실행 예] : 실행 인자 테이블 이름 */
exec sp_generate_insert_script 'Book'
exec sp_generate_insert_script 'BookUser'
exec sp_generate_insert_script 'BookCurrentState'
exec sp_generate_insert_script 'Lend'
exec sp_generate_insert_script 'RFID_INFO'
exec sp_generate_insert_script 'UserState'
-------------------------------------------------------------------------------------------------
'IT_DBMS > MSSQL' 카테고리의 다른 글
[펌] mssql 쓸만한 저장 프로시져 (0) | 2010.06.30 |
---|---|
[펌] 현재 sql server 에서 가장 많은 부하를 일으키는 spid 와 쿼리는? (0) | 2010.06.28 |
MSSQL Server 2005 Tips : 윈도우 인증 → 혼합 인증 & IP로 접근하기 (0) | 2009.06.17 |
[펌] Microsoftware SQL 서버 2005 실전 활용 4 (0) | 2007.02.11 |
[펌] Microsoftware SQL 서버 2005 실전 활용 3 (0) | 2007.02.11 |