March 6, 2009 at 2:55 am
Hi- Masters,
I have one question.
I need to load data from some txt files into some tables with Bulk insert.
Then, this data (in that tables) will popupate other tables (after some transformations and verifications).
After the process is done, i can delete the tables that just got the bulk insert data.
This is a DAILY process:
I will construct the tables (trought stored procedures), then load the data to it , trought bulk insert , then make some transformations to the data, then insert the data into the tables that i need , then i will delete this first tabels.
This tables that will just hold the Bulk insert data for a moment, most be constructed as #table (temporary tables), or can they be created as normal tables and then deleted?
I'm thinking in not create temporary tables (#) to load the bulk. Instead, i think that i can create normal tables on the database and on the end of the process delete then (trought stored procedures).
Is there any problem with this?
Isn't better to create (because of performance) normal tables in this case, to hold the bulk?
tks,
Pedro
I have a dought about how to create this temporary tables to put the data from the Bulk insert
March 6, 2009 at 3:59 am
Hi Pedro,
You can Create #table for importing bulk data.
I don't think using #table will affect anywhere in your case.
March 6, 2009 at 4:15 am
ok.
But i would like to create the tables directly in my database, so this can not be #tables (this tables are created in tempdb), that's why i asked if there is no problem in creating normal tables and then delete them, after they are no longer needed.
Do you seen any problems with this soluction?
tks,
Pedro
March 6, 2009 at 4:27 am
Ya you can also create tables in your db. No problem. If the data is too large the Db will be heavy.
One of the DBA advised me to use #table because it performs well while fetching bulk.
Regards,
Vijay Singh.
March 6, 2009 at 4:36 am
Forgot to tell.
My user database is in simple recovery model (i know the risk of having that way). so in this case will the databse be havy?
March 6, 2009 at 5:04 am
Hmm, then no problem in creating the physical tables.
Simple recovery model Allows for High Performance Bulk Copy Operations
and it Uses Minimal Log Space
Regards,
Vijay Singh:)
March 7, 2009 at 3:17 pm
I'd recommend that you never import 3rd party directly to permanent tables. I always do the imports to temporary staging tables so I can validate the data as being good and reasonable before I move the data to the permanent table. It's also gives me the opportunity to do dupe checks and other things similar.
--Jeff Moden
March 7, 2009 at 7:46 pm
Jeff Moden (3/7/2009)
I'd recommend that you never import 3rd party directly to permanent tables. I always do the imports to temporary staging tables so I can validate the data as being good and reasonable before I move the data to the permanent table. It's also gives me the opportunity to do dupe checks and other things similar.
Why not have permanent staging tables? That's what I usually do. I find that there's a lot of convenience to having the staging table's definition actually exist, even when it is not "staging".
In fat, if I have the space, I usually don't even empty the staging table until the next time that I need it. It sure is easier to debug a load that didn't do what we wanted it to when the staged data is still there.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 7, 2009 at 9:13 pm
RBarryYoung (3/7/2009)
Jeff Moden (3/7/2009)
I'd recommend that you never import 3rd party directly to permanent tables. I always do the imports to temporary staging tables so I can validate the data as being good and reasonable before I move the data to the permanent table. It's also gives me the opportunity to do dupe checks and other things similar.Why not have permanent staging tables? That's what I usually do. I find that there's a lot of convenience to having the staging table's definition actually exist, even when it is not "staging".
In fat, if I have the space, I usually don't even empty the staging table until the next time that I need it. It sure is easier to debug a load that didn't do what we wanted it to when the staged data is still there.
Not a bad idea... but I have between 6 and 8 processes taking different downloads in parallel... I could have each process with it's own named permanent stating table, but the processes are identical and that would require 8 different sets of stored procedures unless I wanted to do the dynamic SQL thing. It's easier to have just one set of procs using Temp tables because each temp table is automatically uniquely named.
--Jeff Moden
March 7, 2009 at 9:30 pm
Hmmm, yeah that's quite a bit different from what I usually have to do: loading daily extracts. Very regular, very stable. Much more suited to permanent staging tables.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply