Microsoft SQL Server is the database management system from Microsoft. Today, am going to show you how to:
- Create a Database
- Create a Table
- Import data from a spreadsheet application (Microsoft Excel)
Before achieving the results above, get a copy of SQL and Data: The step-by-step guide you need in learning how to manipulate and analyze data and information through SQL data analytics for beginners book to become proficient in creating, manipulating data and database objects in Microsoft SQL Server, PostgreSQL and Microsoft Access.
Make sure to update the copy of Microsoft SQL Server before starting any project as you might experience unexpected behaviour. Without further ado, let’s open Microsoft SQL Management Studio and follow the procedures as stated below:
1) Connect to the Server making sure the server type is database engine, Server name is the COMPUTERNAME/SQLEXPRESS. The Authentication can be Windows or SQL Server (whichever you choose during the installation of SQL Server. Just make sure you can remember the password if using SQL Server Authentication).
2) Click the Connect Button (See Fig 1)
3) Create the database by right-clicking on the Database folder (under the Server name in the Object Explorer) and clicking the New Database command.
4) Type the name of the database in the textbox provided for the Database name (mine is Students) in the New Database Dialogue box and click OK. If successful, you now have a database in the server but there are no tables and data in the database.
5) Right-click on the Students database >>Select the Task command and click on Import Data. This displays the SQL Server Import and Export Wizard page.
The SQL Server Import and Export Wizard guides the user on where and how to import the data from the file and where this data would appear. The image below is a snapshot of the excel data we want to import into SQL Server. It has heading – Names, Admin No, Class, Subject, and Score together with their corresponding data.
6) Click Next
7) Select the Source from which to copy data…In our case, it is the Excel file. From the Datasource dropdown, Select Microsoft Excel. In the Excel file Path, >> Click on the Browse button and navigate to the folder where the file is located.
8) Make sure that the Excel version selected is Microsoft Excel 2007 – 2010 and that the First row has column names checkbox is selected.
9) Click Next
10) To specify where to copy the data, Select SQL Server Native Client 11.0. This displays your Server name, the Authentication type, Database (in our case, it is Students).
11) Click Next
12) Leave the default selection (copy data from one or more tables or views) and click Next
13) Click the table name [dbo].[Sheet1$] to edit the name to scores.
The preview button in Fig 7 displays a snapshot of the data as written in the excel file.
The Edit Mappings shows the headings with the default data types and size. You can edit the size by double-clicking on the number.
It is actually good to edit the size. The variable character columns having a 255 size is quite large for the Names, Class and Subject Columns. This increases the overall file size, thus may reduce performance and efficiency in the long run.
14) Click Next twice
15) Click Finish to execute the action and import the data into the database.
In Fig 10, a list of commands was executed during the process. Verify that the process has no errors.
16) Click the close button.
If everything is successful, then congratulations on importing data from an Excel file. Now check to see both the table (Scores as stated in Step 13) and the data are in the database. First, Select the Students database and click the Refresh button from the Object Explorer.
Expand the tables folder tree view from the Students database and check that the dbo.Scores table exits.
I hope you enjoyed the process as described below. Next week, I will show you how we can import data from an excel file into Microsoft Access. Until then, take care.
