0

I would like to know what would be a safe way to insert data over a linked/local server into a empty copy of the database(This database will have the same table structure) using "INSERT INTO SELECT"? I will be getting data from the local server and this data will be inserted over a linked server or local server to an archive DB.

This process needs to happen through Dynamic SQL as I am writing a Generic script.

If you have a sample script that you can supply to me then I would appreciate it.

EXAMPLE

EXEC [server_name].[OI_OAF_Archive_20210218_20210222].[dbo].sp_executesql N'SET IDENTITY_INSERT [OI_OAF_Archive_20210218_20210222].dbo.[ENT_Entry] ON'
EXEC [server_name].[OI_OAF_Archive_20210218_20210222].[dbo].sp_executesql N'ALTER TABLE [OI_OAF_Archive_20210218_20210222].dbo.[ENT_Entry] NOCHECK CONSTRAINT ALL'
IF (EXISTS (SELECT * FROM [OI_OAF_Archive_20210218_20210222].INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'ENT_Entry'))
BEGIN
    PRINT 'Inserting into the table ENT_Entry'
    INSERT INTO [server_name].[OI_OAF_Archive_20210218_20210222].dbo.[ENT_Entry] (entryID,details,createdByID,createdDate,lastModifiedByID,lastModifiedDate,localityID,refNumber,entryDate,gps,reloadEscStep,createdAt,reloadOccurrenceChecklistRule) SELECT entryID,details,createdByID,createdDate,lastModifiedByID,lastModifiedDate,localityID,refNumber,entryDate,gps,reloadEscStep,createdAt,reloadOccurrenceChecklistRule FROM OPENQUERY([server_name], 'SELECT entryID,details,createdByID,createdDate,lastModifiedByID,lastModifiedDate,localityID,refNumber,entryDate,gps,reloadEscStep,createdAt,reloadOccurrenceChecklistRule FROM TMP_Archive_ENT_Entry_70CDC91A ');
END
EXEC [server_name].[OI_OAF_Archive_20210218_20210222].[dbo].sp_executesql N'SET IDENTITY_INSERT [OI_OAF_Archive_20210218_20210222].dbo.[ENT_Entry] OFF'
EXEC [server_name].[OI_OAF_Archive_20210218_20210222].[dbo].sp_executesql N'ALTER TABLE [OI_OAF_Archive_20210218_20210222].dbo.[ENT_Entry] CHECK CONSTRAINT ALL'

This Example is where I INSERT the DATA from The archive server, but I want to insert it from the local server into the archive server, would that be possible?

Wimpie Norman
  • 27
  • 5
  • It's not the `INTO` that'll be the security problem, it'll be the dynamic SQL that will be, if it's poorly written. Show us what you actually have already. – Thom A Feb 15 '21 at 09:56
  • @Larnu - I have added an example in the question – Wimpie Norman Feb 15 '21 at 10:08
  • `[' + QUOTENAME(@ArchiveServer) + ']` isnt' correct. `QUOTENAME` both delimit identifies *and* escapes. You don't need the literal brackets. – Thom A Feb 15 '21 at 10:09
  • Noted @Larnu, thank you I will remove the literal brackets. Would I need to run the INSERT INTO SELECT statement from the archive server or would it be possible to execute from the local server? – Wimpie Norman Feb 15 '21 at 10:12
  • *" Would I need to run the INSERT INTO SELECT statement from the archive server or would it be possible to execute from the local server? "* Either, just write your SQL accordingly. – Thom A Feb 15 '21 at 10:22
  • Thank you @Larnu, I will run my testing and revert back. – Wimpie Norman Feb 15 '21 at 10:42
  • @Larnu - I am currently having some trouble with this receiving some weird error. Would you know what this means "OLE DB provider "SQLNCLI11" for linked server "SERVER_NAME" returned message "Deferred prepare could not be completed."." I have updated my example code above. – Wimpie Norman Feb 22 '21 at 07:59
  • Any of those lines could be causing that error. At least tell us *which*... – Thom A Feb 22 '21 at 09:01
  • Are you sure that is the entire error? This link has the same error but it also includes a more useful error (invalid object name) https://stackoverflow.com/questions/15158017/deferred-prepare-could-not-be-completed-error-when-using-local-database-as-lin please post the entire error message – Nick.Mc Feb 22 '21 at 09:23

0 Answers0