[mssql] sql 2005 insert문 sql백업 프로시저

MSSQL 2005 사용중 sql 파일로 백업을 하려하니 ssms 에서 기능이 없다

2008에서는 있었는데 패키지가 없는건지 찾을수없어 프로시저로 파일 생성

#테이블 컬럼명만 보기

select column_name, data_type

from information_schema.columns

where table_name='테이블명';

#단순쿼리

select '  INSERT INTO [TotalBoard].[dbo].[Tbl_Count] ([Count_Date]  ,[Count_Today]   ,[Count_Total])       VALUES   ('+ CAST([Count_Date] as varchar) +'  ,'+ CAST([Count_Today] as varchar) +' ,'+ CAST([Count_Total] as varchar) +')'  from [Tbl_Count];

#실행문 

exec [TotalBoard].[dbo].[Generate_insert_sql] 'Tbl_kbart_Notice';

#프로시저 

USE [TotalBoard]

GO

/****** 개체:  StoredProcedure [dbo].[Generate_insert_sql]    스크립트 날짜: 12/28/2012 10:32:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE PROCEDURE [dbo].[Generate_insert_sql]

-- Add the parameters for the stored procedure here

@table varchar(50)

AS

declare @cols varchar(1000)

declare @col varchar(50)

set @cols =''

declare colcur

cursor for

select column_name

from information_schema.columns

where table_name=@table 

open colcur

fetch next from colcur into @col

while @@fetch_status=0

begin

select @cols = @cols + ', ' + @col

fetch next from colcur into @col

end

close colcur

deallocate colcur

select @cols = substring(@cols, 3, datalength(@cols))

--select @cols

declare @sql varchar(4000)

declare @colname varchar(100),

@coltype varchar(30)

select @sql = 'select replace(''insert ' + @table + ' (' + @cols + ') '

select @sql = @sql + 'values ('''

declare ccur

cursor for

select column_name, data_type

from information_schema.columns

where table_name=@table

open ccur

fetch from ccur into @colname, @coltype

while @@fetch_status=0

begin

if @coltype in ('varchar', 'char', 'datetime','text')

select @sql=@sql + ''''''

select @sql=@sql + ' + coalesce(convert(varchar, ' + @colname + '), ''null'') + '

if @coltype in ('varchar', 'char', 'datetime','text')

select @sql=@sql + ''''''

select @sql = @sql + ''', '''

fetch from ccur into @colname, @coltype

end

close ccur

deallocate ccur

select @sql=substring(@sql, 1, datalength(@sql)-3)

select @sql=@sql + ')'', ''''''null'''''', ''null'') from ' + @table

exec (@sql)