Copy and paste data between databases in ArcGIS

You can copy data from a database and paste it into another database or a geodatabase through ArcGIS for Desktop.

When you move data from one database type to another, the data types used for the attributes in your table may change. For example, if you copy a table from SQL Server that has a UniqueIdentifier field, when you paste it into a PostgreSQL database, it will be a Varchar field.

Be aware that ArcGIS cannot paste data types or definitions it doesn't support. That means the table or feature class that is created in your target database will contain:

• Data types supported by ArcGIS

  Any columns that cannot be mapped to an ArcGIS data type will not be created in the feature class in the target database. See DBMS data types supported in ArcGIS for a list of supported data types.

• One spatial column

  For feature classes, the first spatial column encountered will be used; the second will not get created in the target feature class. If you want the second spatial column to be used, define a view or a query layer on the source table that only contains the second spatial column. If you create a view, you can copy it from the source database then paste it into the target database. If you define a query layer, you can export the data from the query layer to a new feature class in the target database.

• One geometry type

  If your source feature class contains multiple geometry types (points, lines, polygons, multipoints), ArcGIS will use the geometry type of the first row in the source feature class when you paste the feature class into the target database. If you want to control which geometry type is used in the target feature class, create a query layer of the source feature class and define which geometry type is to be used, then export the data from the query layer to your target database. When you do that, the feature class created in the target database will contain only records of the geometry type you defined for the query layer.

• One dimensionality

  If the records in your source feature class are defined with different dimensionality (xy, xyz, xym, xyzm), ArcGIS will use the dimensionality of the first row in the source feature class; records that do not match that dimensionality will not be created in the target feature class. If you want to specify which dimensionality to use in the target feature class, create a query layer of the source feature class and define which dimensionality is to be used. Then export the data from the query layer to the target database. When you do that, all records in the feature class created in the target database will use that dimensionality. For example, if you define the query layer to have z but not m dimensions, any records that had m dimension information in the source feature class will not have it in the target feature class. Similarly, any record that had only x,y coordinates in the source feature class will have x,y, and z coordinates in the target feature class (the z coordinate is set to 0).

• One spatial reference

  If your source feature class has a spatial reference defined that ArcGIS recognizes, the same spatial reference is used for the feature class in the target database. If the spatial reference is not defined for the feature class, ArcGIS will use the spatial reference of the first row in the table. Records that do not match that spatial reference will not be created in the feature class in the target database. If, instead, the spatial reference cannot be identified for the table or the first row (for example, if a custom spatial reference is used), the spatial reference used in the target feature class varies depending on your DBMS. See the section "How the spatial reference system is used in ArcGIS" in Spatial references for more information.

The following steps explain how to move a table, feature class, or view between databases or from a geodatabase to a database using copy and paste.

Steps:

1. Start ArcMap and open the Catalog window.
2. From the Catalog tree, make connections to both the source and target database.

   Be sure to connect to the source database as a user with privileges to access the data in the source database, and connect to the target database as a user with database privileges sufficient to create tables.

3. If necessary, prepare the data to be moved. or multiple spatial columns, on the feature class and include to determine which properties and columns will be created to the properties on the source feature class.

   If you create a query layer, proceed with the steps in to move your data to the target database.

   • If you want to move a feature class that has records with different dimensionality, geometry types, or spatial references, drag the table onto the map and modify the resultant query layer, choosing one dimensionatliy, geometry type, or spatial reference. To move the data to the target database, export the query layer. (You can't copy and paste a query layer.)
   • If the feature class has multiple spatial columns, create a database view that includes only one of the spatial columns and proceed with copying and pasting.
4. Right-click the table, feature class, or view in the source database and click Copy.
5. Right-click the target database in the Catalog tree and click Paste.

   The data in the target database is owned by the login you used to connect to it.

   Note:

   To paste data into a PostgreSQL or SQL Server database, the user connected to the target database must have a schema with the same name as the database user name.

Alternatively, you can choose the table, feature class, or view in the source database and drag it to the target database. This drag-and-drop procedure performs the same actions as a copy and paste procedure.
6. If you are moving a feature class, the Data Transfer dialog box opens so you can choose which spatial type to use for the spatial column in the destination database. Click in the Config Keyword column, choose a spatial type from the drop-down list, then click OK.

The new table or feature class is created in your destination database.