I have 2 tables in a production database. The tables are prodDB.tblInfo and prodDB.tblInfo2. The tables share a key with column name sysid. I am looking to copy records which share the same sysid based on the value of one of the column's in prodDB.tblInfo into a new, non-production database (testDB.tblInfo3).
Scaled down example:
tblInfo contains FirstName, LastName, State, sysid. tblInfo2 contains Zip, Phone, Email, sysid.
I would like to copy the columns of tblInfo.FirstName, tblInfo.LastName, tblInfo.State and tblInfo2.Zip, tblInfo2.Phone, tblInfo2.Email into testDB.tblInfo3; however, I only want records WHERE State = 'CO'.
SELECT FirstName, LastName, State, sysid
WHERE State = 'CO'
This works fine but I want to then grab the remainder of the info (Zip, Phone, etc) from tblInfo2 of records with the same sysid