May 25, 2010 at 6:41 am
I have some flat file with around 1118546 lines of INSERT command.
This INSERT will be for 4 column in a table.
I can not do this with BCP as it is not just a plane value and doing it through any other way causing SQL.Memory timeout issue.
I am running with 4 GB RAM on SQL Server Enterprise 64-bit.
Would like to know what else could be the efficient approach for doing this.
May 25, 2010 at 6:50 am
Vishal in my experience, a text filefilled with INSERT INTO....commands is much much slower than using BULK INSERT or bcp to bring in a text doucment filled with raw data;
for example, Importing 40K lines of zip code data takes a second or two as raw data and BULK INSERT,, but runs for 3 minutes as INSERT INTO; a million line doc would probably hit the tipping point of the server and never complete if it was all INSERT INTO statments;
can you re-export your file to get it in some sort of delimited format? you can use the import wizard if it is quote delimited or something, or use a format file; but most definitely you can use BULK INSERT or bcp;
abandont he idea of using INSERT INTO statements for million row tables...it's not worth it.
Lowell
May 25, 2010 at 6:54 am
Lowell (5/25/2010)
Vishal in my experience, a text filefilled with INSERT INTO....commands is much much slower than using BULK INSERT or bcp to bring in a text doucment filled with raw data;for example, Importing 40K lines of zip code data takes a second or two as raw data and BULK INSERT,, but runs for 3 minutes as INSERT INTO; a million line doc would probably hit the tipping point of the server and never complete if it was all INSERT INTO statments;
can you re-export your file to get it in some sort of delimited format? you can use the import wizard if it is quote delimited or something, or use a format file; but most definitely you can use BULK INSERT or bcp;
abandont he idea of using INSERT INTO statements for million row tables...it's not worth it.
I completely agree with you since I had the similar experience. but the pain is that this was just a one time export file provided ad in order to have raw data, some manual work will be required which will be time consuming.
May 25, 2010 at 7:01 am
ugg; one-time imports are sometimes the most time consuming...can we see a row or two of sample data? enough to make a format file so you could test a bcp script?
with a decent text editor, you could do a massive find-and-replace to the file with INSERT INTO statements, inserting GO statements every 100 lines or so, that would allow the file to be processed in batches to avoid the time out issue.
---edit----
actually, using editplus or notepad++ as an example, you'd create a macro to page down 5 or six times, jump to the end of line, insert a CrLf ,GO and another CrLf;
then you 'd script that macro to run, say 50K times;
Lowell
May 25, 2010 at 7:11 am
Lowell (5/25/2010)
ugg; one-time imports are sometimes the most time consuming...can we see a row or two of sample data? enough to make a format file so you could test a bcp script?with a decent text editor, you could do a massive find-and-replace to the file with INSERT INTO statements, inserting GO statements every 100 lines or so, that would allow the file to be processed in batches to avoid the time out issue.
Hi Lowell, yes, if nothing will do then, I will have to do a massive find and replace. I am changing it to raw data the same way you suggested but all I was trying to avoid is this massive and manual thing.
This is how the sample data looks like:
INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,78628,4,1)
INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,89059,4,1)
INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,89230,4,1)
INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,89235,4,1)
May 25, 2010 at 7:24 am
Vishal Singh (5/25/2010)
This is how the sample data looks like:
INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,78628,4,1)
INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,89059,4,1)
INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,89230,4,1)
INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES(1,89235,4,1)
looks very easy to re-format; all four values appear to be integers, so no format file required
with Editplus Find and Replace INSERT INTO [tbl1] ([PrimaryID],[RelatedID],[RelationID],[PosID])VALUES( and then find and repalce the ending close-parenthesis;
save the file, and use a bulk insert command like this:
BULK INSERT [tbl1] -- four columns inferred, right?([PrimaryID],[RelatedID],[RelationID],[PosID])
FROM 'c:\Export_o.txt' --filename?
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '',
FIRSTROW = 1
)
Lowell
May 25, 2010 at 7:45 am
May 25, 2010 at 11:59 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply