未加星标

Learn Different Options for Importing Data Into SQL Server

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

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 aBCPcommand in Command Prompt windows by using the following syntax:

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

BCPcommand 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 BCPcommand, 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 BCPcommand (data_file|nul) is full path of data file or when data file should not be stated, theNullvalue. 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.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerWindowsRY
分页:12
转载请注明
本文标题:Learn Different Options for Importing Data Into SQL Server
本站链接:http://www.codesec.net/view/524550.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(33)