Importing Flat Files into SQL Server Management Studio

Trying to conceptualize how new data will interact with existing data in a database but don’t want to spend the time to build out an SSIS package to get it into the database? Well, search no more and we will walk through the steps you can take to get your data into a table within your database in no time at all!

A few things to address before we walk through the import in SSMS. For this example, if your data is currently in an Excel file, you are going to want to get it into a CSV or TXT file format before you initiate the import process. Please refer to Figure 1 when saving out your data.

Figure 1

Once you have your CSV file, you can now initiate the import flat file task in SSMS. You can hover over the database name, right click the database, then select Tasks, then Import Flat File. (Figure 2)

Figure 2

This will open the Import Flat File wizard (Figure 3).

Figure 3

From here you will select Next and click the Browse button to navigate to your CSV or TXT file you saved out. Then you will assign a name to the table in which your imported data will reside and assign it to a schema as seen in Figure 4. (SSMS will default the table name to your file’s name and the schema as .dbo)

Figure 4

Once you have gotten everything properly filled out, click Next. You will then see a preview of your data to be imported. You can peruse the first 50 records of data here (Figure 5). Then click Next.

Figure 5

Next up is to determine the data types and column schema selections for your table. (The wizard will preselect what it thinks is a best fit for your data based on the 50-row sample displayed in the ‘Preview Data’ section of the wizard.) Figure 6 shows the options available to you, including whether or not to allow nulls and if you are assigning a primary key. Once you are satisfied with your selections, click Next.

Figure 6

The following window shown in Figure 7 allows you to view the summarized details of the import that is about to take place. Click Finish.

Figure 7

Depending on whether your selections accounted for the full set of data being imported will depend on what is seen on the Import progress page.

If you do encounter an error upon import, you can click on the Error link that is provided in the Result column to view what error was encountered. If an error is encountered, it typically only stops the ingestion process mid import. You will most likely have to drop the object that was created before you can reattempt the process, but you can keep this wizard window while you take care of that and when done dropping the object, you can simply revert to the prior steps in the wizard by clicking Previous twice to get back to the Modify Columns step to make adjustments to your data type and schema selections.

Figure 8

Figure 9 shows what is experienced when the import process has been successful with ingestion! You can simply click Close to exit the wizard now. You have successfully ingested data into a table in SSMS!

Figure 9

Now you can query your new table and see the data you imported (Figure 10).

Things to consider once this is completed:

Conduct row counts between what was in original source and the table to ensure no data loss.
Quality-check your data to ensure that there are no discrepancies between what is expected and what is actually there in the table.

Figure 10