June 17, 2012 at 3:38 pm
I would like to know what is the syntax to move existing data from one table to another, for example tblAsset which contains Policy No (INT), PolicyStart(DateTime) and PolicyEnd(DateTime) Fields, I would like to refine this table and place this data in a new table of its own, e.g. tblPolicyInfo within the database.
What would the syntax be to move the fields from the tblAsset to tblPolicyInfo? I will require a FK to ensure that the data remains relational so that the data is linked to the correct record. The tblAsset PrimaryKey is AssetID and the new table tblPolicyInfo will be using the PolicyID as its primary key. Correct me if I'm wrong but the best way to link these two tables together would be to have an AssetID FK in the tblPolicyInfo Table. I'd obviously need to copy across the AssetID into the FK AssetID to ensure they remain relational, so that the data is linked to the correct record?
June 17, 2012 at 10:34 pm
Lucasprss (6/17/2012)
I would like to know what is the syntax to move existing data from one table to another, for example tblAsset which contains Policy No (INT), PolicyStart(DateTime) and PolicyEnd(DateTime) Fields, I would like to refine this table and place this data in a new table of its own, e.g. tblPolicyInfo within the database.What would the syntax be to move the fields from the tblAsset to tblPolicyInfo? I will require a FK to ensure that the data remains relational so that the data is linked to the correct record. The tblAsset PrimaryKey is AssetID and the new table tblPolicyInfo will be using the PolicyID as its primary key. Correct me if I'm wrong but the best way to link these two tables together would be to have an AssetID FK in the tblPolicyInfo Table. I'd obviously need to copy across the AssetID into the FK AssetID to ensure they remain relational, so that the data is linked to the correct record?
Correct me if I have got this wrong. More than one policy may be attached to one asset....Right??
In that case, I think you should have "PolicyNo" as Primary Key in the first table which may be referenced by "Policy Id" of the new table, which would be the Foreign Key.
This is just based on the info that you provided us with. Thinkin about the overall scenario I would go by the following Structure:
Table_Assets: Asset_Id (Primary Key)
Table_Policy: Policy_No (Primary Key)
Asset_id (Foreign Key) referencing Asset_Id of Table_Assets
Table_AssetstoPolicy: Id(Primary_Key)
Asset_Id(Foreign Key) referencing Asset_Id of Table_Assets
Policy_No(Foreign Key) referencing Policy_No of Table_Policy
Hope this helps.
June 18, 2012 at 7:43 am
1. CREATE the new table schema
2. INSERT your data over to new schema
3. ALTER table(s) to create PK's on new schema
4. ALTER table(s) to create FK's
5. ALTER table(s) to remove columns from original table
June 18, 2012 at 7:43 am
Bracket that with good backups just in case;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply