Bulk insert and collations

1). I was trying to do bulk insert t temp tables, so because temdb has a
Latin1_General_CI_AS collation, an my database has a
SQL_Latin1_General_CP1_CI_AS one I was unable to do joins untill I created
special tables in my database to truncate before bulk inserts.
2). Another question: all fields in bulk insert file created not by me are
nullable varchars,but it is considers an error when there is no fields
terminators in the last row (it is a timestamp) and there is only only row
terminator .
Do I need to fix datafile or I can change something in bulk insert statement
format or settings?On Thu, 26 Jul 2007 07:56:04 -0700, UncleSam89
<UncleSam89@.discussions.microsoft.com> wrote:
>1). I was trying to do bulk insert t temp tables, so because temdb has a
>Latin1_General_CI_AS collation, an my database has a
>SQL_Latin1_General_CP1_CI_AS one I was unable to do joins untill I created
>special tables in my database to truncate before bulk inserts.
Sorry, I don't see what the question is. You can specify the
collation for any character column in a table, so there is no reason
to use tempdb just to establish a specific colation.
>2). Another question: all fields in bulk insert file created not by me are
>nullable varchars,but it is considers an error when there is no fields
>terminators in the last row (it is a timestamp) and there is only only row
>terminator .
>Do I need to fix datafile or I can change something in bulk insert statement
>format or settings?
I think you are saying that the last row of the table isn't a valid
data row. In the past I have had a quick-and-dirty command line
program written to fix the data file before loading it.
Roy Harvey
Beacon Falls, CT|||I don't using tempdb to force the particular collation, but to avoid creating
too much bulk crap in my own database. I saw in some of collation relared
threads here the use of collation in the comparisons, but the 'collate'
keyword in create statements didn't help.
Does your answer means that I am right now doing only possible stuff to
resolve my problems?
"Roy Harvey" wrote:
> On Thu, 26 Jul 2007 07:56:04 -0700, UncleSam89
> <UncleSam89@.discussions.microsoft.com> wrote:
> >1). I was trying to do bulk insert t temp tables, so because temdb has a
> >Latin1_General_CI_AS collation, an my database has a
> >SQL_Latin1_General_CP1_CI_AS one I was unable to do joins untill I created
> >special tables in my database to truncate before bulk inserts.
> Sorry, I don't see what the question is. You can specify the
> collation for any character column in a table, so there is no reason
> to use tempdb just to establish a specific colation.
> >2). Another question: all fields in bulk insert file created not by me are
> >nullable varchars,but it is considers an error when there is no fields
> >terminators in the last row (it is a timestamp) and there is only only row
> >terminator .
> >Do I need to fix datafile or I can change something in bulk insert statement
> >format or settings?
> I think you are saying that the last row of the table isn't a valid
> data row. In the past I have had a quick-and-dirty command line
> program written to fix the data file before loading it.
> Roy Harvey
> Beacon Falls, CT
>|||On Fri, 27 Jul 2007 06:02:01 -0700, UncleSam89
<UncleSam89@.discussions.microsoft.com> wrote:
>I don't using tempdb to force the particular collation, but to avoid creating
>too much bulk crap in my own database. I saw in some of collation relared
>threads here the use of collation in the comparisons, but the 'collate'
>keyword in create statements didn't help.
>Does your answer means that I am right now doing only possible stuff to
>resolve my problems?
When I need a few staging tables for imported data I generally put
them in the target database with an _Imported suffix on the name. When
I have a LOT of tables for importing data, or the import process
becomes too large or complex, I create a Staging database just for
those tables.
It might be worth mentioning that I never import data directly into
production tables, always into staging tables of some sort. In some
cases I even build history tables for the bulk loaded data and assign
batch identifiers so I can trace back any problems to their origin -
particularly useful when the data source is third party with a track
record of screwing things up.
I never explicitly create any objects on tempdb in production as
tempdb is reinitialized each time SQL Server starts and anything in
tempdb is lost.
Roy Harvey
Beacon Falls, CT