Importing Data

Introduction

There are multiple ways to import data into SQL Server. This article will make a distinction between importing data between tables and importing from external sources such as an Excel of CSV file.

Some methods of importing external data, such as Bulk Import, are only available in certain versions and using other desktop tools.

Importing data from Excel or from CSV files can be accomplished using an SSMS Import Wizard tools under database/Tasks/Import Data. Tutorials for these methods can be found here:

Importing External Data from API's

There are a few sites that provide data in a SQL Server format that does not require using the Import Wizard. One such site is MySafeInfo (MSI). This site provides test data and other lists in multiple formats, including the ability to download the data as a T-SQL created table and INSERT statements.

Here are the steps to import a table from MSI. The source data set is a list of automotive manufacturers and models for recent cars.

  1. Navigate to https://mysafeinfo.com. Register to create an account, if necessary.
  2. Navigate to MSI Makes and Models List
  3. Select View API Options button (right hand side)
  4. Change format to SQL
  5. Enter table name, such as yourschema.carimportdata (replace yoursschema with your actual schema name
  6. Set Create Table option to "yes"
  7. Click the orange link (this generates a text output with the CREATE TABLE and INSERT statements)
  8. Copy and paste the T-SQL output from the web page to an SSMS tab
  9. Make sure the CREATE TABLE statement and INSERT statements have your schema name
  10. Run the query to create and populate the table

You should now have a table named yourschema.carimportdata. The columns are the original MSI names.

The CREATE TABLE statement should look something like this:

CREATE TABLE drm.carimportdata ( ID INT, Manufacturer VARCHAR(255), Model VARCHAR(255), Year VARCHAR(255) );

Importing Internal Data Between Tables

Copying data between tables is a type of data importing. For example, once a sset of data has been imported from an external source, it may not be in a format that is completely usable, or at least not in a preferred format for the current database.

Using the sample auto data from above, to import the data into other tables that are better structured you need to import the data from this temporary table. By analyzing the data, you should be able to determine that a better structure would be two tables, ones for manufacturers (also called "Makes") and one for Models. Each Model would be linked by an FK to a Make. The T-SQL scripts for those two tables look like this (replace "xxx" with your schema name):

CREATE TABLE xxx.makes ( makeid int not null identity (1001,1) primary key ,makename varchar(50) not null );
CREATE TABLE xxx.models ( modelid int not null identity (1001,1) primary key ,makeid int null ,year char(4) ,modelname varchar(50) not null );

The tasks now is to import a unique list of Makes into the makes table and then import the models, including setting the makeid FK column values to point to records in the makes table..

  1. Write a SELECT query to get a distinct list of manufacturers (makes); note that the default API column is named “manufacturer”
  2. Using your distinct SELECT statement, insert records into your makes table
  3. Write a query to return all model names
  4. add a sub-query to all return the corresponding makeid from your makes table, based on the makename. For example:
  5. select mdl, (select makeid from drm.makes where makename=manu) from drm.carimportdata
  6. When this process is complete, you will have a full set of related data in twh two tables

Here are sample statements to accomplish importing data into both tables:

-- INSERT statement with SELECT to populate distinct list of makes
insert into xxx.makes (makename) select distinct manufacturer from xxx.carimportdata
--INSERT statement to populate models table, including FK to makes
insert into xxx.models (modelname, year, makeid) select model, year, (select makeid from xxx.makes where makename=manufacturer) from xxx.carimportdata