February 16, 2012 at 5:23 pm
Alrighty then. I'm trying to do a massive insert with minimal logging of the same table from one database to another as a one-shot for other work. I don't want replication and my ldf space is limited. Table is 65+ gigs and my log has ~20g max. Thus, minimal logging. Besides, the quicker this finishes the quicker things go, less log write = faster work.
BULK INSERT is being whiny about only working with files, openrowset BULK doesn't seem to make sense for this, and I'm trying to make sense of any other method to do a massive insert with minimal logging as per this in BOL:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/f2a68e0f-9190-45c4-abee-1b2ebbb13817.htm
Yes, I'm on 2k5, just using the 2k8 SSMS/BOL. 🙂
I've built the table and it needs a clustered index for my purposes which also exists. I have a linked server which connects to the other server so I can access the view I'm trying to dump into a local table.
What I can't figure out is how to insert, via BULK, a massive set of information across servers for a local dump as a minimally logged action.
I'm also using this as a test bed for a few other possible staging loaders I'm looking into (that are currently batched to avoid ldf breaks) so I'm very interested in understanding how to get this to work without workarounds. I'm looking for a repeatable process.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 16, 2012 at 5:44 pm
How about this?
1. Create a new database in another server with only one table that matches your source table
2. BCP out data from your source table to a file
3. Copy the BCP file to the other server
4. BCP-In the file to the other server's table
5. Take a back-up of the new database
6. Restore it in your source server
I know this is one of the crappiest idea, but worth a try, ain't it? :w00t:
February 16, 2012 at 5:51 pm
ColdCoffee (2/16/2012)
How about this?1. Create a new database in another server with only one table that matches your source table
2. BCP out data from your source table to a file
3. Copy the BCP file to the other server
4. BCP-In the file to the other server's table
5. Take a back-up of the new database
6. Restore it in your source server
I know this is one of the crappiest idea, but worth a try, ain't it? :w00t:
Heheh, yeah, that DOES work. Well, ish, not sure why you're passing around backups. At that point I could pass the BCP file over to the other server and do a direct load. Hoping to avoid that silliness. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 16, 2012 at 6:24 pm
Evil Kraig F (2/16/2012)
ColdCoffee (2/16/2012)
How about this?1. Create a new database in another server with only one table that matches your source table
2. BCP out data from your source table to a file
3. Copy the BCP file to the other server
4. BCP-In the file to the other server's table
5. Take a back-up of the new database
6. Restore it in your source server
I know this is one of the crappiest idea, but worth a try, ain't it? :w00t:
Well, ish, not sure why you're passing around backups.
I thought u wanted to have the table "mirrored" in the same server ; so i thought of passing along the back-up to the source server and restore it so thay you can use 3-part naming convention for your queries 🙂
February 16, 2012 at 6:35 pm
ColdCoffee (2/16/2012)
I thought u wanted to have the table "mirrored" in the same server ; so i thought of passing along the back-up to the source server and restore it so thay you can use 3-part naming convention for your queries 🙂
Ah! Sorry for the confusion. No, I'm looking to go cross server with this. Just via linked server so the data only has to be written once.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2012 at 12:34 pm
I ended up forced to go to SSIS to enforce a table to table BULK with minimal. I can't for the life of me figure out a direct way to go table to table as bulk via T-SQL. I'd blame it on exhaustion but it shouldn't be this difficult.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2012 at 12:42 pm
Using SSIS is probably the best way to do this. You can do what you're asking in SQL 2k8 with a trace flag I believe (I'll find the link when I'm not posting from my phone!), but I think SELECT INTO is the only insert construct that allows this in 2k5.
February 17, 2012 at 2:11 pm
Agreed on the SELECT/INTO but you really don't want to do that across a linked server. It WILL lock the source in that case.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2012 at 2:42 pm
Jeff Moden (2/17/2012)
Agreed on the SELECT/INTO but you really don't want to do that across a linked server. It WILL lock the source in that case.
Yeah, precisely the scenario I'm attempting to avoid. Rats.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply