Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

migrating data from one table to anoter in SQL server 2005

Posted on 2011-09-22
4
Medium Priority
?
188 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 2000 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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 …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

564 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