How does BULK INSERT work internally ?
Hi , Could someone please explain how does BULK INSERT internally work and why is it much faster than the normal INSERT operations ? Regards, Shishir. ...
bulkinsert
Datawarehouse duplicate dimension rows
We're starting to load up a datawarehouse with data from event logs. We have a normal star schema where a row in the fact table represents one event. Our dimension tables are a typical combination of user_agent, ip, referal, page, etc. One dimension table looks like this: create table referal_dim( id integer, domain varchar(255),...
Bulk Insert Code Before </body> Tag in 100 Files
I'd like to insert <?php include_once('google_analytics.php'); ?> before the closing body tag of about 100 php files. Unfortunately the person who made the site didn't make a header or footer template. What is the best way to do this? I've tried using grep/find for getting a list of files and piping the results through xargs to se...
How to copy large set of data in SQLServer db
I have a requirement to take a "snapshot" of a current database and clone it into the same database, with new Primary Keys. The schema in question consists of about 10 tables, but a few of the tables will potentially contain hundreds of thousands to 1 million records that need to be duplicated. What are my options here? I'm afraid tha...
Optimizing MySQL inserts to handle a data stream
Hi, I am consuming a high rate data stream and doing the following steps to store data in a MySQL database. For each new arriving item. (1) Parse incoming item. (2) Execute several "INSERT ... ON DUPLICATE KEY UPDATE" I have used INSERT ... ON DUPLICATE KEY UPDATE to eliminate one additional round-trip to the database. While trying...
Bulk import from Informix into Oracle
Hi. We need to pull some tables from an Informix SE database, truncate tables on Oracle 10g, and then populate them with the Informix data. Does a bulk import work? Will data types clash? I'd like to use a simple Java executable that we can schedule daily. Can a Java program call the bulk import? Is there an example you can provide? T...
Is there an optimal number of records OR data size I can insert in one request?
Using the Mysql multi insert statement (Inserting several records in one INSERT statement). Is there an optimal number for the number of records I can insert in one go, Or to the data size (bytes)? ...
What is the difference between bulk copy (bcp) and bulk insert in SQL Server 2008?
What is the difference between bulk copy (bcp) and bulk insert? Is there a switch to export all of the data in all of tables in a database for bcp? ...
Why does Bulk Insert block Update in MS SSIS?
I have an SSIS package set up like this: If I run only the New Rows flow the Bulk Insert finishes without a problem, but as soon as i connect the Live Rows flow the package stalls indefinitely. When I check the activity monitor the Update Newer Table Rows task stalls, blocked by the Insert New Rows task. Why does the Bulk Insert not ...
SQL Server 2005: Improving performance for thousands or Insert requests. logout-login time= 120ms.
Can somebody shed some lights on how SQL Server 2005 deals with may request issued by a client using ADO.NET 2.0. Below is the shortend output of SQL Trace. I can see that connection pooling is working (I believe there is only one connection being pooled). What is not clear to me is why we have so many sp_reset_connection calls i.e a ser...
Elegant way to handle upsert with Hibernate and MySQL
I'm currently working on a batch import feature that sits on top of Hibernate and MySQL. My goal is to have Upsert functionality for several tables. I'm finding myself writing a lot of code to deal with seeing if the row exists by key and branching to right method. I was wondering if there might be a better way, i.e. something analogous ...
Making bulk Insertion/updation
I have data entry form like... There are some empty rows and some of them have values. User can Update existing values and can also fill value in empty rows. I need to map these values in my DB table and some of them will be inserted as new rows into the database and existing record will be updated. I need your suggestions, How can ...
Sybase JConnect : ENABLE_BULK_LOAD usage
Can anyone out there provide an example of bulk inserts via JConnect (w/ENABLE_BULK_LOAD) to Sybase ASE? I've scoured the internets and found nothing. I'm running on a short timeline so if someone can provide a short java code example it would be much appreciated. Thanks, Chris ...
What is the fastest way to insert 100 000 records from one database to another?
I have a mobile application. My client has a large data set ~100.000 records. It's updated frequently. When we sync we need to copy from one database to another. I have attached the second database to the main, and run an insert into table select * from sync.table. This is extremely slow, it takes about 10 minutes I think. I noticed th...
Performance of bcp/BULK INSERT vs. Table-Valued Parameters
I'm about to have to rewrite some rather old code using SQL Server's BULK INSERT command because the schema has changed, and it occurred to me that maybe I should think about switching to a stored procedure with a TVP instead, but I'm wondering what effect it might have on performance. Some background information that might help explain...
Why do I get "XML parsing: line 2, character 0, incorrect document syntax" when Bulk Inserting in MS SQL Server
I'm doing a BULK INSERT into a table using a FMT format file, but I get the following error: XML parsing: line 2, character 0, incorrect document syntax Here is my code BULK INSERT [DM_Flux].[dbo].[Stage] FROM 'C:\temp\data.dat' WITH (FORMATFILE = 'C:\temp\FormatBcp.fmt') Here is the formatfile (standard format file, not XML): 10...
SQL Bulk Stored Procedure call C#
How do I call stored procedures in bulk? I would like to do something like a bulk copy. All that the stored procedure does is 8 selects for unique constraint and 8 inserts. With no returning value. ...
Can Sql Server BULK INSERT read from a named pipe/fifo?
Is it possible for BULK INSERT/bcp to read from a named pipe, fifo-style? That is, rather than reading from a real text file, can BULK INSERT/bcp be made to read from a named pipe which is on the write end of another process? For example: create named pipe unzip file to named pipe read from named pipe with bcp or BULK INSERT or: ...
Efficent way to bulk insert with get_or_create() in Django (SQL, Python, Django)
Is there a more efficent way for doing this? for item in item_list: e, new = Entry.objects.get_or_create( field1 = item.field1, field2 = item.field2, ) ...
Bulk insert XML data into SQL Server
Using the table structure below how would I create a bcp bulk insert that would handle the XML data. It is important that this is run from command line. CREATE TABLE [dbo].[MyTable]( [Id] [uniqueidentifier] NOT NULL DEFAULT (newid()), [MyXmlField] [xml] NULL Thanks in advance... ...