Importing & Exporting Data
Using the SQL Server Import and Export Wizard
The Import and Export Wizard (DTSWizard.exe) is the easiest method to move data from sources like Excel, Oracle, DB2, SQL Server, and text files to nearly any destination. This wizard uses SSIS as a framework and can optionally save a package as its output prior to executing.
The benefit of an Integration Services package, though, is that we can schedule it to run on a regular basis and modify it as necessary by using SQL Server Data Tools.
When we deploy Integration Services on a computer that has a 64-bit operating system,
Integration Services installs an x64 version of the SQL Server Import and Export Wizard which in some cases may cause problems when connections are made to data sources that use 32-bit providers, such as a 32-bit version of Microsoft Access.
The resolution to avoid this would be to either choose to install Client Tools or SQL Server Data Tools (SSDT) shared features during the SQL Server setup process.
SQL Server 2012 uses parallel scans to retrieve data exported data will not be written using a specific order in the data file unless we take precautions
bcp "<code>SELECT </code>Name, ProductNumber FROM AdventureWorks2012.Production.Product ORDER BY NAME" queryout products.txt -c -T
- The -T option specifies a trusted connection.
- bcp uses the credentials of the currently logged on user. If we do not specify the -T option, we must specify a username and password by using the -U and -P options.
- The -c option specifies that bcp is being used with character data
“You have a file that contains customer names, phone numbers, and addresses. Which command-line utility would you use to import this data into a table on a database instance?
- You use the bcp utility to import data into the database from the command line.
Every week, you must perform a bulk export of data from the database. What steps could you take to automate this process?
- Use the SQL Server Import and Export Wizard to create an Integration Services package. Schedule the package to execute on a periodic basis.
You must copy the contents of several large tables to new tables on a different database hosted on the same instance. Which method could you use to accomplish this goal?”
- You could use the SELECT…INTO statement to copy the contents of existing tables quickly into new tables if the new tables are associated with a different database hosted on the same instance.