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