Questions tagged [bulkinsert]

1

votes
0

answer
48

Views

Laravel bulk insert and with relational one-to-one certain times

I know that that I can do a bulk insert like: $data = [ ['name' => '...', 'age' => ...], ['name' => '...', 'age' => ...], ['name' => '...', 'age' => ...], ]; Model::insert($data); But my problem is that I need to do a bulk insert that sometimes depending on a item value can have a one-to-one relatio...
user2722667
1

votes
1

answer
160

Views

Laravel bulk insert with one-to-one relation

I know that I can insert multiple records in laravel with: $data = [ ['name' => '...', 'age' => ...], ['name' => '...', 'age' => ...], ['name' => '...', 'age' => ...], ]; Model::insert($data); But is is possible to do a bulk insert that also has a one-to-one relation? So that I insert data into two...
user2722667
1

votes
1

answer
37

Views

Conditional insert sql server for more than one row

I am trying to insert a value for every Call Record with a specific date, note and action when a client matches listed emails. The problem is that there are many other call records for these clients, so if I use a select query in the Insert statement, it will add many more rows that I want - I just...
Robert Woolfson
1

votes
2

answer
452

Views

What's the correct format of the format file for double quotes for every field in a csv file when doing bulk inserts in SQL Server?

I am trying to use bulk insert to import a csv file where each field is delimited by double quotes. This is an example of one row int the file: 'SONC','Sonic Corp.','981','amc','0.22','0.24','+0.02','+9.09%','5.15%','' The format file I used is below which was created by bcp based on the destination...
Tony_Henrich
1

votes
2

answer
444

Views

Removing double quotes before bulk insert

I'm trying to load a file using bulk insert, but data isn't inserted correctly because some of the data is covered by quotation marks. I've tried using a format file, but it doesn't work becasue not ALL the rows in that column contain quotes. Only some do. e.g. columna abc cdf 'dfd' dfs 'aee' So my...
NT-Hero
1

votes
1

answer
142

Views

Conflict with multiple insert in two tables

I am trying to insert all the rows of cart Table of userId=1 into Order and OrderItem table the Order table consists of following columns: ORDER TABLE 1.OrderId (int) 2.OrderDate(datetime) 3.userId (int) 4.orderStatus (varchar) the user_tbltable is reference to the Order table with PK of userId and...
Ravi Patil
1

votes
0

answer
46

Views

SQL Server bulk insert issue

I have a windows service running each 5 seconds and this service just call a stored procedure in a SQL Server database, this stored procedures executes a bulk insert like this: BULK INSERT #temp_table FROM '\\path_to_csv.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a', KEEPNULLS, TABLOCK);...
Alexxxx
1

votes
0

answer
16

Views

Sqlite/android: insert actually replaces rows

In Udacity's Developing Android Apps, the app they're building displays the weather forecast in a listview, after fetching the data from the openweathermap api. The data is saved into the db via the function bulkInsert shown below. Updates are called by the option 'Refresh' in the menu. No code han...
Ken Hagdal
1

votes
0

answer
346

Views

how to use pymysql executemany insert many rows and get the ids of each rows

This is a problem in a distribute environment!!! I have processes in many distribute computers, in every process I use pymysql to insert many rows into a table with autoincrease id, I wish I could get the ids of each row. I have solution when I do it on a single node:I just get max id of the table a...
Tarjintor
1

votes
0

answer
187

Views

How to use Format File (.fmt) in PowerShell SqlBulkCopy?

Is it possible to use Format File (.fmt) in PowerShell SqlBulkCopy? I have a script in PowerShell that use the Data.SqlClient.SqlBulkCopy but I can not found how to use Format Files with this method... What I'm using: $bulkcopy = New-Object Data.SqlClient.SqlBulkCopy('connection string', [System.Dat...
tgoncalves3
1

votes
0

answer
47

Views

SQLBulkCopy with type mismatch exception and Datetime complaints about

I want to BULK Insert in a table with different types and a auto-increment column. The problem is, that i get the messagte it is not possible to convert from Int32 to Datetime. This is my table Structure, the id is an auto-increment identity column. in the first Method of this section, i create the...
1

votes
0

answer
148

Views

Fastest way to generate a lot of valid test data in SQL Server?

I want to generate a bunch of test data in SQL Server, across multiple tables and indexes to simulate a real system with a lot of data. I will not necessarily be generating the data on the same server as the DB (so I will have to go across a network). The DB server would not need to be usable until...
Andrew
1

votes
0

answer
17

Views

How to bulk insert data into a table when the primary ID is set with a sequence

I'm trying to use SqlBulkCopy to insert a number of rows into a table where the Id column is set with a sequence. The sequence and table look something like: CREATE SEQUENCE [dbo].[MyTableId] AS [int] START WITH 1 INCREMENT BY 1 MINVALUE -2147483648 MAXVALUE 2147483647 CACHE 10 GO CREATE TABLE [d...
izb
1

votes
1

answer
185

Views

Got invalid UTF-8 value serializing '�8' during MongoDB bulk write

I'm trying to store relatively huge portion of data into MongoDB, using BulkWrite for multiple inserts. Data comes from external resource, so I cannot fully control it's content. Amount of data records can reach 15k and even more for single bulk operation. Here is the part of my code: $bulk = new Bu...
Bushikot
1

votes
0

answer
27

Views

Trigger is not fired When Bulk insertion happened through Import Data option in SQL Server 2017

I have created a table along with trigger, When I am trying to Import the data to SQL server from Excel, trigger is not fired When Bulk insertion happened through Import Data option in SQL Server 2017
Arun G
1

votes
1

answer
3.4k

Views

Bulk insert with Spring Boot and Spring Data JPA not working

I know that there are many similar questions about this argument, but I really need a working solution. I'm trying to configure Spring Boot and Spring Data JPA in order to make bulk insert in a batch. The target is: commit each N-records, not every single record when making repository.save() action....
Alessandro C
1

votes
1

answer
34

Views

Speed up MySQL insert stored procedure

I am trying to speed up the process for inserting 1000s of records from a CSV. I have a contact table that joins to a contact phone table. This is my pertinent SQL structure: Contact Table +----+-----------+----------+------------------+------------+----------------+ | id | firstName | lastName |...
Gremash
1

votes
0

answer
329

Views

MongoDB 3.6-4.0 bulk-insert limitations

According to official MongoDB documentation, since version 3.6 the limit for a bulk-insert is 100,000 operations (compared to 1000 operations in older versions). As a benchmark, using the Java client I insert 100K documents to a collection in varying bulk sizes from 1 to 100K inserts. These are the...
Forepick
1

votes
1

answer
62

Views

MariaDb ColumnStore BulkInsert

I have some code that I'm writing which uses a bulk insert and looks something like this: ColumnStoreBulkInsert b = d.createBulkInsert('pst', 'events', (short) 0, 0); try { for (Map record : records) { try { for (int i = 0; i < schema.length; i++) { Object value = record.get(schema[i].toLowerC...
Michael Gofman
1

votes
0

answer
184

Views

SQL-Server - Bulk Insert Error 7301

Using SQL Server 2016, I am working on a legacy system that requires its nightly import to run via bulk insert. I know SSIS is a better option, but not one that's available to me. I am uploading the file from the local machine with the following command: BULK INSERT DataImports.staging_Companies FRO...
Matthew Baker
1

votes
2

answer
84

Views

Why isn't the Error File for Bulk Insert working?

Thanks for the feedback. H‌ere is my table that I made to force errors. But it doesn't input anything unless all the columns are 255. These are close to the right column sizes‌. This is the staging table. I expect an error file to be created but none are created. The message i get is: Msg 8152,...
Jacob Pressures
1

votes
1

answer
76

Views

Typo3 - bulkInsert from a scheduled command - where should the code reside?

I have created an extension using Extension Builder. This has created my necessary table and repository and controllers files. I have created a command class with a method that is called once a day (via the scheduler extension). The purpose of the command is to truncate a table and replace the data...
James
1

votes
0

answer
99

Views

Data inserted using SqlBulkCopy takes 9x to 92x more space in the database than using INSERT statements

I have a .NET Core 2.0 console application inserting data into a SQL Server 2017 (14.0.1000.169) table. It previously used simple INSERT statements. I changed it to use SqlBulkCopy, but found that it started using up space in the DB very quickly! Measuring the space used before and after data insert...
EM0
1

votes
2

answer
189

Views

My Sql Bulk inserts loading the data with truncating

I am trying to load the data from CSV file to MYSql database through bulk insert option. Here are the below create table syntax and CSV file CREATE TABLE discounts ( id INT NOT NULL , title VARCHAR(10) NOT NULL, expired_date DATE NOT NULL, amount VARCHAR(255 ) NOT NULL ); CSV file format: '475','Ba...
Karthick Sambanghi
1

votes
1

answer
199

Views

EFUtilities bulk InsertAll with children entities

I am trying to bulk insert a list of items in my SQL Server by using the EFUtilities package. Is there a way i can insert an entity with multiple navigation properties? All i have found is this EFBatchOperation.For(DbContext, DbContext.Products).InsertAll(batch); EFBatchOperation.For(DbContext, DbCo...
DKar
1

votes
2

answer
83

Views

Bulk Insert with a wrong data in a row [closed]

I am trying to set up an automated data workflow for my company, inserting data into a database (microsoft sql server) every Monday. The 'Bulk Insert' statement will insert data row by row. However, if it finds wrong data in the middle, it will stop the process and it won't take out the data that w...
James Heo
1

votes
1

answer
69

Views

How to save bulk records as transaction to SQL server more efficiently?

I am working on a C# MVC application. In this application user is uploading data from EXCEL spreadsheet and data is showing to grid. After it has been showing to grid, user hit 'validate data' button. Application needs to perform UI (data length, empty field, data formats, etc.) validation and add...
SilverFish
1

votes
0

answer
10

Views

Json file to MongoDB insert fails

I am using C# to insert a JSON file to MongoDB.I am getting a JSON input file like in the bottom of this question. Table columns and rows may be above hundred. How to insert it as each record in MongoDB? .I am getting the duplicate _id error while inserting with 'collection.insertone(document)' comm...
Sreejesh Babu
1

votes
1

answer
76

Views

How to skip an existing object instance when creating resources in bulk python

I am trying to create a resources in bulk. While the resources are created I have the matric_no has to be unique. If the value of an existing matric_no is uploaded together with the some new entries, I get an integrity error 500 because the value already exists and it stops the rest of the values fr...
Chioma Igbokwe
1

votes
0

answer
40

Views

Multi-row conditional insert with DEFAULT values

Here is a simplified version of a PostgreSQL query I have, to insert multiple rows into a table: INSERT INTO my_table (a, b, datasource) VALUES ('foo', 'bar', 'my source'), ('foo', DEFAULT, 'my source'), (DEFAULT, 'bar', 'my source'); Notice that a DEFAULT value could appear in either column a or b....
Inkling
1

votes
0

answer
82

Views

Entity Framework extentions bulkinsert ignores set timeout

We are trying to insert a large dataset (2+ million records) into our database, using Entity Framework bulk insert. The extensions is EntityFramework.BulkInsert.Extensions (nuget EntityFramework.BulkInsert-ef6) Version 6.0.2.8. We are using this the following way: using (StockEntities context = new...
Vincentw
1

votes
1

answer
19

Views

Inserting multiple rows in one table from two other tables

I need to insert multiple rows into a table with one query (instead of using looping, which is possible, but a huge waste of server resources.) I have three tables: -Members MemberID (int) -Items ItemID (int) -MemberInventory MemberID (int) ItemID (int) When I add a new member, I need to select that...
Michelle H
1

votes
0

answer
28

Views

Is it possible to gain a bigger optimization boost for the following batch+bulk INSERT approach for SQLIte in Android?

I'm developing a cross-platform data importer and I'm currently trying to optimize the overall import performance on both JRE and Android runtimes. Having JDBC in use, I managed to optimize the importer on test data (~500K rows if I'm not mistaken) for the JRE runtime from 12s to 3,5s-4s just using...
lsh
1

votes
0

answer
29

Views

Insert query won't return on rails

In first place, sorry for my bad English. I had a rails 2.3.1 project and i'm updating it to version 5.2.1. On this project, i have a delayed_job that inserts multiple lines on a database table. The code that i'm using to insert is: ActiveRecord::Base.connection.execute(' INSERT INTO (id, col2, col...
Arthur Bender
1

votes
1

answer
21

Views

Bulk Insert support for Unicode seperator

I am using Azure data factory to Archive data from Azure Sql Db to Azure Blob Store and Bulk insert to retrieve the data. I am using below as row and column seperator. Column delimiter:\u0001 Row delimiter:\u0003 My Bulk Insert is below. BULK INSERT mytable 'MyPath/file.txt' WITH (DATA_SOURCE = 'MyS...
Alivia
1

votes
0

answer
44

Views

How to speed up exporting dataframes to MS SQL Server? R is twice as fast

I am very well aware that there are many similar questions, and most answers suggest things like bulk inserts of CSVs (which would be more headache than exporting from R). However, I am not aware of any comparison with R which addresses if pandas.DataFrame.to_sql() can ever reach the same speed as R...
1

votes
4

answer
345

Views

Do you know a Bulked/Batched Flows Library for C#

I am working on a project with peek performance requirements, so we need to bulk (batch?) several operations (for example persisting the data to a database) for efficiency. However, I want our code to maintain an easy to understand flow, like: input = Read(); parsed = Parse(input); if (parsed.Count...
ripper234
1

votes
1

answer
1.8k

Views

What bcp format file that I'm gonna used to insert 59 columns?

First Observation, limitation with this BCP format file Data File: (Continuous up to three lines) 1;a;b;c;d;e;f;; 2;d;e;f;g;h;i;; 3;g;h;i;j;k;l;; 4;j;k;l;m;n;o;; 5;m;n;o;p;q;r;; 6;p;q;r;s;t;u;;What bcp format file that I'm gonna used to insert 59 columns? Format File: 9.0 9 1 SQLCHAR 0 0 '' 0 x '...
user55700
1

votes
1

answer
1.3k

Views

How to bulk insert into a table in sql server 2005

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;...
Himadri
1

votes
2

answer
1.6k

Views

Passing Binary Data to a Stored Procedure in SQL Server 2008

I'm trying to figure out a way to store files in a database. I know it's recommended to store files on the file system rather than the database, but the job I'm working on would highly prefer using the database to store these images (files). There are also some constraints. I'm not an admin user, an...
Joe Majewski

View additional questions