I'm having problems bulk inserting data into my SQL Express 2005 DB. I'm using the following code: BULK INSERT [dbEPi].[dbo].[postcode] FROM 'C:\Development\postnummerRegister.txt' WITH ( FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n' ) and I get the following error: Msg 4866, Level 16, State 1, Line 1 The bulk load failed. T...
My understanding of SQL*Loader's conventional path is that it simply generates INSERT statements and sends them to the database. Are there any performance benefits to using this rather than just generating the SQL statements programmatically and executing them against the database? ...
I read the section in the SQL*Loader reference manual and it's a bit over my head. Our clients are all running x86, so I'm assuming we won't have to worry about byte ordering. However, we do have some customers running on 32-bit and others running on 64-bit OSes. What exactly do I need to do to make sure I don't run into any problems ...
I am writing a python script that will be doing some processing on text files. As part of that process, i need to import each line of the tab-separated file into a local MS SQL Server (2008) table. I am using pyodbc and I know how to do this. However, I have a question about the best way to execute it. I will be looping through the f...
I came across the .import command to do this (bulk insert), but is there a query version of this which I can execute using sqlite3_exec(). I would just like to copy a small text file contents into a table. A query version of this one below, ".import demotab.txt mytable" ...
I am importing csv to db using bulk insert. It is the comma delimited csv file. No text qualifiers for all fields. But some fields may have comma as part of the data. for eg, ADDRESS field value. Those values are surronded with double quotes. Those double quotes appear only if the field value has comma in it otherwise values are not su...
Hi, I need to write some code to insert around 3 million rows of data. At the same time I need to insert the same number of companion rows. I.e. schema looks like this: Item - Id - Title Property - Id - FK_Item - Value My first attempt was something vaguely like this: BaseDataContext db = new BaseDataContext(); foreach (...
What's a reasonable maximum size that a cross-platform application could allow a text file to get? I understand that this is an oversimplified question so allow me to explain. My team is implementing a bulk load interface for clients to load data into our database. It will write out a CSV file and then load that file into the appropri...
I am trying to do a bulk upload into a SQL server DB. The source file has duplicates which I want to remove, so I was hoping that the operation would automatically upload the first one, then discard the rest. (I've set a unique key constraint). Problem is, the moment a duplicate upload is attempted the whole thing fails and gets rolled b...
I'm fairly new to SQL Server. I'm trying to bulk insert into a table, using the command in SQL Server Management Studio (2005): BULK INSERT Table1 FROM 'c:\text.txt' WITH (FIELDTERMINATOR = '|') I get the error: Msg 4860, Level 16, State 1, Line 1 Cannot bulk load. The file "c:\text.txt" does not exist. I'm positive the file actua...
Hi all, I have a storedproc which takes an ntext field where some records are passed. Suppose there is a table t as below. | ID | Name | Designation| -------------------------- | 1 | ABC | Team leader| | 2 | DEF | Developer | | 3 | XYZ | Manager | I am sending two more record as '4|Tom|Developer; 5|John|Team Leader;' The ab...
An MS SQL Server 2008 BULK INSERT to a datetime column introduces an error in the 3rd decimal place of the seconds portion: 2009-09-19 15:02:41.328 in the input file becomes 2009-09-19 15:02:41.327 in the database. Here is what the input text file contains: 1 2009-09-19 15:02:41.328 Here is what the table looks like after BULK INS...
I (will) have hundreds of thousand of records where i insert once, never update with many rows holding the same previousId. Can i guaranteed a start/end index? where i insert X number of rows in table_c with a transaction and write the start and end (or start and length or end and length) into table_b instead of having each row hold tabl...
Hello everyone, I am using VSTS 2008 + C# + .Net 3.5 + ADO.Net to develop a console application to do bulk insert copy. I want to use both bulk insert batch and bulk insert timeout property. For the BulkCopyTimeout property, I am confused and want to know whether it applies for the whole bulk or applies to only each batch of the bulk? ...
Hi, I have a table in database which already have 100 records. Now I have to insert 100 more records to it. Now which object we have to use? whether dataadapter or sqlbulkcopy? ...
I have a csv that looks like this: "blah","blah, blah, blah ect, ect","column 3" "foo","foo, bar, baz more stuff on another line", "another column 3" Is it possible to import this directly into SQL server? ...
I have something that looks like this foreach (var user in NewUsers) { var dbUser = new User {FirstName = user.FirstName}; dbUser.Save(); } That is too many inserts into the database. Can I do something like? User.BulkInsert(NewUsers); Thanks. ...
Does MySql have a bulk load command line tool like bcp for SQLServer and sqlldr for Oracle? I know there's a SQL command LOAD INFILE or similar but I sometimes need to bulk load a file that is on a different box to the MySQL database. ...
Hi!, I´m having trouble with bulk insert a file that contains a field in data type text, and it holds enters and the delimiter for the fields are pipes "|" and the row terminator is "|\n" I get an error of truncation for the fields next to the text field. I think that the bulk insert thinks that the enters in the second field are the n...
Hi everyone, have an issue here of how to configure mysql (myisam) properly for the bulk insert (load data infile) to be performed fast. There is 6 Gb text file to be imported, 15 mln rows, 16 columns (some int, some varchar(255), one varchar(40), one char(1) some datetime, one mediumtext). relative my.conf settings: key_buffer = 80...