Solved

How can I insert data from one field to 9 other fields?

Posted on 2009-04-02
8
178 Views
Last Modified: 2012-05-06
Hello again,

I'm inserting data from the old database, now I got stuck, there is a field on the other one that needs to break into 9 fields on the new one.

I have no idea how do it..Can someone please help me:

here is the fields and the data source I have:

Old database:
RC_DB2.dbo.rc_Referrals.ReasonsForReferral (int 4)

New database:  (nvarchar 25)
RC_DB3.dbo.tbl_Referral.ReasonsForReferralAbscesses
RC_DB3.dbo.tbl_Referral.ReasonsForReferralCellulitis
RC_DB3.dbo.tbl_Referral.ReasonsForReferralDiabetes
RC_DB3.dbo.tbl_Referral.ReasonsForReferralFractures
RC_DB3.dbo.tbl_Referral.ReasonsForReferralMusculoskeletal
RC_DB3.dbo.tbl_Referral.ReasonsForReferralPneumonia
RC_DB3.dbo.tbl_Referral.ReasonsForReferralPostSugical
RC_DB3.dbo.tbl_Referral.ReasonsForReferralSkinUlcer
RC_DB3.dbo.tbl_Referral.ReasonsForReferralViralSyndrome


Thanks,
Caliu
0
Comment
Question by:Romacali
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 24053182
Just select the field 9 times in your insert statement

Insert INto Table
(RC_DB3.dbo.tbl_Referral.ReasonsForReferralAbscesses,
RC_DB3.dbo.tbl_Referral.ReasonsForReferralCellulitis,
RC_DB3.dbo.tbl_Referral.ReasonsForReferralDiabetes,
RC_DB3.dbo.tbl_Referral.ReasonsForReferralFractures,
RC_DB3.dbo.tbl_Referral.ReasonsForReferralMusculoskeletal,
RC_DB3.dbo.tbl_Referral.ReasonsForReferralPneumonia,
RC_DB3.dbo.tbl_Referral.ReasonsForReferralPostSugical,
RC_DB3.dbo.tbl_Referral.ReasonsForReferralSkinUlcer,
RC_DB3.dbo.tbl_Referral.ReasonsForReferralViralSyndrome)
Select RC_DB2.dbo.rc_Referrals.ReasonsForReferral as R1, RC_DB2.dbo.rc_Referrals.ReasonsForReferral as R2, RC_DB2.dbo.rc_Referrals.ReasonsForReferrall as R3, RC_DB2.dbo.rc_Referrals.ReasonsForReferrall as R4, RC_DB2.dbo.rc_Referrals.ReasonsForReferrall as R5, RC_DB2.dbo.rc_Referrals.ReasonsForReferrall as R6, RC_DB2.dbo.rc_Referrals.ReasonsForReferrall as R7, RC_DB2.dbo.rc_Referrals.ReasonsForReferrall as R8, RC_DB2.dbo.rc_Referrals.ReasonsForReferrall as R9
0
 
LVL 78

Expert Comment

by:arnold
ID: 24053217
Does the entry from the old needs to end up as a reference in all in the new or does it need to only appear in the right one on the new database?
0
 

Author Comment

by:Romacali
ID: 24053478
It needs to appear in the right fields. I need to break the data there and add to the new ones.
Like I have 1,99 in one fields and it should be:
RC_DB3.dbo.tbl_Referral.ReasonsForReferralAbscesses => 1
RC_DB3.dbo.tbl_Referral.ReasonsForReferralOther => 99

Any idea?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 78

Expert Comment

by:arnold
ID: 24053661
You need to use SSIS to process the data from the old, split the columns you need into variables and then generate an insert.
It is not a simple process.  Depending on the structure of the old and the new database, you would be better off not trying to migrate data a table at a time, but to aggregate the whole data and then distribute it in the new database.

Are you normalizing the new database based on the data in the old, or are you only altering the structure of the new database?
0
 

Author Comment

by:Romacali
ID: 24054326
The databases are in the server. And Once I transfer all the users I won't need the old one anymore.
I read something about parsing data into multiple fields. Is it something I can do? it is ok to insert numbers in the other fields. I just need to parse the data correctly.

Please help
0
 
LVL 78

Expert Comment

by:arnold
ID: 24054396
You need to use SSIS to get the data and have the right logic to then transfer
i.e.
If the column has
reason1,reason2,reason3,reason4,reason5,reason6
This makes it simpler, but if you have
reason1,reason5
reason2,reason3
reason1,reason4,reason6
In this case you likely have another column that maintains which reasons these are which makes matters more complicated.

There are no simple solutions to this since everything depends on the data as well as the new relationships.
0
 

Author Comment

by:Romacali
ID: 24061778
hi Arnold,

Do you have any link that can help me with this SSIS? I'm very new to SQL and I'm completely lost...
0
 
LVL 78

Accepted Solution

by:
arnold earned 500 total points
ID: 24063054
The following link might be the example you are looking for.
http://www.aspfree.com/c/a/MS-SQL-Server/Transferring-a-Database-Using-the-SSIS-Designer/
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select - Finding chars in a column 2 70
Sql Permission 6 71
T-SQL to Update Table Dynamically 2 61
SQL- GROUP BY 4 21
In this article I will describe the Copy Database Wizard 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

713 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