Solved

migrating data from one table to anoter in SQL server 2005

Posted on 2011-09-22
4
178 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 4 178
Unable to save view in SSMS 21 73
How can i get data when i use where clause with group by? 3 41
date diff with Fiscal Calendar 4 57
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

820 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