Migrate Excel Data to SQL

Question

I have a large table of information (around 11,000 rows, 4 columns) in Excel that uses a macro and I need to import it to an SQL server, Microsoft SQL Server Management Studio, which will be utilized by another server to get the new information.

Example: If I type into SQL:

Insert Into ENT_LINK_OBJECTS (OBJ_NAME, ENTITY_KEY, IDENTITY_KEY)
Select 'TDS-C1487-81236', ITEM_KEY, 1
From ENT_ITEM_MASTER As M
Where M.ITEM_CODE = 'TL-123'
or M.ITEM_CODE = 'TL-456'

I can then open the program which holds all this information, called Matrix, which prompts me to enter an item key and/or code and/or type etc (which has all possible files listed below it) and hit search (image 1). If I type in TL-123 to the item code section (image 2), it narrows down the files to any containing TL-123 (image 3). When i double click, I can click on many tabs, one of which is "Links". In that tab under document name the information TDS-C1487-81236(image 4). How would I go about making that happen?

(1)

(2)

Then hit ENTER

(3)

(4)

The website below is a good explanation of what I am getting at but I do not know how to implement it. What would be the most efficient way to migrate the data from my excel document to the SQL server?

http://sqlmag.com/business-intelligence/excel-macro-creates-insert-statements-easy-data-migration

Answer 1

Create a linked server or Use statement like OPENROWSET to access Excel Sheet. That would be the easiest and fastest method of accessing excel sheet via SQL.

Answer 2

Have you tried DTSWizard ? Its a GUI based tool to do so.. and should be shipped with MS SQL server