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?