Using Identity Insert on a linked server We have a database hosted in Microsoft Azure and using this database connection details I have created a linked server object in SSMS on our on-premise SQL box and I can insert into it as following syntax:

insert into [Azure].[budget].dbo.Bill


I wanted to set identity insert on and have tried the following:

SET IDENTITY_INSERT [Azure].[budget].dbo.Bill ON
insert into [Azure].[budget].dbo.Bill 


I was then informed that I would have to use dynamic SQL for this so I changed to the following:

EXEC [Azure].[budgetenergy].dbo.sp_executesql N'SET IDENTITY_INSERT Bill ON;insert into Bill ([FIELD LIST]) select [FIELD LIST] from [DUOS3NF].dbo.bill where dateOfIssue &gt; ''10 mar 2015''';


The [DUOS3NF] is a database that is on the SMSS box it is not a linked server. Using this approach is there any way that I can insert into the linked server the results of the select statement of another database?

When I execute the query the error it does not recognise the database.
Linked servers aren't usually a good idea from a security perspective. See distribution statistics section here

I think SSIS would provide a better way of doing this.