IBM Support

How to copy SQL table (for example XSTAGEFACT) from source database to target (different) database

Technote (troubleshooting)


Problem(Abstract)

Customer would like to perform some testing (using a test database). As part of this, they would like to copy some information from their production (source) database into their test (target) database.
- Specifically, they only want to copy a specific database table.

Example: Customer would like to test 'import from staging tables' functionality.
- For this they would like to copy over the table 'XSTAGEFACT'.

How can they copy over a database table (from the source to target SQL database)?

Cause

There are many different methods to export data from source to target Controller database.

  • TIP: The most popular method is explained inside separate IBM Technote #1372672.

This Technote is specifically related to the rare scenario where only a single database table is needed.

  • Most likely, this might be for testing import from staging tables (which requires the data to reside inside the table 'XSTAGEFACT')

Environment

Microsoft SQL.

Resolving the problem

There are many possible methods to copy data from source to target database.

  • This Technote demonstrates how to use SQL Server Management Studio to use the 'Import and Export Wizard'

Example:

The following example is based on Microsoft SQL 2016:

1. Logon to the source SQL server

2. Launch SQL Server Management Studio

3. Right-click on the 'source' database

4. Click "Tasks - Export Data":

.
Click Next

5. Choose "SQL Server Native Client 11.0" as your data source:


6. Ensure that your servername, authentication and database name are correct.

7. Choose destination: Flat File Destination
8. Enter a suitable file path/name, for example: C:\UTILS\Globe_XSTAGEFACT_demo.txt

9. Click Next, then Next
10. Choose the relevant table, for example: [fastnet].[xstagefact]


11. Click "Next", "Next", "Finish".

You should get a success message similar to:


=============================================

12. Logon to the target SQL server

13. Launch SQL Server Management Studio

14. Right-click on the 'target' database

15. Click "Tasks - Import Data"

16. Click Next

17. Choose "Flat File Source"

  • Fill in the other relevant sections, for example:

18. Click Next.
19. Choose "SQL Server Native Client 11.0" as your destination:
20. Choose the correct destination server, authentication and database

    - For example:


21. Check that the source/destination mappings look correct, for example:


22. Click Next, Nex t , Finish.

You should get a success message similar to:

Related information

1372672 - How to use 'Export Data' and 'Export Structur

Historical Number

1031069

Document information

More support for: Cognos Controller
Controller

Software version: 10.3

Operating system(s): Windows

Reference #: 1370812

Modified date: 16 May 2011