Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

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

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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 79

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 79

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 79

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 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