home
Script data from tables to files as insert statements
Author Nigel Rivett
This isn't DMO but it compliments the other scripts.
Given a table it will script all the data as insert statements.
This can be used to populate a table or to record changes e.g. in lookup tables.
It uses sp_CreateDataLoadScript (the remote version) which can be found under tsql scripts on this site)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_ScriptData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ScriptData]
GO
create proc s_ScriptData
@SourceUID varchar(128) ,
@SourcePWD varchar(128) ,
@OutFilePath varchar(256) ,
@OutFileName varchar(128) = null ,
@SourceSVR varchar(128) ,
@Database varchar(128) ,
@TableName varchar(128)
as
/*
exec s_ScriptData
@SourceUID = null ,
@SourcePWD = null ,
@OutFilePath = 'c:\TestScripts\' ,
@OutFileName = null ,
@SourceSVR = '(local)' ,
@Database = 'northwind' ,
@TableName = 'sysconstraints'
*/
declare @sql varchar(4000)
if @OutFileName is null
Select @OutFileName = @TableName + '.sql'
if @SourceSVR = '(local)'
select @SourceSVR = '[' + @@servername + ']'
-- create output directory - will fail if already exists but ...
select @OutFilePath = @OutFilePath + replace(replace(@SourceSVR,'[',''),']','')
select @sql = 'mkdir "' + @OutFilePath + '"'
exec master..xp_cmdshell @sql, no_output
select @OutFilePath = @OutFilePath + '\' + replace(replace(@Database,'[',''),']','')
select @sql = 'mkdir "' + @OutFilePath + '"'
exec master..xp_cmdshell @sql, no_output
select @OutFilePath = @OutFilePath + '\Data'
select @sql = 'mkdir "' + @OutFilePath + '"'
exec master..xp_cmdshell @sql, no_output
select @OutFilePath = @OutFilePath + '\'
select @sql = 'bcp "set fmtonly off exec admin..sp_CreateDataLoadScript ''' + @SourceSVR + ''' , ''' + @Database + ''' , ''' + @TableName + '''" queryout "' + @OutFilePath + @OutFileName + '" -c -S' + @@servername
if @SourceUID is not null
begin
select @sql = @sql + ' -U' + @SourceUID + ' -P' + @SourcePWD
end
exec master..xp_cmdshell @sql
GO
home