Upload Data to Sql Server From Csv

Today, at that place are easier ways to import CSV to SQL Server. You lot tin code less or utilise graphical tools. Yous can fifty-fifty get the CSV from cloud storage to your on-premise SQL Server. This post will focus on how you can practice it in 3 easy ways.

Table Of Contents

1. Why to Import CSV to SQL Server
2. How to Import CSV File in SQL Server (3 Easy Means)
3. Majority INSERT
4. SQL Server Management Studio Import CSV Tools
5. ETL Tools – Skyvia Deject Solution
6. Determination

Why to Import CSV to SQL Server

I read about comma-separated-values, or CSV, in Wikipedia. Tin can y'all believe that CSV was supported back in 1972? For what reason?

To exchange data betwixt 2 or more systems of different platforms and architectures. Today, the reason is still the same. Imagine you have 2 proprietary systems. I is using a proprietary NoSQL database similar PayPal'south. And the other is using SQL Server. It'due south like a Japanese talking to an Estonian. Understanding is nigh impossible. What is the solution you lot will inquire?

Export a CSV file from the NoSQL database. Then, import the CSV file to SQL Server. Rather than invent new software, exchange a mature and mutual file format. That will exist easier and less time-consuming.

Here are common scenarios when CSV tin can be useful:

• Customers pay a service visitor through a bank. So, the depository financial institution provides payment records to the service company using a CSV file.
• An operational organization built in-house needs to be integrated into an ERP system.
• Several SharePoint lists demand to be synced to a SQL Server database for data analysis.
• A biometrics system needs to be integrated into a human being resources organisation for attendance purposes.

Though this tin be done using JSON or XML, CSV is simpler to generate from the source. If the data requirement is not-hierarchical, CSV tin be a good fit. What well-nigh the target organization using SQL Server? Information technology is rated four.v out of five in Gartner Peer Insights. SQL Server in the deject or Azure SQL is also 1 of the top 3 DBMS of 2020. And then, learning to import CSV to SQL Server is worth the effort. You will ask how to do information technology. The answer is described in detail beneath.

How to Import CSV File in SQL Server (three Easy Ways)

Before we outset, we demand a sample CSV and a target tabular array in SQL Server. Check it out in the screenshot below.

This will be used to upload to SQL Server using 3 of the different ways to import CSV. So, download a copy of the role player.csv file from here. Recall where y'all saved it. You'll demand it later in this tutorial.

Meanwhile, the target table is structured the same in SQL Server.

In this tutorial, I'one thousand using the server proper noun MS-SQLSERVER. And the database name is CSV-MSSQL-TEST.

Using Majority INSERT

Majority INSERT is a command in SQL Server to import data files into a database table. It can be used to upload various file formats, including CSV. If you dear a fiddling coding, this is the way to become. Hither's the code on how to bulk insert CSV into SQL Server:

-- truncate the table starting time TRUNCATE TABLE dbo.Actors; Go  -- import the file Majority INSERT dbo.Actors FROM 'C:\Documents\Skyvia\csv-to-mssql\actor.csv' WITH ( 		FORMAT='CSV', 		FIRSTROW=2 ) Go

The start command simply deletes all the records in the target table. Then, the BULK INSERT command includes the target table and the CSV file. The location of the CSV should follow the rules of the Universal Naming Convention (UNC). Yous as well must tell SQL Server what file it'southward dealing with. In this case, FORMAT=CSV because the file is in CSV format. Finally, specify what row the data starts. FIRSTROW = 2 because the first row contains the column names.

You lot can issue a Majority INSERT command from SQL Server Management Studio or any other SQL Server tool. Another manner to do it is in PowerShell.

Pros and Cons of Using Majority INSERT

Pros:

• If y'all know the syntax, typing tin can exist faster than using a GUI interface;
• No need to parse the information. Bulk INSERT does it for you. If the target column uses a information type too modest for the data, an error will occur;
• Scheduling of execution possible in SQL Server Amanuensis;
• Likewise nifty for a one-time import task.

Cons:

• You cannot specify a CSV from cloud storage like Google Drive or OneDrive;
• Allows only SQL Server as the target database;
• Requires a technical person to code, run, and monitor.

Using SQL Server Management Studio Import CSV Tools

If you don't prefer coding, another useful tool is the Import Information from SQL Server Management Studio (SSMS). Here'due south how to import CSV to MSSQL table using SSMS.

Step one. From the Object Explorer, Expand the Databases Binder

Start, y'all need to go to Object Explorer and select the target database. Aggrandize the Databases folder. Cheque it out below.

Footstep 2. Select the Target Database

In this example, the target database is CSV-MSSQL-Examination. Correct-click that database then select Tasks. And and then, click Import Data.

The SQL Server Import and Consign Wizard window will announced with a welcome screen. From here, click Next.

Pace 3. Select a Flat File Source

This part volition let you pick the data source. For this, click the drop-down list and select Apartment File Source. Afterwards, click Next.

Footstep 4. Specify the CSV File

Now, you need to specify the file. Click Browse and specify the path of the CSV file every bit shown in the screenshot. If you have downloaded it, specify the download location. Then, in the file type, select CSV files (*.csv). And afterwards – actor.csv. Go along further by clicking Open.

Pace 5. Configure the Columns

Tin't SSMS detect the correct information types for each cavalcade? In our sample data, detection of the right types and sizes is off. So, we need to gear up information technology upwards ourselves.

Pro tip: Whenever you import data from ane platform to another, it's all-time to match the correct types and sizes. Why? To avoid errors and headaches.

Let's starting time past clicking Advanced. Then, you will see a listing of columns. Click each and set the type and size.

Based on the data types and sizes, refer to table beneath on what to ready for each column.

Column Proper noun	DataType	OutputColumnWidth
id	four-byte signed integer [DT_I4]	Due north/A
lastname	string [DT_STR]	20
firstname	string [DT_STR]	20
middlename	string [DT_STR]	20
suffix	string [DT_STR]	3

Step 6. Choose the Destination (SQL Server)

At present, you need to specify the SQL Server equally the target of the import procedure. To do that, select Microsoft OLE DB Provider for SQL Server. Then, select the SQL Server proper name and enter the necessary credentials. Select the Database name and click Next.

Pace vii. Specify the Database Tabular array and Check Cavalcade Mappings

Later selecting the database, y'all also need to specify the table. Choose the Actors table.

And so select Edit Mappings to see if the columns from the source match the target.

One time the Column Mappings window appears, check if the columns from the source and target lucifer. You tin either pick delete rows in the destination table or append rows. In our example, nosotros want to start cleaning. If you attempted to import using Majority INSERT before, there is information present in the target table. And then, an error will occur. To avert that, choose Delete rows in the destination table. Then, click OK.

Finally, to end our setup for the target table, click Next.

Stride 8. Optionally Save to an SSIS Package or Run Immediately

You can relieve the entire import configuration to an SSIS package. If y'all choose this, y'all can schedule the package to run at regular intervals. In our case, we will just run information technology immediately. For amend understanding, check out the side by side screenshot.

Then, choose Next to see a summary of your called settings. Or click Finish to run the import process. You will see the progress in the next window if you click Finish.

That's information technology for importing CSV to SQL Server using Import Information in SSMS. Yous tin can also choose Import Apartment File, but it will always dump to a new table. And you cannot salve it to an SSIS package.

Pros and Cons of Using SQL Server Management Studio Import Tools

Pros:

• No coding is required;
• If column mappings friction match source and target, it merely works;
• Allows many information sources and destinations, not but SQL Server;
• Saving to SSIS catalog and scheduling is possible, but limited to what was divers;
• Great for a ane-fourth dimension import task.

Cons:

• If you don't accept the specifications of the column types and sizes in the CSV file, column mapping is cumbersome;
• No manner to go the CSV from Google Drive, OneDrive, or a similar cloud storage.

Integrate data between 80+ cloud sources with no coding with Skyvia Data Integration

Using ETL Tools – Skyvia Deject Solution

Finally, nosotros volition utilize a cloud ETL tool to import the CSV file to SQL Server. One of the possible ways of importing CSV to SQL Server is from deject storage like Google Drive. In this section, yous will meet how to use Skyvia to import the CSV file from Google Drive to SQL Server on a local PC. Before you lot can use it, you lot need an account on Skyvia and Google. Both are costless to register.

The elements of a successful import using Skyvia are the following:

• Amanuensis – you need an Agent to permit Skyvia to connect to a remote SQL Server.
• Connections – you demand to define two connections: i for Google Drive and some other for the remote SQL Server.
• Parcel – Skyvia uses packages to define tasks for the import process (similar SSIS).

To start, yous need to log in to Skyvia. You will exist redirected to your default workspace. And so, from hither, you tin can create all the 3 things higher up. Hither's how.

Step 1. Create an Agent

To create an Agent or a tunnel to a remote SQL Server, click NEW. Then, select Agent.

Once y'all're on the next folio, proper noun your agent Skyvia-MyPC. Then, download and install the Skyvia agent. Delight pay attention to where y'all install the agent. Y'all will need this when y'all download the Agent Key. Finally, download the amanuensis cardinal file and store it where yous install the Skyvia Agent.

From here, your agent configuration has been completed. But you need to test it. So, run the installed Skyvia agent application. Here's a screenshot of the installed Skyvia Agent and Fundamental files.

Here'south what it looks like after running the agent.

In one case done, you tin can bank check the status on Skyvia. Here's a screenshot of a good connection. This means that now it is possible to connect to your SQL Server from Skyvia.

Step 2. Create Two Connections for the Source and Destination

Offset, let u.s. create a connexion to Google Bulldoze where the CSV is located. For this, click NEW and click Connection.

On the next page, you lot need to select a connector. Click Google Drive.

And then name the connection MyGDrive, and sign in to your Google account. Click Create Connection to create a connection. Here'south a completed Google Drive connectedness screenshot.

Second, let'southward create a SQL Server connection.

Click NEW again and select Connectedness. Refer to the screenshot in a higher place. Then click SQL Server. Proper noun your connection CSV-MSSQL-Examination. After that click Agent and select the Skyvia-MyPC agent created earlier. So enter the server proper noun, credentials, and database name. Hither'south a screenshot of a completed SQL Server connection.

Step 3. Create the Skyvia Parcel to Import CSV File to SQL Server

Nosotros're almost done. The final part is to create the Skyvia package. Then, offset by clicking NEW then Import. This will create an import packet.

In the opened package editor, proper name your package CSV-SQL-Examination and indicate the source and target. Our source is MyGDrive Google Bulldoze connectedness. And the target is CSV-MSSQL-TEST, the SQL Server connectedness.

Another things to pay attention to:

• Select the the Use new runtime checkbox. Otherwise, the SQL Server connection won't be visible in the driblet-down listing of targets;
• Choose CSV from storage service. Otherwise, the Google Drive connection won't be selected;
• Select the Preserve task ordercheckbox. This will brand tasks run in succession.

Step four. Create Tasks in the Import Bundle

Information technology's time to create the tasks. First, any existing record there is in the Actors table, it should exist deleted. Under Tasks, click Add new. A new window will announced. Then, select the actor.csv file in Google Drive. And so, brand the Text Qualifier blank and the Code Folio Western European Windows (1252). Finally, gear up the id column to DT_I4 (Integer).

Click Next stride to continue further. On the second page, under Functioning, click Delete. This will delete the records in the target. Click Next step again to keep to mapping settings. You lot will see a mapping betwixt the id column of the source and target. Click Salvage.

Finally, the start chore is completed.

The second and last chore is to insert the rows in the CSV file to SQL Server. The steps are well-nigh the same, except you demand to ascertain all column types and sizes based on Table 1 earlier. Then, click Next footstep and select the Insert operation instead of Delete. Finally, Save the task.

Y'all tin can run into below the screenshot of the completed parcel. Click Salve to save the package.

Footstep 5. Run the Packet

To run the package, click the Run button in the upper right corner of the page. Then, click Monitor to come across the progress. Come across a sample screenshot beneath.

Pros and Cons of Using Skyvia

Pros:

• A lot of data sources and destinations, including cloud storages;
• Experienced ETL professionals will experience an piece of cake learning curve;
• Schedule an unattended execution of packages;
• Flexible pricing based on current needs and usage;
• No need to install development tools (except when Agent is required).
• Rated 4.8 in G2's Best ETL Tools and four.nine in Gartner Peer Insights.

Cons:

• Sometimes queueing tin accept longer than the actual runtime elapsing. This tin exist improved.

Schedule your CSV information export and import to deject apps or databases without coding

Decision

You can use these three ways to import CSV to SQL Server: all are viable tools depending on your needs.

• BULK INSERT – proficient for on-premise import jobs with a little coding.
• SQL Server Management Studio Import Tools – skilful for one-fourth dimension import and export of various on-premise data.
• ETL Tools – the most flexible for on-premise and cloud data of various types. Yous can use either SSIS or a deject solution like Skyvia.

Was this post useful? If aye, please share it on your favorite social media platforms.

hartforeced02.blogspot.com

Source: https://skyvia.com/blog/3-easy-ways-to-import-csv-file-to-sql-server