Solved

migrating data from one table to anoter in SQL server 2005

Posted on 2011-09-22
4
173 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
Comment Utility
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
Comment Utility
change the test.INFORMATION_SCHEMA.TABLES  to Src_DB.INFORMATION_SCHEMA.TABLES
0
 
LVL 9

Accepted Solution

by:
sachinpatil10d earned 500 total points
Comment Utility

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
Comment Utility
thanks
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

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…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

772 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now