Copying data from one table to another (same SQL Server 2008 database)
Posted on 2014-07-15
I had to change a certain field definition (nvarchar(3000) to nvarchar(max)) but I could not do it in the table (say tableName1) "to itself". Therefore I created a second table (say tableName2), identical to the first one but this field defined in the new way.
When I try to:
insert into dbo.tableName2
select * from dbo.tableName1
I get the following error message:
"An explicit value for the identity column in table 'dbo.tableName2' can only be specified when a column list is used and IDENTITY_INSERT is ON."
What should I do to solve this issue? Important: I want both tables to have their primary key with identical values, that is, I would like to have tableName1 data fields copied EXACTLY in the corresponding data fields of tableName2.