Transferring Data from one table to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Transferring Data from one table to another

Author

Topic

rustynails28
Starting Member

12 Posts

Posted - 2012-06-17 : 17:29:16

Hi Everyone, 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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 22:04:39

for copying you can use select ...into...syntax or insert... select but since you want to have different pk i would suggest latter

so first create new table same as tblAsset and then use


INSERT tblPolicyInfo (AssetID,PolicyNo,PolicyStart,PolicyEnd)
SELECT AssetID,PolicyNo,PolicyStart,PolicyEnd
FROM tblAsset

for keeping referential integrity you can use FK relationship as you explained

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources