0

Due to limited size of distribution db I wish to shift or copy data from there to another database using script.Is there any script that copies data from distribution database(System Database) to another user created database using a job at specific interval but newly copied data should append to previously copied data?

I have tried to copy data using insert script that select values from source db and copies into destination db where my source is system db table and destination is user defined db table.But it showed me this error: Msg 8101, Level 16, State 1, Line 6 An explicit value for the identity column in table 'classicmodels.dbo.MStracer_tokens' can only be specified when a column list is used and IDENTITY_INSERT is ON.

SET IDENTITY_INSERT classicmodels.[dbo].MStracer_tokens ON

INSERT INTO classicmodels.[dbo].MStracer_tokens

SELECT *
FROM   [distribution].[dbo].MStracer_tokens

SET IDENTITY_INSERT classicmodels.[dbo].MStracer_tokens OFF

I want the same data from source to be copied on to destination.Destination only stores this copied data.

Zendie
  • 1,176
  • 1
  • 13
  • 30
Ankita Potdar
  • 67
  • 1
  • 9
  • So ... what part of that error message do you not understand. You must specify a column list in the insert statement. It is also a general best practice to provide an column list regardless of the use of the identity_insert feature. – SMor Sep 10 '19 at 13:12
  • Thank you for helping me and my team. Query executed successfully. – Ankita Potdar Sep 11 '19 at 17:35
  • We would like to see your co-operation even in future. – Ankita Potdar Sep 11 '19 at 17:36

0 Answers0