January 2, 2009 at 1:39 am
Hi,
Happy new year.
A question about a bulk insert i am trying to do.
I am getting a syntax error (Incorrect syntax near the keyword 'BULK'.) on this statement:
I've looked in the MSDN and I believe the syntax is correct but maybe I am overlooking something??
INSERT INTO vwAankomstenBulk
SELECT
aanleveranciernr,
aanartikelnr,
aanmerk,
aanclass,
aanOGnr,
cast(aandatumaankomst as datetime),
aanvesnummer,
aanaantalsrtikelen,
aanOGwaarde,
aansoort
FROM OPENROWSET(BULK N'C:\bap\import\INKM.20081229', FORMATFILE='C:\bap\import\aankomsten.fmt') as t1
Thanks in advance,
Regards,
Heisa
January 2, 2009 at 1:49 am
The bulk provider was intreduced in SQL Server 2005. You can't work with it if you use SQL Server 2000.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 2, 2009 at 1:58 am
oh,
That would explain the systax error 🙂
Then I have another question,
I wanted to use this statement:
SET DATEFORMAT ymd
GO
BULK INSERT vwAankomstenBulk FROM 'C:\bap\import\INKM.20081229' WITH (FIELDTERMINATOR = '";"',
FORMATFILE='C:\bap\import\aankomsten.fmt')
GO
But one column in the import file is a timestamp, the date notation is like this: yyyymmdd
When I execute the statement I get the following error:
Bulk insert data conversion error (type mismatch) for row 1, column 11 (aanDatumAankomst).
The column in SQL Server is a datetime column
Thanks in advance,
Regards,
Heisa
January 2, 2009 at 2:14 am
Timestamp is a data type that has noting to do with neither time nor date (and of course nothing to do with stamps?). Time stamp is a binary value that users have no control over it. The server modifies the timestamp value automatically when ever the record is updated.
As for your problem – it will be easier if you’ll post your table’s DDL and attached a text file that contains few records that you are trying to import. Without it, it will be hard to know what went wrong.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 2, 2009 at 2:26 am
Thx,
I've added a zip file with the files:
importfile.txt - contains a few records of the import;
tblaankomsten.sql - contains the table script;
vwaankomsten.sql - contains the script of the view on the table;
aankomsten.fmt - the FORMAT FILE used to BULK insert the file.
I created a view on the table to import the file, ignoring the identity column.
Thx in advance!
Regards,
Heisa
January 2, 2009 at 3:14 am
Unfortunately I wasn’t able to import the file and got the same error that you got. I played with it a bit, but no matter what I tried, it just didn’t work. I guess that the bulk insert statement doesn’t work with the same date formats that can work with other statement such as select, insert, etc’. The one work around that I can think about is to create a staging table and define the column aanDatumAankomst as char(8). Them modify the FMT file and define that column as SQLCHAR instead of SQLDATETIME. Import the file to the new table, and then insert the data to the real table. I know that it isn’t the what you expected, but maybe someone else would be able to give you a better solution.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 2, 2009 at 3:28 am
Ok thx!
I'll try to find a solution or otherwise use your work-around.
Thx
Regards,
Heisa
January 2, 2009 at 3:43 am
If you’ll find a solution, pleas post it, so all of us will learn it.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 7, 2009 at 4:15 am
Hi,
If I use the BCP tool and change the SQLDATETIME field to SQLCHAR, it works! The format of the datetime in the importfile is yyyymmdd.
BCP is not as strict as the BULK INSERT t-sql.
I created a testfile with an integer, a float, a char and a datimetime field.
In the format file I declared each field as a SQLCHAR. In the database table the columns are declared as integer, float, char and datetime.
bcp
Regards,
Heisa
January 7, 2009 at 10:42 pm
Thank you for the information. I’m sure that it will help someone else that will encounter the same problem.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply