New SQL Server table from Excel

This allows you to create a table in SQL Server from a table of data in Excel in just a few clicks. For those of you who are more advanced users, it is possible to fine tune the creation of the table like setting the exact data types on each column.

Prepare your data in Excel

The table creator uses the Excel table format to fetch extra information about the data formats, so you will need to have your data in an Excel table like this:

If your data is not already formatted as an Excel table, simply select a cell within the data range and press CTRL + T (hold down the CTRL-key and press T), then press 'OK' in the Create Table dialog.

Create the table in SQL Server

1. Click the New SQL Server table from Excel button from the SQL Server Tools menu.
   

2. The Create SQL Server Table dialog will be displayed:
   

3. Specify the table name and which SQL Server database to create the table in. SQL Spreads will always remember the last used database. If you need to change to another SQL Server click the Edit button next to the server name.

4. You can optionally change some of the properties of the table columns (see below for more details), or simply click OK to create the table. A dialog will appear to confirm the table creation was succesful.
   

Fine tuning the table column properties

In the Create SQL Server Table dialog you'll see all the columns in your Excel table and be able to specify the exact data types to use. SQL Spreads will infer the best data type based on the data in the Excel table, but you can select different ones by clicking the Type drop down list and selecting another data type for each required column:

For the following data types, you can also specify the exact size of the column in the database:

• Varchar and NVarchar: Can be set to the max number of characters or MAX to auto-adjust the size based on the content in the column.
• Decimal and Numeric: The length must be entered as '(7,3)' where the first number is the total number of figures and the last number is the number of figures after the decimal separator.

You can set a specific column to be the Primary Key by checking the Primary Key box.

If your columns should allow nulls, then check the Allow null box for each required column.

Default Settings

The option Insert the rows from Excel into the new SQL Server table is enabled by default. If the setting is disabled, none of the rows, from your raw data that you used to create the table initially, will be inserted into the newly created table in the database.

The option Add an auto-incrementing Identify column is enabled by default. This will automatically add an auto-incrementing Identity column into your table that you can name according to your requirements.

By default, the schema name dbo will be used for your newly created table. You can re-name the schema to align to your SQL Server table naming convention in your database by using the Use the schema name field.