build sql script: created

Showing posts with label created. Show all posts

Thursday, March 22, 2012

Bulk Insert Statement

I am trying to perform bulk insert but it is not working.

I created a table

INSERT INTO [demo].[dbo].[test]

([ID] ,[Plates] ,[Driver])

VALUES

(<ID, int,> ,<Plates, varchar(7),> ,<Driver, varchar(7),>)

I am tryin to insert a file.txt

100, 091-184, DOUG798
101, 406-846, DALL152
102, 384-080, TIZE489
103, 064-460, NAMO927
104, 101-366, CETI001
105, 109-366, JESS111

Any help, please

Juvan

hi Juvan,

the syntax you used is not correct for BULK INSERT statement,
have a look at http://msdn2.microsoft.com/en-us/library/ms188365.aspx for the statement's full synopsis and syntax..

[FILE d:\fmt.txt]

9.0

1 SQLCHAR 0 10 "," 1 FirstName "Latin1_General_CI_AS"

2 SQLCHAR 0 10 "," 2 LastName "Latin1_General_CI_AS"

3 SQLCHAR 0 8 "\r\n" 3 BDate "Latin1_General_CI_AS"

[/FILE d:\fmt.txt]

[FILE d:\studs.txt]

Juvan, Bonni, 19701015

Andrea, Montanari, 19651030

[/FILE d:\studs.txt]

SET NOCOUNT ON;

USE tempdb;

CREATE TABLE dbo.Students(

FirstName varchar(10) NOT NULL,

LastName varchar(10) NOT NULL,

BDate datetime NOT NULL

);

BULK INSERT dbo.Students

FROM 'd:\studs.txt'

WITH(FORMATFILE = 'd:\fmt.txt') ;

SELECT * FROM dbo.Students;

DROP TABLE dbo.Students;

--<--

FirstName LastName BDate

- - --

Juvan Bonni 1970-10-15 00:00:00.000

Andrea Montanari 1965-10-30 00:00:00.000

regards

I have a simple table I have created to do a bulk insert with a SP. Everything works fine but when the sp runs, the first column in the rows are like

"0243567

My bulk insert

BULK INSERT [Mas500_app].[dbo].[Test] FROM 'C:\Documents and Settings\Chris\Desktop\testbulk1.txt' WITH (FIELDTERMINATOR = '"~"', ROWTERMINATOR = '"')

the testbulk.txt file

"0243567"~"08/26/06"

How would I go about in my bulk statement to remove the " from the first column in the row.

ThanksWell there is no such options in bulk statement to remove the " from "0243567

the trailing " is removed becuase you have specified it as the field terminator

however you have two options
1) Change the txt file to remove the " from the first row data
2) issue the follwoing update statement immediately after the bulk insert statement

update [Mas500_app].[dbo].[Test]
set column1 = replace( column1 , " , '' )

HOwever the above replace statement is helpful only if your data in column1 does not contain any " else you will have to go for more elaborate procedures|||I think this might do what you want (it trims the first character off a field value).

UPDATE MyTable
SET MyField = SubString(MyField,2,Len(MyField))

HTH ~Georgesql

Thursday, March 8, 2012

Bulk Insert fails to import data files created on Unix

It seems to me that files created on Unix machines with line terminator \n, or chr(10), cannot be imported using the Bulk Insert statement. Is this a bug, or an oversight by Microsoft? Does this mean that unless one replaces all \n with \r\n, there is no way to use Bulk Insert to import Unix files? This is a very strange behavior by MSSQL. Even lessor programs such as Excel and Word automatically recognize chr(10) as a line termination character. Am I missing something, or is this just the way MSSQL is?

You will need to use a format file, in this you can specify the terminator for the last column in a row.

Have a look in BOL. This page shows an example of a file using /r/n which you can obviously change

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ecfc546d-f708-45f4-878d-fb71b5fd1a0a.htm

|||

Of if you are using the BULK INSERT TSQL statement look at this page

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/be3984e1-5ab3-4226-a539-a9f58e1e01e2.htm

|||I figured out that the problem has to do with MSSQL's native behavior. It turns out that whenever it sees \n, it automatically converts it to \r\n, without notifiying the user. There are at least three ways to work around this strange behavior: 1) replace every \n in your file with \r\n before using Bulk Insert, 2) build your sql statement dynamically either in a stored procedure or VB.net, i.e., use & chr(10) & ,or + chr(10) +, instead of '\n' as the line terminator in your statement, and 3) load your file into a Datatable via ADO.net, and then insert the entire Datatable into MSSQL.|||

Note sure what figuring out was required, as BOL has an example that just works.

|||

I have never succeeded to convince BULK INSERT to read Unix

files, and I find one of these solutions usually works:

1. See if the process that moved the files from the Unix

machine can correct the line ends (for example, ftp can do

this)

2. Run the Unix utility unix2dos on the files before they

leave the Unix machine, or afterwards, under the Cygwin

Unix shell for Windows. (Or write a tiny command-line

Windows program to do this.)

Steve Kass

Drew University

ktto@.discussions.microsoft.com wrote:

> I figured out that the problem has to do with MSSQL's native behavior.

> It turns out that whenever it sees \n, it automatically converts it to

> \r\n, without notifiying the user. There are at least three ways to work

> around this strange behavior: 1) replace every \n in your file with \r\n

> before using Bulk Insert, 2) build your sql statement dynamically either

> in a stored procedure or VB.net, i.e., use & chr(10) & ,or + chr(10) +,

> instead of '\n' as the line terminator in your statement, and 3) load

> your file into a Datatable via ADO.net, and then insert the entire

> Datatable into MSSQL.