Import Data to MS SQL 

A problem when implementing MS SQL Server is importing small pieces of data, in the form of Excel or Access files, into the database. In this article we will use a Microsoft tool called SQL Server DTS (Data Transformation Services) to efficiently migrate Excel and Access data to a SQL Server. SQL Server DTS comes bundled with the SQL Server. We have used SQL Server 2000 and its DTS services.

First, run the Import and Export wizard by going to Start>Programs>Microsoft SQL Server and click on Next. When the wizard asks you to select the data source that you would like to import to the SQL Server, select ‘Data Source: Microsoft Excel (version)’. Select the Excel version that you need from the drop-down menu. Give the path of the Excel file
and click on Next. Follow the  same steps for importing Access data.

To define where DTS will import the data, in the Destination section select Microsoft OLE DB provider for SQL Server and give SQL administrative username and password. Then come to the Database section. If you have a pre-existing database, select the database where you would like to import the Excel/Access files to from the drop-down menu. Or you can create a new database out of the entire Excel/Access file. For that select from the database. This will bring up a dialog box, where you define the name, log size and data size of the new database. Give a name to the new database and keep the log and data size as default (by default its 2). Click on the Ok button. 

To import the entire Excel file to a table, select ‘Copy table(s) and view(s) from the source database’. If you want selected data from the Excel file, then select ‘Use the query to specify the data transfer’. You can even build your own query to select specific data from the Excel file with the query builder tool. Click on Next to continue. 

All the entries that you import from Excel are copied as ‘nvarchar’ data type in the table. This creates a problem while writing an SQL query based on number or date. So, to modify the fields of a table, you need to first select the source spreadsheets that will be imported to SQL table and then click on the Transform button. In the window that opens you can edit the data type of a table. When you click on the Next and then the Finish buttons, depending up on the size of the Excel data, DTS will transfer all the spreadsheet data in to the
SQL table. 

In Access you have to select the data types from the Access database that you want to modify and import to SQL. To do so, tick the tables and click on the Transform button to change the data type. Once you have finalized the data structure, click on Next and then on the Finish buttons. In few seconds, depending on the size of your data, your data will be migrated 

to SQL. 

With this, you can transfer fragmented, small chucks of data from various data sources to a single database in SQL Server.

Sanjay Majumder