Transfer rows from a single table to multiple

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Transfer rows from a single table to multiple

Author

Topic

ronedin
Starting Member

29 Posts

Posted - 2007-10-19 : 06:45:56

Hi,

I want to transfer rows from one database to another.
Source

My Source tables in database 'Staging'

CustomersEng
Name
Age
StCode
CityCode
InternalID

CustomersSpanish
Name
Age
StCode
CityCode
InternalID

CustomersEng and CustomersSpanish contains a replica..only difference being that 'Name' in the CustomerSpanish table is written in spanish language. The Stcode, citycode and Internalid together makes the row unique.

Sample script
Create table #CustomersEng (name nvarchar(20),age int, st_code int,
citycode int, internalid int)
Insert into #CustomersEng values('Aron',23,1,1,12);
Insert into #CustomersEng values('Cathy',28,1,1,13);
Insert into #CustomersEng values('Zarog',33,1,1,14);
Insert into #CustomersEng values('Michale',25,1,2,12);
Insert into #CustomersEng values('Linda',43,1,2,13);
Insert into #CustomersEng values('Burt',53,1,2,14);

Create table #CustomersSpanish (name nvarchar(20),age int, st_code int,
citycode int, internalid int)
Insert into #CustomersSpanish values('Arona',23,1,1,12);
Insert into #CustomersSpanish values('Cathylo',28,1,1,13);
Insert into #CustomersSpanish values('Zarogo',33,1,1,14);
Insert into #CustomersSpanish values('Michalo',25,1,2,12);
Insert into #CustomersSpanish values('Lindalo',43,1,2,13);
Insert into #CustomersSpanish values('Burto',53,1,2,14);

---------------------------------------------------------
Destination tables in database 'CMO'

CustomerMaster
CustomerId (identity) (pk)
StCode
CityCode
InternalID

CustomerDetails [customerid and languagecode together are primary keys]
CustomerId (fk)
Name
Age
languagecode

Language
languagecode (pk)
languagename

SCRIPTS
Create table #CustomerMaster (customerid int identity(1,1), st_code int,
citycode int, internalid int)

Create table #CustomerDetails (customerid int,name nvarchar(20),age int,
languagecode int)

Create table #CustLanguage (languagecode int, languagename varchar(10))
Insert into #CustLanguage VALUES (1,'English')
Insert into #CustLanguage VALUES (2,'Spanish')

EXPECTED OUTPUT
CustomerMaster
1 1 1 12
2 1 1 13
3 1 1 14
4 1 2 12
5 1 2 13
6 1 2 14

CustomerDetails
1 Aron 23 1
1 Arona 23 2
2 Cathy 28 1
2 Cathylo 28 2
3 Zarog 33 1
3 Zarogo 33 2
4 Michale 25 1
4 Michalo 25 2
5 Linda 43 1
5 Lindalo 43 2
6 Burt 53 1
6 Burto 53 2

how do i transfer? thanks

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-10-20 : 05:12:19

Are the databases on the same server?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources