I would suggest before moving this data deleting all records in the HQMS_Audit and HQMS_Audit_Data tables as they can contain large numbers of records that are not required.
DELETE FROM [dbo].[HQMS_Audit_Data]
DELETE FROM [dbo].[HQMS_Audit]
1. Modify and run the following query to find out which tables you would like to migrate.
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, NULL, NULL)
WHERE name = 'Type' and objname like 'HQMS_%' and
(value = 'TRX' OR
value = 'OPR' OR
value = 'SETUP' OR
value = 'MSTR')
order by objname
TRX are transaction tables
OPR are operational data tables (imported data from GP + records added manually)
SETUP are setup tables. (most times you DO NOT want to script the HQMS_Setup table)
MSTR are all the master tables with data setups like inspections and tests etc.
2. Click on the database where the records currently reside and then Tasks, Generate Scripts
3. Click next
4. Pick "Select specific database objects"
5. Select all of the HQMS tables you want to copy over from the query in step 1
Unfortunately in this window there is no filter capability. You will need to click on each required table you want to move to the new database.
6. Click Next
7. Click the Advanced button in the top right of the screen
8. Scroll down to almost the very bottom of the window that comes up and change 2 settings - Script Use Database to False and Types of data to script to DATA ONLY.
9. Click ok
10. You can change any of the Advanced settings that you want such as where the file gets saved etc. Click ok to run and create the script.
11. Execute the script on the database you wish to move the data to.
12. Test the migration