2

I've been working with SQL Server since SQL Server 7. So I know about BCP, Bulk insert, openrowset and SSIS.

My question is a simple one:

  • Do you know of any other ways to Import/Export data using SQL server Express versions SQL 2005 to 2014 (and soon 2016).

I want to make sure that there isn't anything else due to the fact that the use of BCP (which is what I'm using right now) requires the use of xp_cmdshell and for security reasons, requires an xp_cmdshell_proxy account. I'm being challenged at work, and want to make sure nothing else exists, or that is new in Microsoft SQL Server (Express) before I respond.

  • Are there specific things about the technologies you've already mentioned that make "something else" a necessity? – Aaron Bertrand Nov 10 '15 at 21:28
  • maybe this SQL Server Export and Import Wizards and some Powershell utilities like Out-DataTable and Write-DataTable ? – Jefferson B. Elias Nov 10 '15 at 21:30
  • @Aaron Bertrand I want to make sure that there isn't anything else due to the fact that the use of BCP (which is what I'm using right now) requires the use of XP_CMDSHELL and for security reasons, requires an XP_CMDSHELL_PROXY account... I Like the Idea of SQLCLR but don't have knowledge/experience using it. – JohnG Nov 11 '15 at 13:17
4

SQLCMD can be used to export data. The default output is sometimes unwieldy for that, but you can use some command-line options to turn off the headers, trim trailing spaces, and to use a comma separator instead of the default space:

sqlcmd -Q "SET NOCOUNT ON; SELECT TOP(10) name, object_id, type FROM sys.objects;" ^
  -h-1 -W -s ","

Returns:

sysrscols,3,S
sysrowsets,5,S
sysclones,6,S
sysallocunits,7,S
sysfiles1,8,S
sysseobjvalues,9,S
syspriorities,17,S
sysdbfrag,18,S
sysfgfrag,19,S
sysdbfiles,20,S

Use the -o option to send the output to a file:

sqlcmd -Q "SET NOCOUNT ON; SELECT TOP(10) name, object_id, type FROM sys.objects;" ^
  -h-1 -W -s "," -o C:\temp\SysObjects.txt

A custom .NET program (Console app, Windows app, or even Web app) can be created to import and/or export. For importing, since Table-Valued Parameters (TVPs) are not an option here, it is sometimes faster to transform/serialize the data into XML, and then execute a stored procedure that will accept the XML parameter and using the .nodes() function, deserialize it back into a rows that can be inserted via the INSERT INTO table (fields) SELECT x.value(),... FROM @XmlParam.nodes(); construct. Of course, you wouldn't want to do 1 million rows in this manner, so you would break the import file into batches of X rows and send 100 - 500 at a time. If there is a lot of data then maybe straight INSERT statements are better, but in that case you would want to wrap each batch of X rows into an explicit transaction using SqlTransaction.


Depending on how comfortable you (or someone you work with) are with .NET programming, you can use SQLCLR to build your own custom import and/or export functionality. This would work in all editions of SQL Server in all versions starting with SQL Server 2005.

Along these lines, there is a commercially available library of SQLCLR functions and procedures called SQL# (which I am the creator of). Some of the functions and stored procedures handle importing and exporting data. The File_SplitIntoFields Stored Procedure returns a result set where each row is a line from the input file, but each field in the file is a separate column. By default the datatype for each field is NVARCHAR(MAX), but there is an optional input parameter to specify what the exact datatype of each column should be. This makes it rather easy to do:

INSERT INTO dbo.MyImportTable (Col1, Col2, Col3, Col4)
  EXEC SQL#.File_SplitIntoFields
    @FilePath = N'',
    @RegExDelimiter = N',',
    @DataTypes = N'NVARCHAR(100),INT,DATETIME,BIT';

And there is an equivalent Stored Procedure called INET_SplitIntoFields that does the same thing, but rather than sourcing the data from a file, it streams it directly from a URL (hence no need to download the file locally first).

For exporting, there is a Stored Procedure called DB_BulkExport that combines many of the features of SQLCMD and SSIS. I created this after needing to do several exports and not being able to decide between SQLCMD and SSIS since each one has its pros and cons, and each one has one or two features that the other one doesn't have but all of those features were needed. For example, DB_BulkExport doesn't need any hard-coded output field specification and can handle SELECT * queries (something I couldn't figure out how to manage in SSIS but was easy with SQLCMD). It can also handle text-qualification of fields without having to update the source query to concatenate the double-quotes in there (which makes for a very ugly and hard to maintain query). In fact, it can be set to text-qualify ALL fields, or just fields that need it based on their datatype (strings, dates, GUIDs, etc).

  • I didn't think of SQLCLR since I don't have the experience to use it. Can a SQLCLR link to a Delphi DLL? Don't ask me what that means, the developers here are asking me if it's possible... right now, I feel like a deer facing headlights. – JohnG Nov 11 '15 at 13:26
  • @JohnG It depends on if the Delphi DLL is pure managed code (no C++, etc) or mixed (managed and unmanaged code). Mixed DLLs are not allowed. But even if the Delphi DLL is pure managed code, if it has dependencies on unsupported .NET Framework libraries that are mixed, that also won't work. And if there are dependencies that are pure Framework DLLs, those can change to mixed in the future. Many of these options for 3rd party DLLs will require them be to set to UNSAFE and the DB to be marked as TRUSTWORTHY. Not ideal, but they can be on a separate instance with Linked Server to move data over. – Solomon Rutzky Nov 11 '15 at 13:43
  • 1
    @JohnG For more info on working with SQLCLR, see my article Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server) and the series in general. Levels 3 and 4 go over the various security options and implications of each. Free registration is required (sorry, I have no control over that site). – Solomon Rutzky Nov 11 '15 at 13:44
  • @JohnG PS, I am currently performance testing the import. At first glance, the notion of taking 5 minutes to import 1 million rows seems like a rather long time. – Solomon Rutzky Nov 11 '15 at 13:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.