Solved

migrating data from one table to anoter in SQL server 2005

Posted on 2011-09-22
4
181 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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