Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-04-02
8
Medium Priority
?
183 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 80

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 80

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 80

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 80

Accepted Solution

by:
arnold earned 1500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

824 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