July 9, 2012 at 12:31 pm
hi,
I am trying to create a Bulk Insert. I thought I would start off simple by creating a test file with only three field types, Bigint, varchar and DataTime.
Below is my Create table statement, the bulk insert, sample data and Format file.
In this current state I am receiving an error message "Invaild column Name 'RollDt' ".
What might be causing this error?
Create Table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Test](
[RollNo] [bigint] NULL,
[Location] [varchar](50) NULL,
[RollDt] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Data file to Import. Save as RollTest.txt (Be sure to not add a RETURN after the second line. The file must only be 2 lines long.)
88888^NT^07/01/2012 00:00:00
9999^CO^06/01/2012 00:00:00
FormatFile: Save as FormatRoll.txt
9.0
3
1 SQLCHAR 0 0 "^" 1 RollNo ""
2 SQLCHAR 0 0 "^" 2 Location ""
3 SQLCHAR 0 0 "\r" 3 RollDt ""
Bulk Import
INSERT INTO Test
(RollNo,Location,RollDt)
SELECT RollNo, Location,RollDt
FROM OPENROWSET(BULK 'C:\BulkInsert\RollTest.txt', FORMATFILE = 'C:\BulkInsert\FormatRoll.txt', FirstRow=1) as Z
go
July 9, 2012 at 12:51 pm
Well, your format file is wrong for starters... So change the columns to 3 instead of 2. Also, assuming this is 2008 since you are in a 2008 forum, change the 9.0 to 10.0
FormatFile: Save as FormatRoll.txt
10.0
3
1 SQLCHAR 0 0 "^" 1 RollNo ""
2 SQLCHAR 0 0 "^" 2 Location ""
3 SQLCHAR 0 0 "\r" 3 RollDt ""
Jared
CE - Microsoft
July 9, 2012 at 12:52 pm
Change format file to recognize the 3rd column:
9.0
3
1 SQLCHAR 0 0 "^" 1 RollNo ""
2 SQLCHAR 0 0 "^" 2 Location ""
3 SQLCHAR 0 0 "\r" 3 RollDt ""
July 9, 2012 at 12:55 pm
Jared,
Thanks. That enabled me to get to the original error I needed help with. So that I didn't have to create a second post, I added the date field to my original work and I missed that value. Thank you.
Now on to the real problem. With the corrected Format file, I receive the error message, "Error converting data type varchar to bigint". I assume this is occurring on the first field. What do I change to eliminate this error?
July 9, 2012 at 1:00 pm
Try changing the "\r" to "" or "\r\ n" (without the space before the n... I had to do that to get this to post properly)
Jared
CE - Microsoft
July 9, 2012 at 1:10 pm
The best way to do is to first generate the format file using the bcp.Once that is done.You can use that file to bulk import or for bcp.
e.g. use below
bcp "<dbname>dbo.test" format nul -f test_format.txt -S <servername> -U<username> -c<you can use -n-N,-w)
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 9, 2012 at 1:14 pm
Gullimeel (7/9/2012)
The best way to do is to first generate the format file using the bcp.Once that is done.You can use that file to bulk import or for bcp.e.g. use below
bcp "<dbname>dbo.test" format nul -f test_format.txt -S <servername> -U<username> -c<you can use -n-N,-w)
That may help with the table and version and such... but it is blind to the format and delineation of the actual file.
Jared
CE - Microsoft
July 9, 2012 at 1:17 pm
Yes.. But then you have to modify it according to your need.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 9, 2012 at 1:19 pm
Jared,
Thank you that worked. I had written a Bulk Import about 2 years ago and I used that as my model. That older import (which still works) only has a /r. Oh well, always more to learn.
GulliMeel,
I can't spend a lot of time on this at the moment as I am working for someone else. But I will try BCp this evening. Thanks,
July 9, 2012 at 1:21 pm
Glad to help! Below is the link I use for reference:
http://msdn.microsoft.com/en-us/library/ms191479.aspx
Jared
CE - Microsoft
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply