drop procedure sp_xfrdata
go

create procedure sp_xfrdata @table_name varchar(30)= null, @rows varchar(12) = "10" ,
@where varchar(256) = null
WITH encryption
as

/********************************************************************************/
/*	Created By :	Raghu Pattath						*/
/*	Created On :	April 21, 2001						*/
/*	Description:	sp_xfrdata 						*/
/*      Compilation:    Compile in the database that you want to use		*/
/********************************************************************************/
/*      Version    :    Ver 2.0							*/
/*	Resources  :	For updated versions http://www.pattath.com	       	*/
/********************************************************************************/

SET CONCAT_NULL_YIELDS_NULL  OFF
--select "exec sp_xfrdata " + '"' + user_name(uid) + '.' + name + '"' + ",100" from sysobjects where type="U"
--OR
--sp_msforeachtable @command1="print '?'", @command2=" sp_xfrdata '?','ALL' "

if @table_name is null
begin
	PRINT 'Usage to transfer all rows : sp_xfrdata "owner.table", "ALL" '
	PRINT 'OR'
	PRINT 'Usage to transfer the top 50 rows : sp_xfrdata "owner.table", 50 ' 
	PRINT 'OR'
	PRINT 'Usage the default is top 10 rows : sp_xfrdata "table" ' 
	PRINT 'OR'
	PRINT 'Advanced Usage that returns top 3 rows : sp_xfrdata sales,3,"where stor_id = 7131 " '
	PRINT 'OR'
	PRINT 'Advanced Usage that return all rows : sp_xfrdata sales,"ALL","where stor_id = 7131 " '
	PRINT '-----------------------------------------------------------------'
        PRINT "Note: If you are running this in the Query Analyzer then remember to set the Column Size "
        PRINT "to the desired size to avoid truncation "
        PRINT ''
        PRINT 'Note: osql -Usa -Popen -Sthunder -Q "sp_xfrdata titles,100" -d"pubs" -o"titles.txt" -w500'
	RETURN(1)
end


if ( (isnumeric(@rows) = 1) and (cast(@rows as int) < 1) )
begin
	PRINT 'Parameter #2 i.e @rows should be  >0 '
	RETURN(1)
end
else if ( (isnumeric(@rows) != 1 ) and  @rows <> "ALL")
begin
	PRINT 'Parameter #2 i.e @rows should be  "ALL" or >0 '
	RETURN(1)
end




declare @table varchar(30) , @owner varchar(30) , @db varchar(30)

select @table = parsename(@table_name,1) 
select @owner = parsename(@table_name,2) 
select @db =  parsename(@table_name,3)
select @db = isnull(@db,db_name()) 



if @db is not null and @db <> db_name()
begin
	PRINT "You are executing sp_xfrdata from <" + db_name() + "> but you are referring to an object in <" + @db + ">"
        PRINT "Note: Compile the Stored Procedure in <" + @db  + "> and then try ! "
	RETURN(1) 
end


if not exists (select * from  sysobjects where name = @table and uid = user_id(isnull(@owner,"dbo") ) )
begin
	PRINT 'Table <'+ @table_name + '> Not Found in Database <' + db_name() + '> on <' + @@servername+ '>'
        RETURN(1)
end

if (@rows  = "ALL")
begin
select @rows = "100 PERCENT"
end

declare @istring varchar(8000), @vstring varchar(8000), @estring varchar(8000),@max_colid smallint

SET NOCOUNT ON

create table #t1
(
column_name varchar(30) null ,
column_id smallint  ,
type varchar(15) null ,
computed varchar(5) null,
length int ,
prec tinyint,
scale tinyint,
nullable varchar(10) ,
TrimTrailingBlanks varchar(10) ,
FixedLenNullInSource varchar(10) ,
Iden bit
)

insert #t1 
select
			'Column_name'			= cast(name as char(25) ),
                        'Column_id'                     = colid ,
			'Type'					= cast( type_name(xtype) as char(15) ),
			'Computed'				= case when iscomputed = 0 then "No" else "yes" end,
			'Length'				= convert(int, length),
			'Prec'					= case when charindex(type_name(xtype), 'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney') > 0
										then convert(char(5),ColumnProperty(id, name, 'precision'))
										else '     ' end,
			'Scale'					= case when charindex(type_name(xtype), 'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney') > 0
										then convert(char(5),OdbcScale(xtype,xscale))
										else '     ' end,
			'Nullable'				= case when isnullable = 0 then "no" else "yes" end,
			'TrimTrailingBlanks'	= case ColumnProperty(object_id(@table_name), name, 'UsesAnsiTrim')
										when 1 then "no"
										when 0 then "yes"
										else '(n/a)' end,
			'FixedLenNullInSource'	= case
						when type_name(xtype) not in ('varbinary','varchar','binary','char')
							Then '(n/a)'
						When status & 0x20 = 0 Then "no"
						Else "yes" END,
			"Identity" = COLUMNPROPERTY( OBJECT_ID(@table_name),name,'ISIDENTITY')
		from syscolumns where id = object_id(@table_name) order by colid

select @max_colid = max(column_id) from #t1

select @istring = "INSERT " + @table_name + " ( " 

select @istring = @istring + ltrim(rtrim(column_name))+", "  from #t1
where (type <> "timestamp" ) --and Iden = 0

select @istring  = substring(@istring,1,len(@istring)- 1 )  + " )"

--select @istring
--select * from #t1

if exists(select * from #t1 where type ="image" or type="text" )
begin
	print "/*Insert statements for " + db_name()+"."+ isnull(@owner,"dbo")+"."+@table + "*/"
        print "Sorry, No Can Do it has a column with the image/text datatype " + char(10) +char(10)
	RETURN(1)
end

---------------------------

select @vstring =  @vstring + A.c1 + "+"
from
(
select
c1 =  
CASE 

WHEN ( (type = "nvarchar"  or type = "varchar" or type = "char") and (column_id < @max_colid)  )
THEN 'CASE WHEN ('+ rtrim(column_name) + ' is null) THEN ' + "'null,'" +
' ELSE ' + 
"'" + '"' + "'+" +  rtrim(column_name) + "+'" + '",' + "'" +  'END'

WHEN ( (type = "nvarchar"  or type = "varchar" or type = "char") and (column_id = @max_colid)  )
THEN 'CASE WHEN ('+rtrim(column_name)+ ' is null) THEN ' + "'null'" +
' ELSE ' + 
"'" + '"' + "'+" +  rtrim(column_name) + "+'" + '",' + "'" +  'END'


WHEN (type = "nvarchar"  or type = "varchar" or type = "char" ) THEN 'CASE WHEN ('+rtrim(column_name)+ ' is null) THEN ' + "'null,'" +
' ELSE ' + 
"'" + '"' + "'+" +  rtrim(column_name) + "+'" + '",' + "'" +  'END'



WHEN (type = "smallint" or type ="tinyint" or type ="int" or type = "bit" or type ="decimal" or type="numeric" )
THEN 'CASE WHEN ('+rtrim(column_name)+ ' is null) THEN ' + "'null'" +
' ELSE ' + 
 'cast(' + rtrim(column_name) + ' as varchar(15) ) END' + "+'" + "," + "'"


WHEN ( (type = "datetime" or type = "smalldatetime") and (column_id < @max_colid) )
THEN 'CASE WHEN ('+rtrim(column_name)+ ' is null) THEN ' + "'null,'"  +
' ELSE ' + 
"'" + '"' + "'+" + 'cast('+  rtrim(column_name)+' as varchar(20) )' + "+'" + '",' + "'" +  'END'

WHEN ( (type = "datetime" or type = "smalldatetime") and (column_id = @max_colid))
THEN 'CASE WHEN ('+rtrim(column_name)+ ' is null) THEN ' + "'null'"  +
' ELSE ' + 
"'" + '"' + "'+" + 'cast('+  rtrim(column_name)+' as varchar(20) )' + "+'" + '",' + "'" +  'END'

WHEN (type = "money" or type="smallmoney" ) THEN 'CASE WHEN ('+rtrim(column_name)+ ' is null) THEN ' + "'null'" +
' ELSE ' + 
 'cast(' + rtrim(column_name) + ' as varchar(20) ) END' + "+'" + "," + "'"

WHEN (type = "float" or type ="real" )
THEN 'CASE WHEN ('+rtrim(column_name)+ ' is null) THEN ' + "'null'" +
' ELSE ' + 
 'cast(' + rtrim(column_name) + ' as varchar(30) ) END' + "+'" + "," + "'"


END
from #t1
where (type <> "timestamp" ) -- and Iden = 0
) as A

--select @vstring "before"

--select substring(@vstring,len(@vstring)-1,len(@vstring) )

if (substring(@vstring,len(@vstring)-1,len(@vstring) ) = "'+" )
begin
 select @vstring = "'"+ @istring + char(10) +
 'VALUES(' +"' +" + substring(@vstring,1,len(@vstring)-3)+ "'"   + "+'" + ")" + "'" 
end
else
begin
  select @vstring = "'"+ @istring + char(10) + 
 'values(' +"' +" + substring(@vstring,1,len(@vstring)-3)+ "'"   + "+'" + ")" + "'" 
  select @vstring = substring(@vstring,1,len(@vstring)-8) + "' " +'END' + "+')" + "'"
end

--select @vstring "after"


if (@rows = "100 PERCENT" )
begin
	print "/*Insert statements for all the rows from " + db_name()+"."+ isnull(@owner,"dbo")+"."+@table + "*/"
	if (@where IS NOT NULL) 
	begin
	print "/* WHERE CONDITION USED IS "+ @where + " */"
	end
	if exists(select * from #t1 where iden = 1) 
	print "SET IDENTITY_INSERT  " + isnull(@owner,"dbo")+"."+@table + " ON " 
end
else
begin
	print "/* Insert statements for the top " + @rows + " rows from " + db_name()+"."+ isnull(@owner,"dbo")+"."+@table + " */"
	if (@where IS NOT NULL) 
	begin
	print "/* WHERE CONDITION USED IS "+ @where + " */"
	end

	if exists(select * from #t1 where iden = 1) 
	print "SET IDENTITY_INSERT  " + isnull(@owner,"dbo")+"."+@table + " ON "
end




select @estring =  "SELECT top " + @rows  +  @vstring + " FROM " + @table_name + " " +
ISNULL(@where,"")


exec (@estring)

	if exists(select * from #t1 where iden = 1) 
	print "SET IDENTITY_INSERT  " + isnull(@owner,"dbo")+"."+@table + " OFF  " 


return (0) -- sp_xfrdata
----