Solved

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

Posted on 2009-04-02
8
177 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 77

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 77

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 77

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 77

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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 67
Order by but want it in specific order 2 34
Bubble user-defined Sql RAISERROR(...) to c# exception 14 189
Grid querry results 41 80
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

840 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