Mariadb – how to insert big data in mariadb with execution time less than max_statement_time value

On SQL Server 2005, and SQL Server 2012, I did the following:

USE tempdb;
CREATE TABLE ImpTest
(
    ImpTestID INT NULL
    , ImpTestDate DATETIME NULL
);
GO
BULK INSERT ImpTest FROM 'C:\SQLServer\ImportTest.txt' WITH (FIELDTERMINATOR=',');
SELECT * FROM ImpTest;
DROP TABLE ImpTest;

With the following data:

1,
2,20130406
3,NOT_DATE
4,
5,20130409

On both servers, I received the following:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified
codepage) for row 3, column 2 (ImpTestDate).

Perhaps, as Aaron noted, this is a bug that needs to be addressed through http://connect.microsoft.com

I'm wondering about some of the details of your system, including locale settings, collations, physical format of the import file (is it from a Unix system? etc).

Use the OUTPUT clause of the INSERT statement to capture the identity values created in data. Then use that to write to relation.

create table #data ( id int  primary key
                  , value int not null);


if not exists (select id from data where value = 1337) 
    insert into data (value) 
    OUTPUT INSERTED.id, INSERTED.value
        INTO #data
    values (1337);

select * from #data;

insert into relation (someid, dataid) 
select 
    42, id 
from #data;

select * from relation;