January 9, 2009 at 2:33 am
I am creating and loading data through scripts scripts.
There are two tables which are having records around 2lacs and 45 lacs.
For these two tables I have created insert scripts having format as follows,
Insert into Table_name (Column1,column2,...) values(value1,value2,...)
The problem running these scripts is they are consuming so much time,nearly 3 to 4 hours.
I have tried the option of bulk insert but it is not producing the exact data that is in scripts.
So can any one tell If I use ,
Insert into Table_name (columns...)
select value1,value2....
union all
select value1,value2....
union all
select value1,value2....
will the performance get better or same as the scripts.
If any other option is there please give the suggestion.
January 9, 2009 at 2:48 am
You can use the script below...
insert into
select column1,column2... from
January 9, 2009 at 2:50 am
you can use the below
insert into
select column1,column2 from
January 9, 2009 at 2:52 am
barunkmallick (1/9/2009)
you can use the belowinsert into
select column1,column2 from
Don't do double post just edit the post!
January 9, 2009 at 3:43 am
No use .....it gives approximately same performance. Please read question carefully. I need to insert 45 lacs records in a table and i am having insert script of it.It takes approx. more than 5 hours.
I want to make it fast.
Any other way to have that data in the table through scripts.
January 9, 2009 at 3:49 am
Yes! below query will perform better as this will be bulk insert
Insert into Table_name (columns...)
select value1,value2....
union all
select value1,value2....
union all
select value1,value2....
-Vikas Bindra
January 9, 2009 at 3:50 am
Performance will improve using UNION ALL, as you mention in your first post. Of course, always test first.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 9, 2009 at 3:53 am
Try dropping the indexes before inserting and recreate the indexes after insert
and insert using Select unionall option
-Vikas Bindra
January 9, 2009 at 6:26 am
I'm confused as to why BULK INSERT is not generating the correct data. It's generally going to work better than straight inserts just because of the difference in logging. Any more details there?
"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
January 9, 2009 at 8:01 am
Yes you are right.
I have tried the BULK INSERT by using .csv file it is working correct.
Because i have not specified the KEEPIDENTITY attribute it was giving incorrect result.
Thank you .
January 9, 2009 at 8:05 am
rtbhosale (1/9/2009)
Yes you are right.I have tried the BULK INSERT by using .csv file it is working correct.
Because i have not specified the KEEPIDENTITY attribute it was giving incorrect result.
Thank you .
Excellent, glad to hear it.
"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
January 9, 2009 at 9:26 am
rtbhosale (1/9/2009)
Yes you are right.I have tried the BULK INSERT by using .csv file it is working correct.
Because i have not specified the KEEPIDENTITY attribute it was giving incorrect result.
Thank you .
So how much better did your performance get?
January 12, 2009 at 7:04 am
Less than 5 minutes approximately 45 lacs records inserted into database.
Extremely fast.
January 12, 2009 at 9:20 am
January 13, 2009 at 9:09 am
once again
lakhs is a large measure - I think it is from the Hindi
por favor (please, in Spanish)
if you use a non-english term, please define it
dank U (Dutch)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply