Solved

migrating data from one table to anoter in SQL server 2005

Posted on 2011-09-22
4
175 Views
Last Modified: 2012-06-22
Hi,

I have a set of tables in my source database.
The naming convention for the tables are:

XXXEmployee2010
XXXSalary2010

I need to migrate data from XXXEmployee in source database to Employee table in my destination database.
eg:

Declare @Src_Table nvarchar(25)
, @Des_Table nvarchar(25)

Set @Src_Table = 'XXXSalary'
set @Des_Table = Substring('XXXSalary',3,6)

Insert into DES_DB_dbo.@des_Table
Select * From  Src_DB.dbo.@src_Table

Q1.   what is the correct way of writing above inset into syntax using parameters @des_Table and @src_Table?

Q2.  How can I write a script in sql that goes through the tables in source database and one by one transfer them to destination database using query above?

Many thanks in advance

0
Comment
Question by:shmz
  • 3
4 Comments
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36585140
1. you will have to write dynamic sql to execute the insert statment with parameters

2. check out the below code
declare @strSql nvarchar(1000)
declare cur cursor for 
select  'Insert into DES_DB_dbo..' + Substring(TABLE_NAME,3,6) + 
		' select * from Src_DB..' + TABLE_NAME StrSql  
from test.INFORMATION_SCHEMA.TABLES 

open cur
fetch next from cur into @strSql
while @@FETCH_STATUS = 0
begin 
	print @strSql
	fetch next from cur  into @strSql
end
close cur
deallocate cur

Open in new window

0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36585143
change the test.INFORMATION_SCHEMA.TABLES  to Src_DB.INFORMATION_SCHEMA.TABLES
0
 
LVL 9

Accepted Solution

by:
sachinpatil10d earned 500 total points
ID: 36585149

declare @strSql nvarchar(1000)
declare cur cursor for 
select  'Insert into DES_DB_dbo..' + Substring(TABLE_NAME,3,6) + 
		' select * from Src_DB..' + TABLE_NAME StrSql  
from Src_DB.INFORMATION_SCHEMA.TABLES 

open cur
fetch next from cur into @strSql
while @@FETCH_STATUS = 0
begin 
	exec sp_executesql @strSql
	fetch next from cur  into @strSql
end
close cur
deallocate cur

Open in new window

0
 

Author Closing Comment

by:shmz
ID: 36595432
thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question