I have a pair of linked SQL servers: ServerA and ServerB. I have the following statement I wish to execute:
INSERT INTO [ServerB].[data_collection].[dbo].[table1]
SELECT * FROM [ServerA].[data_collection].[dbo].[table1]
However, as I was informed in a previous question I asked here, it turns out that the implicitly called 'sp_cursor' procedure cannot handle the 766 columns I am trying to copy from ServerA to ServerB, despite there being only a single row in the table.
In the aforementioned previous question, I was told that the workaround is to set the access mode to be "Table or view - fast load". Unfortunately, this is slightly beyond my limit of SQL knowledge. In the comments of the aforementioned question's answer, I was advised the following:
To pull this off it looks like you're going to have to do your work in an SSIS package. The key is that under the hood an INSERT BULK is being executed, as opposed to SP_cursor (which was producing error). According to this MSDN question there is no way to access it directly - it is only accessible via the Bulk Insert API's.
Again, this is beyond the limit of my SQL knowledge. So how does one get from where I am now — having only a pair of linked servers — to the point where I can make use of the INSERT BULK queries?
SSIS is complex because of how feature rich it is, and if you don't feel comfortable with it I wouldn't recommend going that route. However since this is a straight snatch and grab, I think you could probably do it by using the import export wizard (basically a wizard for very simple SSIS packages). Detailed instructions can be found here.
You select your source DB and table, then your destination DB and table, map the columns if needed, and execute it to transfer the data. You can even save the process as an SSIS job. Good luck and hope this helps.