BCP Utility

Hi all,

I want to use the BCP utility to import data from a .dat file into my database. The .dat file contains a table called xv_Appointments containing the following fields:

AppointmentKey
SurgerySlotKey
PatientKey
Cancelled
Continuation
Deleted
TimeArrived

I would like to import only two of these fields into a table called tbl_Appointments e.g.

AppointmentKey
TimeArrived

I can't seem to get the BCP util to do this. It only works if I import all of the fields from xv_Appointments. Does anyone know if this is possible?

ThanksReplace IN with FORMAT and add "-f file_name.fmt" without quotes on the command line when issuing BCP statement.

Open your favorite text editor and modify the format file to look something like this:8.0
7
1 SQLINT 0 0 "\t" 1 AppointmentKey ""
2 SQLCHAR 0 255 "\t" 0 SurgerySlotKey SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 255 "\t" 0 PatientKey SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 255 "\t" 0 Cancelled SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 255 "\t" 0 Continuation SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 255 "\t" 0 Deleted SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 255 "\r\n" 7 TimeArrived SQL_Latin1_General_CP1_CI_AS|||Thanks for replying and you've certainly pointed me in the right direction. I've almost got it working but for a couple of problems. The first problem is that when it imports the Appointment key it seems to be adding a tab character or something to before the key e.g. 341084.80096.Appointment becomes 341084.80096.Appointment when imported to the new table. Also, I can't get it to import the date field 'TimeArrived' (please see attached), SQL server throws up the following error message:

Server: Msg 4829, Level 16, State 1, Line 1
Could not bulk insert. Error reading destination table column name for source column 8 in format file

Even although the column name is correct...I have tried importing the other date fileds and it does the same thing.

I have attached my .fmt file for you to have a look at.

Thanks|||The second token in the format file (where you have SQLCHAR etc) refers to the format of the field IN THE HOST FILE, not on the server. If this a date in ASCII readable string format, e.g. "Jan 1 2004 10:00AM" or "2004-01-01" the this should be set to SQLCHAR

see Books online : bcp utility / format files / Using format files|||Tried setting the date field to SQLCHAR (see attached file) but it's still giving me the same error message:

Server: Msg 4829, Level 16, State 1, Line 1
Could not bulk insert. Error reading destination table column name for source column 8 in format file '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\bcp.fmt'

Here's the SQL I'm using:

BULK INSERT tbl_UpdateTimeArrived FROM '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\xv_Appointment.dat'
WITH (FORMATFILE = '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\bcp.fmt

The only other thing that may affect it is the .dat file in question is from a SQL Server 6.5 database and I'm importing it into a SQL 2000 database.|||maybe your SQLBIT columns should be SQLCHAR as well...|||Still didn't work but I'm slowly getting to the bottom of it. Tried importing a non-date, non-bit field and it imported fine (again see attached) - so it seems to be the date fields that are causing the problem...Any ideas?|||can you attach say top 5 lines of your data file ?|||I've figured it out. I need the same number of fields in the destination table as the table in the .dat file. Even although I only want to import two fileds. In effect, the destination table needs to be an exact replica regardless of whether I want to use all the fields or not!!

If anybodyt knows any different then please let me know.