Using a Linked Server to import data from Excel

I have read a lot about linked servers being bad when there are multiple hops across servers and distributed joins.

What if I am just using a linked server to import data into SQL Server from an Excel spreadsheet?

What if that's all I am doing and the rest is being all done in SQL Server itself on a single server?

I want to avoid SSIS owing to headaches with multiple transactions and chaining them to each other (which is failing for some reason).

You can use the Import wizard to load your data. The Import/Export wizard actually uses SSIS to do the work. But if your goal is to avoid learning SSIS, then just do not save the package.

Simple way to import data into SQL Server

That MSSQLtips article walks you through setting up the import and then run the data into your SQL Server destination.

If you want to reuse the package (which is probably a good idea) there is a save option that allows you to save the SSIS package that was internally generated and use it again and again.

However, if the Linked Server approach is the way you want to go, there is a tutorial here:

Excel Import to SQL Server using Linked Servers

This is somewhat more complex to set up, but should work OK.

UPDATE: based on your comment about coordinating two transactions you might find it simpler to import the data into two staging tables. (You would most likely truncate those tables before each load.) The table Stage01 receives one load and the table Stage02 receives the other load.

Then, now that both data sets are in their own tables, you can join between Stage01 and Stage02 data sets to insert the resolved data into your operational database tables. This would likely simplify the processing of the two data sets.

StackExchange Database Administrators Q#112839, answer score: 4