table information to sql file with the help of generate sql script

[michelleqw]

Dear sql-server users,

We are trying to make a sql file with structure and records of our tables with the help of “tools / generate sql script”. If we do so the only result is that the structure is put in a sql file.
Is it possible to get the row information? If yes, how do we do that. We don’t want to make a backup!

Nice regards,

Michelle.

 

[dbomrrsm]

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  Procedure sp_CreateDataLoadScript
@TblName varchar(128)
as
/*
To execute
exec sp_CreateDataLoadScript 'YourTableName'
*/


	create table #a (id int identity (1,1), ColType int, ColName varchar(128))

	insert 	#a (ColType, ColName)
	select case when DATA_TYPE like '%char%' then 1 else 0 end ,
		COLUMN_NAME
	from 	information_schema.columns
	where 	TABLE_NAME = @TblName
	order by ORDINAL_POSITION
	
	if not exists (select * from #a)
	begin
		raiserror('No columns found for table %s', 16,-1, @TblName)
		return
	end

declare	@id int ,
	@maxid int ,
	@cmd1 varchar(7000) ,
	@cmd2 varchar(7000)

	select 	@id = 0 ,
		@maxid = max(id)
	from 	#a

	select	@cmd1 = 'select '' insert ' + @TblName + ' ( '
	select	@cmd2 = ' + '' select '' + '
	while @id < @maxid
	begin
		select @id = min(id) from #a where id > @id

		select 	@cmd1 = @cmd1 + ColName + ','
		from	#a
		where	id = @id

		select @cmd2 = 	@cmd2
				+ ' case when ' + ColName + ' is null '
				+	' then ''null'' '
				+	' else '
				+	  case when ColType = 1 then  ''''''''' + ' + ColName + ' + ''''''''' else 'convert(varchar(20),' + ColName + ')' end
				+ ' end + '','' + '
		from	#a
		where	id = @id
	end


	select @cmd1 = left(@cmd1,len(@cmd1)-1) + ' ) '' '
	select @cmd2 = left(@cmd2,len(@cmd2)-8) + ' from ' + @tblName

	select '/*' + @cmd1 + @cmd2 + '*/'

	exec (@cmd1 + @cmd2)
	--SELECT * FROM #a
	drop table #a


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

[blue]DBomrrsm[/blue]

 

[michelleqw]

Thanks dbomrrsm for the information.

Is there not an more easy way to do it by "generate sql script"?

Michelle.

 

[JamesLean]

The "generate sql script" option only scripts object definitions - it cannot be used to generate data scripts.

Is there any particular reason you don't want to use a backup?

--James

 

[dbomrrsm]

now way I know of although creating the above stored procedure and running it isnt much harder than doing it by generating a script.

[blue]DBomrrsm[/blue]