June 13, 2011 at 1:25 pm
I need to run a script that generates a table and inserts 300k records. sqlcmd truncates results to around 50k. How do I run this large script that inserts 300k rows? I am using microsoft sql server 2008 Below is an example of the query. In addition I have a 41mb text file that is a similar query which returns error on insufficient memory though the server has 8gb and nearly nothing else on it.
if exists ( select * from sys.tables where name = N'jobexp')
DROP TABLE [jobexp];
CREATE TABLE [jobexp] (
[JOBEXPNO] Integer,
[JOBSIDE] Integer,
[JOBJN] Char(2),
[JOBKEY] Char(23),
[JOBPAYEE] Char(40),
[JOBPROJ] Char(12),
[JOBLEV2] Char(6),
[JOBLEV3] Char(6),
[JOBTYPE] Char(1),
[JOBSTATUS] Char(2),
[JOBCOST] Numeric(11,3),
[JOBQTY] Numeric(8,2),
[JOBBAMT] Numeric(10,2),
[JOBLASTP] Char(12),
[JOBINV] Char(12),
[JOBCTL] Integer,
[JOBNOTES] Text,
[JOBJNARC] Bit,
[JOBVENID] Char(8),
[JOBPJINV] Char(12),
[JOBACCOUNT] Char(12),
[JOBCATGY] Char(6),
[JOBCHECK] Integer);
insert into [jobexp] values(73,1,'JX','','REPRODUCTION','011835','RE','R','R','R',14.400,1.00,3.00,'DEBBIE','',0,'',null,'353360','','','T',0);
insert into [jobexp] values(74,1,'JX','','REPRODUCTION SPECIALTIES','011835','RE','R','R','R',57.600,1.00,57.60,'CARLA','',0,'',null,'353410','','','',0);
etc...
June 13, 2011 at 1:31 pm
Have you looked into the BULK INSERT statement... or the bcp command-line utility?
--SJT--
June 13, 2011 at 1:51 pm
the data is from a dbase5 file and formatted by an export tool. It seems by cursory view of bcp or bulk import that the formatting included in my original post is not considered. In other words I cannot reformat the source data. The example in the first post is what I have to work with.
June 13, 2011 at 3:57 pm
Break the 300k file script file into 10 files of 30k apiece, being sure to break between insert statements. Then just run them sequentially.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 14, 2011 at 5:19 am
Was hoping to avoid this as it is accounting info and breaking scripts may introduce errors. Also must be replicated possibly three times prior to final conversion.
Found that I could import into Access and then import into sql but am astounded that there is not some way to import the data directly into sql when the scripts are in tsql format.
June 14, 2011 at 5:48 am
You may want to try adding in some GO statements every 1000 lines or so - this is the batch terminator in SQLCMD and SSMS. Otherwise it's trying to submit the whole lot in one go.
SQL Server isn't really optimised for these kind of logical imports. You can build an SSIS package to read directly from dbase 5:
June 14, 2011 at 7:20 am
steve conard (6/14/2011)
Was hoping to avoid this as it is accounting info and breaking scripts may introduce errors. Also must be replicated possibly three times prior to final conversion.Found that I could import into Access and then import into sql but am astounded that there is not some way to import the data directly into sql when the scripts are in tsql format.
Look at SSIS or BCP or OPENROWSET for importing larger data sets. You are simply dealing with a limitation of the SQLCMD utility. I know your vendor wrote it that way, but frankly generating tens of thousands of input statements is a terrible way to get data into a database.
Does your source data exist in some sort of work table prior to the generation of the tens of thousands of individual insert statements? If so, the data could be imported directly into your table directly and finish a LOT faster.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply