Solved

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

Posted on 2009-04-02
8
179 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

Suggested Solutions

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…
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…

710 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