I have two databases on the same SQL Server. One is used for production (used by the users), and the other is a test database used for testing. The testing database has the same tables and structure as those in the production database and most tables have a primary key.

Until recently, this client was using an extremely old SQL Server with SQL Server Enterprise Manager. Copying data from the production database to the test database was easy and straightforward using the Data Transformation feature of SQL Server Enterprise Manager.

Now the client has moved all databases to a new SQL Server and installed SQL Server Management Studio (SSMS) to maintain the databases. My problem is when I try to copy data in a table from the production database with the Import Data feature in SSMS, it fails saying it cannot copy the data into the primary field on the destination data source.

I’ve tried using SELECT and INSERT INTO statements in a SSMS query with SET INSERT_IDENTITY ON for the destination table as the first statement of the query and OFF as the last statement of the query.

But when I run this query, I get an error message saying that when using INSERT_IDENTITY in the query, I must supply the list of columns to be imported. Most of the tables have have too many columns to make this feasible.

How can I use SSMS to import tables when the majority, if not all tables, contain a primary key?

This a feature that will be used a lot, so any help would be greatly appreciated.

Larry