home
Google



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