Questions tagged [bulk]
The bulk tag has no usage guidance.
48
questions
0
votes
2answers
29 views
MySQL update a record on duplicate key update (merge)
I have a table products with the follow schema:
CREATE TABLE `products` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint unsigned NOT NULL,
`article_id` bigint unsigned NOT NULL,
...
0
votes
0answers
51 views
Use of tempdb for bulk upsert operation
We are using SSIS for ETL purpose. There is a shortcoming in SSIS as it doesn't provide any in built component which will bulk upsert the destination. The alternative to it to use Staging tables which ...
0
votes
0answers
36 views
Creating a Format File For Bulk import
So when I try implementing this query:
BCP -SMSSQLSERVER01.[Internal_Checks].[Jan_Flat] format out ^
-fC:\Desktop\exported data\Jan_FlatFormat.fmt -c -T -UUGmn91 -SMSSQLSERVER01 -PChan
I am getting ...
4
votes
1answer
430 views
Select and Update MAC Addresses to Same Format
I have a mysql database with a macaddress column, this contains thousands of mac addresses in various formats; uppercase, lowercase and with colons. See below for an example;
+-------------------+
| ...
0
votes
2answers
97 views
Can't see process progress, machine getting slow trying to import 500 million records
Working environment:
PostgreSQL 12.0, Ubuntu 18.10, 4GB memory, i5-3230M CPU @ 2.60GHz × 4, Os 64 bits
I'm trying to import a .csv file with more than 500 million records into a PostgreSQL db table, ...
2
votes
2answers
388 views
SSMS - Openrowset maximum file size
Apologies if this has been answered elsewhere but I couldn't find anything here or online.
Does openrowset have a file size limit? I'm trying to write a stored procedure, part of which checks if ...
2
votes
1answer
749 views
Mongodb - Will Bulk.find.remove() lock the collection
I'm planning to run Bulk.find.remove() on a very big collection. (1.2TB).
Will it block inserting or updating other records (which are not a part of the above find)?
0
votes
0answers
62 views
Import folder with Multiple CSV's into SSMS w/ out SSIS
I am wondering what's the best and most effective way to import csv's stored in a local folder into DB w/ in SSMS.
Folder path- C:\Users\ngutierrez\Desktop\Open_Air_Tables
Folder contains about 30 ...
1
vote
0answers
750 views
BULK INSERT ERRORFILE Not Created
We have stored procedure on SQL Server v12.0.5203.0. Within that procedure we have a dynamic SQL statement that performs BULK INSERT. We are trying to capture misformatted rows and continue processing ...
1
vote
1answer
811 views
How to perform builk update within mysql stored procedure
I have been trying to perform a bulk update using a single update query but unable to do so within the stored procedure.
Following is the bulk update query that run flawlessly
UPDATE `...
5
votes
2answers
935 views
Using CHECKPOINT vs GO
Typically, when one of the developers or data analysts needs to perform a very large update or deletion of data (where a truncate or truncate/insert wouldn't make sense because the data set to keep is ...
9
votes
1answer
6k views
Should I disable autovacuum on a table while I do a bulk update?
I need to perform a simple update on all rows in a table. The table has 40-50 million rows. Dropping indexes and constraints during the UPDATE results in a massive performance improvement.
But what ...
1
vote
4answers
3k views
Bulk table creation and import of data
I have a around 400 CSV files which have been exported from an old Paradox database, with each CSV representing a table within the old database. Each CSV has a different number of columns and ...
0
votes
1answer
2k views
Importing Data in Parallel in SQL Server
I have more than 100 files to import into the sql server and most of them are of 500 MB. I want to leverage the the parallel import utility of SQL server and have read a number of webpages, like the ...
4
votes
1answer
2k views
Copy Millions of Records from one table to another with the same schema, in the same database
I am trying to find the best way to copy a large amount of rows ( ~70million) from one table to another with the same schema inside the same database.
Our system currently runs with a set of A/B ...
2
votes
2answers
37k views
How to insert numerous data into temporary table?
I'm building a program which generates a T-SQL query in following form:
DECLARE @In TABLE (Col CHAR(20))
INSERT INTO @In VALUES value1, value2... value1000
GO
INSERT INTO @In VALUES value1001, ...
0
votes
1answer
2k views
Execute immediate call causes 'variable not in select list' error
Execute immediate call causes 'variable not in select list' error
hi there, i'm practicing with collections and execute immediate by combining them in plsql and have the following code:
set ...
4
votes
1answer
4k views
mongodb bulk write all or nothing?
I am learning about mongodb. If I create a bulk write is this transaction all or nothing? I have a scenario where my users can delete who they are following. For this to happen I need to delete from ...
2
votes
0answers
485 views
oracle bulk loader processing columns with newline characters
I'm trying to load a file with description columns which may contain newline characters. The file is Unix format utf16, tab separated and description columns are enclosed in ". The problem is bulk ...
0
votes
1answer
1k views
How to load 100k + Records into progres database from a CSV file?
Our project needs to load 100k+ rows into a Postgres table. We have a huge .CSV file with all these 100K+ rows and we need to load this data into a table effectively and quicker (may be in the form of ...
15
votes
3answers
30k views
Create a table from a CSV file with headers
I'm seeking to find a way to generate a new MySQL table solely based on the contents of a specified CSV. The CSV files I'll be using have the following properties;
"|" delimited.
First row specifies ...
0
votes
0answers
580 views
Create Temporary Index for bulk operation and Delete after its completed
I am moving bulk data to archive tables.
Some times query returns up to 4.5million records, which takes about 2:15 minutes for a table of 40 columns.
I want too increase the performance for the ...
3
votes
1answer
2k views
Can SQL Server Bulk Copy cause timeouts when querying the table
In SQL Server 2008 R2, can using BulkCopy to upload thousands of rows of data cause problems for queries taking place on the same table?
Specifically could it cause the queries to time out?
The ...
3
votes
2answers
3k views
Bulk insert with format file: 0 rows affected
When using the BULK INSERT command in SQL Server 2008 it returns:
(0 row(s) affected)
I am using this command to carry out the bulk insert:
BULK INSERT Test
FROM 'C:\DataFiles\Tests.dat'
...
2
votes
0answers
2k views
CSV File Into MYSQL Merging - LOAD DATA on Duplicate Key?
Hey Dba Stack Exchange,
My question to you is with regards to mysql. Right now I have a medium to small sized csv with 25k lines I need imported into mysql for a client. Problem is, their is only ...
47
votes
2answers
68k views
Optimizing bulk update performance in PostgreSQL
Using PG 9.1 on Ubuntu 12.04.
It currently takes up to 24h for us to run a large set of UPDATE
statements on a database, which are of the form:
UPDATE table
SET field1 = constant1, field2 = ...
2
votes
2answers
252 views
SQL Server BULK INSERT
I have a file saved using BCP and I want to know the specification of the table of the database that it needs to be materialized on the database. Is that possible?
Thanks
0
votes
1answer
92 views
Reinsert data in new table with new IDs
I have the following two tables:
buynsell
CREATE TABLE `buynsell` (
`id` VARCHAR(20) NULL DEFAULT NULL,
`msg` VARCHAR(255) NULL DEFAULT NULL,
`date` VARCHAR(25) NULL DEFAULT NULL,
`...
6
votes
2answers
5k views
Index before or after bulk load using load infile?
I have a database with over 1B rows and two columns that are indexed (in addition to the PK).
Is it better to have the index pre-defined in the table before the load infile or better to index after ...
32
votes
2answers
52k views
Most efficient way of bulk deleting rows from postgres
I'm wondering what the most efficient way would be to delete large numbers of rows from PostgreSQL, this process would be part of a recurring task every day to bulk import data (a delta of insertions +...
3
votes
1answer
1k views
Which MySQL table to use for read-only, bulk-loaded tables?
So I have a workload on 2 key tables in my application with the following characteristics:
READ-ONLY DATA
The data is bulk-loaded in a fully consistent manner from a single process on a schedule, ...
7
votes
2answers
4k views
Bulk Data Loading and Transaction Log
I'm currently working on a project which bulk import data from flat files (csv) about 18 different files each linking to a specific table through some stored procedure.
I followed the steps as ...
1
vote
1answer
1k views
bulk insert from file
I am often requested to upload data from file (let's say csv) into a Sql Server 2008 db.
I would like to use BULK INSERT, but I have no access to the db server file system.
Questions
is there a way ...
11
votes
1answer
3k views
Best way to re-import large amount of data with minimal downtime
I need to import about 500,000 records containing IP lookup (read-only reference) data about once a week (only three int/bigint cols).
I don't really want to worry about merging the data with the ...
4
votes
2answers
970 views
fast bulk incrementing in MySQL
I have one big table foobar describing a many-to-many-relation and containing millions of foo's, millions of bar's and every bar having several hundereds of foo's -> billions of rows.
CREATE TABLE `...
4
votes
2answers
6k views
Mysql load from infile stuck waiting on hard drive
I've got a windows 7 64 bit machine that I'm using for some load testing of a mysql db. My program uses sqlalchemy to connect and run several load from infile statements on said database. These bulk ...
2
votes
1answer
163 views
After dropping the clustered index, it is still attempting to do a clustered insert [closed]
I am trying to convert data from one database system to another. One of the tables I need to transfer and format contains over 10 million rows.
I am running the following script to do it:
USE [...
1
vote
1answer
429 views
In a simple table with no complex indexes, does a large number of existing rows slow down an INSERT?
I have this small, simple table:
CREATE TABLE `ads_tracker` (
`id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`type` smallint unsigned NOT NULL,
`reference` smallint unsigned NOT NULL,
...
22
votes
3answers
35k views
Why is 'LOAD DATA INFILE' faster than normal INSERT statements?
I've read an article that mentioned we can achieve 60,000 inserts per second by using the LOAD DATA IN FILE statement, that reads from csv files and inserts the data into a database.
Why should it ...
1
vote
2answers
9k views
What would be the fastest approach to bulk update this mysql table (Innodb)?
I have a table with 100M rows. I am able update my table like 850 updates / minute. How to speed this up?
I am using following approach:
mysql -uroot < query_file
where query_file contains 1M ...
1
vote
1answer
357 views
Importing large file while keeping table available for other operations
We need to import millions of rows in a quite big table.
Say like 5 million rows into a table with more than 1 billion rows.
After preprocessing the import file we will have update operations and ...
14
votes
2answers
12k views
What specifically does OracleBulkCopy do, and how can I optimize its performance?
To summarize the specifics: We need to stage approximately 5 million rows into a vendor (Oracle) database. Everything goes great for batches of 500k rows using OracleBulkCopy (ODP.NET), but when we ...
4
votes
3answers
466 views
How to send+update on duplicate a big time series to SQL server
I use a SQL Server 2008
I would like to send (and update on duplicate datetime index) a big time series (more than 1000 datapoints and eventually much more than that).
What is the best way to do so?
I ...
1
vote
1answer
919 views
How do I load them as varbinary(MAX) format into SQL Database?
There are 1000 picture that are jpeg format.How do I load them as varbinary(MAX) format into SQL Database?But at the same time I want the bulk insert functionality.
8
votes
4answers
973 views
Suggestion for Bulk Data Import
We are working on a project which requires import data from Excel spreadsheet on daily basis. The data will be import from pre-define template and what we're thinking, first we will upload data in ...
4
votes
2answers
4k views
How do you copy a table from MySqlServer_A to MySqlServer_B?
I have a daily task that collects data from 3 MySql Servers and then pushes that aggregate data back out to each of the servers deleting the previous days data. All servers are MySql. I would like to ...
3
votes
1answer
302 views
Can I use dbms_redefinition with bulk data transformations for better results?
Here is a situation for bulk loading in Oracle:
Schema A { T1, T2, T3, T4}
Schema B { TR1, TR2}
Where schema B is re-factored from schema A. Both exist in same DB. Schema A has terabytes of data ...
5
votes
2answers
3k views
BULK INSERT - best usage
I need to export 150 million rows of only int/bigint columns from one SQL instance to another. I am using BCP queryout to export the rows and BULK INSERT to import the rows to another table.
I split ...
