Upload a Csv File to Sql Ssms

While creating a database, your client may need to relieve sometime information in new database and he has asked yous to import his CSV file into SQL server database, or you already have some data in .csv file and needs to import it, then we can take 2 possible ways to import csv data into sql server, using Bulk insert SQL query or using SQL server management studio (SSMS) GUI options, let's take a look on each of them one by 1.

So, earlier nosotros proceed to look more details into importing methods, suppose, this is our .csv sample file.

Import CSV to SQL server using query

Showtime, we will try to import above Orders.csv file into SQL server table "Orders" using query, so to simply import the above data into SQL server table, we will apply below query

          Bulk INSERT Orders FROM 'D:\Orders.csv' WITH (     FIRSTROW = ii, -- as 1st one is header     FIELDTERMINATOR = ',',  --CSV field delimiter     ROWTERMINATOR = '\n',   --Employ to shift the control to next row     TABLOCK )        

Later running the above query yous will run across output as below

and you can verify information technology in the tabular array

Few things to annotation, here

• In the above query, we have rights to read file from source "D:\Orders.csv", if you don't have proper permission, you may become error, so make sure you take proper permissions.
• In the above csv file, we are notifying "Id" data as well, there can be possibility, when we don't take Main fundamental, and we need to auto-increment PK, nosotros will show you how to import csv file into sql server with auto-increment id.

Import csv into SQL with auto-increment Columnd (Id)

When we take a situation in which, we take csv file, simply nosotros cannot provide PK "Id" field inside the .csv file, and need's to automobile-increment it, we can do it by providing space with comma in csv file, and then above csv file will look similar this

          Id,Country,Price,OrderQuantity ,India,10.00,iv ,Commonwealth of australia, 5.00,10 ,Brazil, 10.00,5 ,Communist china,5.l,5 ,Nepal,20.20,10        

and you can import and salve it in same way, similar you did before, using same query

          BULK INSERT Orders FROM 'D:\Orders.csv' WITH (     FIRSTROW = two, -- as 1st 1 is header     FIELDTERMINATOR = ',',  --CSV field delimiter     ROWTERMINATOR = '\due north',   --Apply to shift the command to next row     TABLOCK )        

Import CSV file into SQL server using SQL server management Studio

In this process, we will use SQL server management studio GUI to import csv file in sql server database table, as majority re-create and other bulk import options are not available on the SQL servers, then this GUI based option is practiced and like shooting fish in a barrel for big CSV files, since it allows you to import data, step by footstep and more than hands.

Step 1: Select database, correct-click on it -> "Tasks"->Select "Import apartment file"

At commencement, open up your SQL server management studio, and select the database ( in this example "OrderDetails") then correct-click on it, after right-clicking on information technology, select "Tasks"-> Select "Import apartment file"

Now, once you select "Import flat file" a new dialog box, will open, click "Next"

Stride two: Browse file and requite table proper noun

Clicking "next", volition bring the new screen, using which we need to select the "csv" file to be imported, and then click on "Browse", locate the .csv file and give tabular array name.

Note: Table proper name muste be unique, means that table should exist new table ( not already created table )

Step iii: Preview data before saving it

Once, yous volition click next later on selecting file, you tin can preview the data before saving it into tabular array, considering in a higher place CSV file, we take can preview similar below

Equally you can see in the in a higher place image, we tin can run across CSV data in preview, you lot can click on "Next"

Step 4: Check Information-type and map it properly, to successfully import csv

Now, you need to map csv file columns with database columns blazon properly, as shown in the beneath image

You can alter data-type, as per your csv file, once done, click "Next"

Notation: you need to map information-type with columns properly, otherwise you volition get conversion error.

Step five: Check details and click stop

Once you are done with data mapping, you can verify details and click "Finish", data will exist imported, with creation of table.

Click "Finish" and in the side by side Screen you volition come across, information has been imported successfully ( if in that location is any error, you will encounter mistake and can check mistake.), and so click "Shut".

You can refresh the tables of database, select tabular array, information technology will show all the information imported properly.

Since, nosotros imported the file into our "OrderDetails" database in the "Order" table, the "OrderDetails" database should contain a table named "Order". Become to Object Explorer-> Databases -> OrderDetails-> Gild

Then, you can run the query

          Utilise OrderDetails Select * from Orders        

Output:

That'south it, as you lot can see in the above table, we were able to import CSV file in SQL Server database table successfully.

Yous may besides like to read:

Aggregate Functions in SQL Server (SUM, AVG, COUNT, MIN, MAX)

Common Tabular array Expressions (CTE) in Sql server

Check database size in Sql server ( Various Ways explained)

Row constructor in SQL server (Explanation With example)

Understanding SQL server Coalesce() with instance (ISNULL comparing included)

horwitztandishe.blogspot.com

Source: https://qawithexperts.com/article/sql/import-csv-into-sql-server-with-query-or-without-query-using/265