Import Data from Excel into SQL Sever

So again today I faced another challenge as expected. The task for today was to import and merge data from four tables in a database located in a certain/specified server into a single table of an existing database on another server.

Connecting to the server wasn't the hard part, copying the data into an excel sheet and saving it as a CSV format wasn't either. It was importing of the data brought a headache. 

This is why I love Google. I get answers

These are the steps I followed to get the job done

Note: Create a Destination Table

1. Convert the Excel format to  xlsx  because it's the only format accepted. 
2. Connect to SQL Management studio, right-click the database , select "task" then "Import Data" 
3. Select "Microsoft Excel" as the Source  and Choose the "File Location" then Follow the Wizard steps as shown on the screen.
4. A table is created in the Database and it uses the Excel Title as the Table Name.
5. Right-click on the Table, "Script Table as" ==>  "CREATE To" to get the CREATE Format of the table for your destination Table where all the data will eventually be. Copy and Run the script in your existing table to generate column names
6. Right-Click on the Database, select "task" ==> "Generate Scripts" and Follow the Wizard to the Scripting Options page
7. Select "Advanced" on the "Types of data to script" select any option that suits what you want to do
8. Click on the file menu to add a file (the Generate script you just saved). once loaded edit the table name to point to the new Table (Destination table) and run the Query.
9. Run Select (*) FROM Destination Table to confirm it has records.
10. Repeat steps for all other Tables. 

An excel sheet can't take more than 1,048,576 rows.