Most of the time users need to import their data into SQL Server for its proper management. Select from the variation of source as well as destination data source types, choose tables for copying or even for specifying own query for data extraction and store the work as SSIS package. SQL Server provides other different options for importing data into SQL server just like BCP, Open query, Open row set, Bulk insert. In the following write-up, we have discussed these different ways in details.

Import Using BCP

The BCP utility is a command-line tool, which utilizes the Bulk Copy Program API to bulk copy data between the instance and data files of SQL Server. By utilizing this, you can easily export all the data files from SQL Server database into data file and vice versa and create format files, which supports importing as well as exporting operations.

To utilize BCP utility for performing these tasks, run a BCP command in Command Prompt Windows by using the following syntax:

bcp {table|view|"query"}
    {out|queryout|in|format}
    {data_file|nul}
    {[optional_argument]...}

BCP command needs three arguments. The first one is (table|view|“query”) that represents source data as well as destination in SQL Server database. Utilize BCP utility for exporting data from table or preview via a query. If there is some specific query then, must surround it in quotation marks.

The other argument in BCP command is (out|queryout|in|format), which determines the command mode. While running BCP command, you must state one of the mentioned four modes as stated:

• out: The command transfers data from table or preview into data file.
• queryout: This exports data that is recovered via a query into data file.
• in: This command imports data from data file into table or preview.
• format: This command makes a format file based on a table.

The third argument in BCP command (data_file|nul) is full path of data file or when data file should not be stated, the Null value. If you are importing data then, you must state the file, which contains source data.

Import BULK INSERT

The BULK INSERT statement that is a Transact-SQL statement that permits to bulk-load data file into SQL Server database. There are several examples of BULK INSERT statements for copying data into Server table. 

OPENROWSET

There are various of situation arises where users need to run an ad hoc query, which recovers the data from remote OLE DB data source and loads bulk of data into SQL Server table. In such a situation, users can utilize OPENROWSET function in T-SQL for passing string connection and query to the data source in a manner for recovering the relevant data. It can be utilized from the OPENROWSET function for recovering the data from any source, which supports registered OLD DB provider like remote instance of server.

OPENDATASOURCE

OPENDATASOURCE functions makes easy for users in getting an information about ad hoc connection as a part of four-part object name as one time linked server. There is no need for specifying or the creation of linked server to query other sources if it is preferred infrequently.

OPENQUERY

The OPENQUERY command is mainly utilized for the initiate an ad-hoc distributed query by simply using linked server. It is begun by stating OPENQUERY as table name in a way of clause. It opens linked server, then performs a query as if implementing from that server.

While implementing queries as well as receiving data directly in similar way is not bad, there are effects while joining the results of OPENQUERY via local table. Typically joining remote tables via local tables across this network is not an efficient way of querying. In some circumstances, it may be better to subscribe a remote table by importing it locally after that joining it locally.

Conclusion

Importing of the data is quite important for the proper management of data in an appropriate way. Therefore, in the above discussion, we have discussed different options for importing data into SQL Server.