4

I am exporting a table to a TSV file using sqlcmd, and I am running into issues. The table has 16+ million rows and about 55 columns.

The problem is that it does not export the full table, but seems to stop randomly at various points (i am guessing a timeout?) Each time a different number of rows are exported and each time the file is of a slightly different size (indicating that I am not hitting any row or size limit).

I am not using any timeout switch (meaning the default of "as long as it takes" is used).

Here is my command (with most columns removed for simplification and illustration purposes):

sqlcmd -U myUsername -P myPassword -S SERVERNAME -d "DBNAME" -Q "SELECT ROW_KEY, ISNULL(CARRIER_ID, ''),ISNULL(CONVERT(VARCHAR(19),ORDER_DATETIME,120),''),ISNULL(HEIGHT, ''),ISNULL(RESIDENTIAL_CHARGE, '') FROM MYTABLE" -o "OUTPUT_FILE.txt" -h-1 -s"|" -W

I wonder if it could have something to do with timeouts or the use of ISNULL() on all of the columns (although when i run the query in sql server management studio I get the correct number of rows returned e.g. 16 million + )?

Again, I get about 4-8 million rows each time, but never the full amount. I am in a sql server 2k5 db, and running sqlcmd from a remote machine with sql server 2k8.

6

You'll probably want to do this with bcp, which is intended for this kind of thing. It would be something like this:

bcp "SELECT ROW_KEY, ISNULL(CARRIER_ID, ''),ISNULL(CONVERT(VARCHAR(19),ORDER_DATETIME,120),''),ISNULL(HEIGHT, ''),ISNULL(RESIDENTIAL_CHARGE, '') FROM MYTABLE" queryout OUTPUT_FILE.txt -c -S <servername> -U <login_name> -P <password>

Replace -c with -w if you're dealing with nvarchar/nchar, and want Unicode output.

1
  • +1 for suggesting bcp. I would suggest using -n to bcp out in Native Format which is very fast compared to regular bcp. You wont be able to see the contents of the file as they are in binary, so there is no conversion involved and it will cut down your time as well. – Kin Shah Jul 11 '13 at 14:59
3

Might consider using Invoke-Sqlcmd since you are working from a machine with SQL 2008 installed. You do not get the column separator -s option as sqlcmd does, but within PowerShell you could export it to XML or CSV.

Basic syntax for your query would be:


$q = @"
SELECT ROW_KEY, 
   ISNULL(CARRIER_ID, ''),
   ISNULL(CONVERT(VARCHAR(19),ORDER_DATETIME,120),''),
   ISNULL(HEIGHT, ''),
   ISNULL(RESIDENTIAL_CHARGE, '') 
FROM MYTABLE
"@

Invoke-Sqlcmd -ServerInstance MyServer -Database MyDatabase -Query $q -Username  -
Password  | Export-Csv MyFile.txt

Then if you absolutely needed to keep a particular separator between the columns you could just modify your query a bit:


SELECT ROW_KEY + '|' + 
   ISNULL(CARRIER_ID, '') + '|' +
   ISNULL(CONVERT(VARCHAR(19),ORDER_DATETIME,120),'') + '|' +
   ISNULL(HEIGHT, '') + '|' +
   ISNULL(RESIDENTIAL_CHARGE, '')
FROM MYTABLE
9
  • 1
    Export-Csv takes a parameter delimiter – Jefferey Cave Aug 26 '15 at 21:45
  • I tried PowerShell 5.1 with Invoke-SqlCmd and a table with millions of rows. It turns out that the whole data set being exported is collected in memory (memory usage grows up to 7GB in my case). Powershell is also painfully slow in comparison with e.g. F# or C# – Ivan Akcheurov Apr 18 '18 at 13:15
  • @JeffereyCave it defaults the delimiter to a comma, so unless you want something else other than that it is not a required parameter. – user507 Apr 18 '18 at 15:24
  • @IvanAkcheurov PowerShell itself is not slow, you are dealing with the Invoke-Sqlcmd command that is slow. When you isse that command and pipe it to something else it has to read all the data in before it will pass it down...it does not stream it in any manner. If you are dealing with a large amount of data you will be better of using the BCP utility, that is what it is meant to do. – user507 Apr 18 '18 at 15:28
  • @ShawnMelton, just tested a sample. Please try the following: gc .\1500MB.txt | measure in PowerShell (took 82 seconds) and let length = readLines "1500MB.txt" |> Seq.length in F# Interactive (took 5 seconds). PowerShell is 16x times slower but it doesn't eat memory anymore as it used to. F# is brilliant as always :) – Ivan Akcheurov Apr 25 '18 at 9:42
0

What @db2 said. If that doesn't work, try segmenting your data. If you have an integer ID field, you could add

 WHERE ID % @NumSegments = @SegmentNumber

Loop for all values of @SegmentNumber from zero to one less than the number of segments, and you should get all records. Microsoft SQL's implementation of modulus only returns positive numbers, unlike some (https://stackoverflow.com/questions/4412179/best-way-to-make-javas-modulus-behave-like-it-should-with-negative-numbers/4412200#4412200) languages.

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.