Copying Data from one Database to Another

,

This script will be helpful to only those people who are
having identical database with different names.
This script can be used to copy single or multiple tables from one database to another
The parameters @tname , source database and target database are important for the script to work.

In the script below I have considered two databases Northwind and pubs . The table to transfer is jobs.

declare 
@tname varchar(100) ,
@target_db varchar(100),
@table_name varchar(50),
@column_names varchar(50),
@colstat smallint ,
@cum_column_names varchar(5000),
@sqlstring nvarchar(2000)

----------------------------------------------------------------------------
--------- Please set the parameters below to start the copy process --------
set @tname = 'jobs'
USE pubs	-- specify the source data base
set @target_db = 'Northwind'
----------------------------------------------------------------------------
declare table_cursor cursor for
select name from sysobjects where name like @tname
--select name from sysobjects where name = 'soltprod'
open table_cursor
fetch next from table_cursor into  @table_name
while @@fetch_status <> - 1
begin
	set @cum_column_names = ''
-- cursor for columns 
	declare column_cursor cursor for
	select name , colstat from syscolumns where id = (select id from sysobjects where name = @table_name) 
	order by colorder
	open column_cursor
	fetch next from column_cursor into  @column_names , @colstat
	while @@fetch_status <> - 1
	begin
		if @colstat <> 1 
			set @cum_column_names = @cum_column_names + @column_names + ','
		fetch next from column_cursor into  @column_names , @colstat
	end
	close column_cursor
	deallocate column_cursor


	set @sqlstring = 'insert into ' + @target_db + '..' + @table_name + '(' + left(@cum_column_names,len(@cum_column_names)-1) + 
			')  select ' + left(@cum_column_names,len(@cum_column_names)-1) + ' from ' + 
				 + @table_name
--	print @sqlstring
	execute sp_executesql @sqlstring
	--print @table_name
	--print left(@cum_column_names,len(@cum_column_names)-1)
	fetch next from table_cursor into @table_name
end

close table_cursor
deallocate table_cursor

Rate

5 (1)

Share

Share

Rate

5 (1)