0

I am restoring two large .bcp files that together total approximately 1.6B records. The total records in each file is unknown. To keep from filling up the transaction log, I'm importing 100M records at a time using -F and -L arguments. Is there a way to figure out how many records are in each file?

Here is what I'm doing for the restore. If I don't know the right number of records, I am thinking I'll end up rolling back when I give bcp a -L value that doesn't exist:

bcp [IMP_Table].[dbo].[LINE_ITEM] in "E:\Temp\LINE_ITEM-1.BCP"  -b100000 -f "E:\Temp\LINE_ITEM-1.fmt" -T -S"Server\DS" -F 1 -L100000000

Thanks,

Pat

pyearick
  • 81
  • 1
  • 6
  • Well yeah, but not with T-SQL functions AFAICT. You could run `xp_cmdshell` to get the result of `FINDSTR /R /N "^.*" file.txt | FIND /C ":"` which counts the number of lines from shell command findstr( cf http://stackoverflow.com/a/10109647/243373). That would work only if your row terminator is `/r/n` of course. – TT. Nov 13 '16 at 22:25
  • Is there any particular reason you don't just use the `-b` option to limit the batch size while still importing the whole file? – Jeroen Mostert Nov 13 '16 at 22:31
  • Jeroen, sorry - I am using -b let me edit what I pasted in. I was blowing up my log just using -b however so I went to -F -L. I'm in simple recovery, this is a "one time" import but BCP still adds to the log using -b apparently. – pyearick Nov 13 '16 at 22:47
  • If you're in simple recovery and you're setting a batch size on the import, you shouldn't be running into issues. Do you have anything else that would prevent log clearing (CDC, replication, etc)? – Ben Thul Nov 14 '16 at 00:04
  • Thanks Ben. Another dba told me that I didn't need -b since the default -b was 1000 rows (as it prints out to the screen) so I wasn't setting -b when I started this process. Maybe that was the issue. Since then I have been using -b explicitly.Thanks for all the replies folks, I'll just keep pressing ahead and hopefully I can figure it out at the end of the file from the messages. I've restored 800M records so far. – pyearick Nov 14 '16 at 00:52

0 Answers0