SQL Server is experiencing problems with data replication transfer in tables

During the previous work, the company requested that some of the initialized underlying data in SQL Server be fully replicated to another data table with the same table structure, when the first response wrote the SQL statement as follows: Insert into Table1 (FILED1,FILED2) Select Filed1,filed2 from Table2. Execution Success!!!

Look carefully, the data is all messed up (ID is the self-increment ID, but the ID is not necessarily continuous ).

Then the B scheme was adopted:

The self-increment of the ID in the Table1 is canceled, the data is inserted and then added to the self-growth (problem solving).

but When the Table1 is dynamically generated by the stored procedure and requires the data to be fully copied into it, Plan B fails!

So the C scheme, the specific code is as follows:

Create Table1

CREATE TABLE [dbo]. [Table_1] (
[ID] [int] IDENTITY (*) not NULL,
[Filed1] [varchar] () NULL,
[Filed2] [varchar] () NULL,
CONSTRAINT [pk_table_1] PRIMARY KEY CLUSTERED
(
[Id] ASC
) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = ON) O N [PRIMARY]
) on [PRIMARY]

GO

Then: Insert into Table1 (filed1,filed2) Select Filed1,filed2 from Table2 (this is definitely not a correct notation, self-increment ID problem)

thus: SET IDENTITY_INSERT [Table_1] on INSERT to Table1 (id,filed1,filed2) Select Id,filed1,filed2 from Table1 SET IDENTITY _insert [Table_1] Off (SET identity_insert [table_1] on | | OFF allows an explicit value to be inserted into the identity column of the table. )

Execute : because it does not exist or you do not have the required permissions

Then add: database name. dbo.

SET identity_insert database name. dbo. [Table_1] On insert to Table1 (ID,FILED1,FILED2) Select Id,filed1,filed2 from Table1 SET identity_insert database name. dbo. [Table_1] off execution succeeded!

Here, solve the problem! Work often encounter such problems, I hope to be helpful to everyone!

SQL Server is experiencing problems with data replication transfer in tables