May 1, 2016 at 12:28 pm
Jeff Moden (4/30/2016)
Grant Fritchey (4/28/2016)
Technical question:I want to export large amounts of data. However, I don't want to use SSIS. I'm going to use SQLCMD.EXE. I need to be able to script these exports so that I do, let's say, 10,000 rows at a time, in chunks. I'm doing this so that I have the ability to easily restart, and, allow for starting to migrate or move the exported files as each chunk finishes.
What's the best way to, in a relatively generic fashion, efficiently divide a table into these 10,000 row chunks?
Articles, blog posts, some documentation to get this going in the right direction. Please!
Oh, and, what the heck... URGENT!
Ha!
Ok, so based on some of the problems folks think you might have with chunking data, I'm curious. How many columns and how many rows do you want to export? And will they be delimited or fixed field?
It's going to be anything and everything. We're working on some automation for a tool at Redgate.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2016 at 1:52 pm
Grant Fritchey (5/1/2016)
Jeff Moden (4/30/2016)
Grant Fritchey (4/28/2016)
Technical question:I want to export large amounts of data. However, I don't want to use SSIS. I'm going to use SQLCMD.EXE. I need to be able to script these exports so that I do, let's say, 10,000 rows at a time, in chunks. I'm doing this so that I have the ability to easily restart, and, allow for starting to migrate or move the exported files as each chunk finishes.
What's the best way to, in a relatively generic fashion, efficiently divide a table into these 10,000 row chunks?
Articles, blog posts, some documentation to get this going in the right direction. Please!
Oh, and, what the heck... URGENT!
Ha!
Ok, so based on some of the problems folks think you might have with chunking data, I'm curious. How many columns and how many rows do you want to export? And will they be delimited or fixed field?
It's going to be anything and everything. We're working on some automation for a tool at Redgate.
How will the exported data be used? That's important because it will help define what the best tool is. For example, if it's for SQL Server to SQL Server, a "Native" format BCP would probably be the best way to go no matter what vehicle you fire it from.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2016 at 6:14 am
Grant Fritchey (5/1/2016)
Jeff Moden (4/30/2016)
Grant Fritchey (4/28/2016)
Technical question:I want to export large amounts of data. However, I don't want to use SSIS. I'm going to use SQLCMD.EXE. I need to be able to script these exports so that I do, let's say, 10,000 rows at a time, in chunks. I'm doing this so that I have the ability to easily restart, and, allow for starting to migrate or move the exported files as each chunk finishes.
What's the best way to, in a relatively generic fashion, efficiently divide a table into these 10,000 row chunks?
Articles, blog posts, some documentation to get this going in the right direction. Please!
Oh, and, what the heck... URGENT!
Ha!
Ok, so based on some of the problems folks think you might have with chunking data, I'm curious. How many columns and how many rows do you want to export? And will they be delimited or fixed field?
It's going to be anything and everything. We're working on some automation for a tool at Redgate.
Can I take a moment to be a naysayer here?
"Anything and everything" is the quickest way to defeat. If a company tries to do too much at the same time on a project, the project is automatically doomed to failure either before or right after release time.
It's not that I want to sound pessimistic. It's just that from my perspective, the best way to start this tool is to define a specific start and end point for a specific line of data. Then once the first goal is met and validated, expand it to the other paths one at a time. I know that sounds annoying and like a lot of work, but if you narrow down the expectations for the first bit of work, you'll save yourself a lot of frustration and a possible giant failure at the starting sprint.
May 2, 2016 at 7:09 am
Anyone have experience loading ASCII flat files into tables where the files may contain some extended ASCII characters? Any special tricks involved to accomplish this load?
Thanks,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 2, 2016 at 7:15 am
FYI
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 2, 2016 at 7:23 am
GilaMonster (5/2/2016)
FYI
YAY! And we're just now upgrading to 2012!
I feel so far behind the curve... sigh.
May 2, 2016 at 7:34 am
Brandie Tarvin (5/2/2016)
GilaMonster (5/2/2016)
FYIYAY! And we're just now upgrading to 2012!
I feel so far behind the curve... sigh.
If you're so far behind the curve, you aren't alone. We're migrating databases from SQL 2005 to SQL 2012.
May 2, 2016 at 7:36 am
GilaMonster (5/2/2016)
FYI
That date pretty much means we were both right on the release date. Now to try and convince some vendor to support it. For us that is the single biggest obstacle to upgrading. Most of our vendors still don't support SQL 2014, much less 2016.
May 2, 2016 at 8:20 am
TomThomson (4/30/2016)
Serializable is probably OTT since repeatable read may be adequate (maybe that depends on what sort of updating is going on, I haven't thought it through) but I suspect it isn't going to deliver high performance; nor is snapshot isolation, the temp database is probably going to grow too much when a vast collection of export chunks have to be mutually compatible and snapshot isolation is used to ensure that by building them all in one enormous transaction.
Repeatable Read would have less overhead, but it would still have problems with Phantom Reads which could occur if the database is not quiesced and more than one table is read. For example, you could catch a row in table B that is paired with a row in table A that you didn’t pick up during its export. You could ignore any rows that are newer than the time you started the export, but you would need some way of identifying which rows those are – if Grant needs a truly generic solution and can’t enforce any schema, that would be challenging.
Edit: Ah - I just realized that I made an assumption that Grant never stated. I assumed this was for multiple tables, not just a single one. You're right - I think I did make it overly complicated.
May 2, 2016 at 8:33 am
Ed Wagner (5/2/2016)
Brandie Tarvin (5/2/2016)
GilaMonster (5/2/2016)
FYIYAY! And we're just now upgrading to 2012!
I feel so far behind the curve... sigh.
If you're so far behind the curve, you aren't alone. We're migrating databases from SQL 2005 to SQL 2012.
Ditto that. Going through that right now except they tried to give me a decade old box for my staging server. It didn't survive. Now I know why we had problems with DR... the hard disk would drop transfer rates to just 3-7MB per second after 12 minutes, which is when cache filled and it went totally synchronous on some drives that are actually slower than USB 2.0 flash drives. I guess they never checked for things like that. Trying to restore 1.5TB of files certainly does. 😛 All the while, they thought it was the DR software. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2016 at 8:36 am
Jeff Moden (5/2/2016)
Ed Wagner (5/2/2016)
Brandie Tarvin (5/2/2016)
GilaMonster (5/2/2016)
FYIYAY! And we're just now upgrading to 2012!
I feel so far behind the curve... sigh.
If you're so far behind the curve, you aren't alone. We're migrating databases from SQL 2005 to SQL 2012.
Ditto that. Going through that right now except they tried to give me a decade old box for my staging server. It didn't survive. Now I know why we had problems with DR... the hard disk would drop transfer rates to just 3-7MB per second after 12 minutes, which is when cache filled and it went totally synchronous on some drives that are actually slower than USB 2.0 flash drives. I guess they never checked for things like that. Trying to restore 1.5TB of files certainly does. 😛 All the while, they thought it was the DR software. :hehe:
p.s. We've had the 2012 boxes stood up for almost 2 years just sitting there burning trons. It took that long for management to finally agree as to an implementation test period and date. It's a classic Catch-22 where they insisted that we were too busy to test the systems that would make all of us... less busy. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2016 at 8:41 am
WayneS (5/2/2016)
Anyone have experience loading ASCII flat files into tables where the files may contain some extended ASCII characters? Any special tricks involved to accomplish this load?
Most likely an issue with how the encoding/Code Page is being specified, but it depends a lot on the source file, the datatype and Collation of the destination field, and how you are doing the import.
Can you perhaps post this as a question in whatever forum is appropriate here and then reply to this post with the link to that thread? Thanks :-).
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
May 2, 2016 at 11:39 pm
We're still on 2008 here 😉
May 3, 2016 at 4:08 am
jeff.mason (5/2/2016)
GilaMonster (5/2/2016)
FYIThat date pretty much means we were both right on the release date. Now to try and convince some vendor to support it. For us that is the single biggest obstacle to upgrading. Most of our vendors still don't support SQL 2014, much less 2016.
We have some vendors that won't support us past SQL 2008 R2... 🙁
Thomas Rushton
blog: https://thelonedba.wordpress.com
May 3, 2016 at 4:18 am
Chad Crawford (5/2/2016)
TomThomson (4/30/2016)
Serializable is probably OTT since repeatable read may be adequate (maybe that depends on what sort of updating is going on, I haven't thought it through) but I suspect it isn't going to deliver high performance; nor is snapshot isolation, the temp database is probably going to grow too much when a vast collection of export chunks have to be mutually compatible and snapshot isolation is used to ensure that by building them all in one enormous transaction.Repeatable Read would have less overhead, but it would still have problems with Phantom Reads which could occur if the database is not quiesced and more than one table is read. For example, you could catch a row in table B that is paired with a row in table A that you didn’t pick up during its export. You could ignore any rows that are newer than the time you started the export, but you would need some way of identifying which rows those are – if Grant needs a truly generic solution and can’t enforce any schema, that would be challenging.
Edit: Ah - I just realized that I made an assumption that Grant never stated. I assumed this was for multiple tables, not just a single one. You're right - I think I did make it overly complicated.
It is multiple tables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 53,881 through 53,895 (of 66,738 total)
You must be logged in to reply to this topic. Login to reply