I need to do a bulk update of 1 field in a data-table (removing
zero-padding). The update is expected to update 52.5 million records.
What techniques could I use so I can ensure
1) Consistency in the transactional log backups (every 10 mins)
2) Consistency in the full DB backup every day
3) Disk drive of Transaction logs do not get filled up completely (I suspect
even if I set rown count to 10,000 and loop through, disk-space would still
be used')
Target environment
SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
(Different disk drives for data and log files)A normal set based operation will do fine. update my table set myfield =
'mynewvalue'
However, this will create significant locking so you're only going to be
this aggressive on a database that's not in use (Overnight, for most) or
write a cursor that includes some logic in it but that's going to be slow.
DatabaseAdmins.com
Databases Reliable. Available.
"Patrick" wrote:
> I need to do a bulk update of 1 field in a data-table (removing
> zero-padding). The update is expected to update 52.5 million records.
> What techniques could I use so I can ensure
> 1) Consistency in the transactional log backups (every 10 mins)
> 2) Consistency in the full DB backup every day
> 3) Disk drive of Transaction logs do not get filled up completely (I suspe
ct
> even if I set rown count to 10,000 and loop through, disk-space would stil
l
> be used')
> Target environment
> SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
> (Different disk drives for data and log files)|||SQL Server takes care of consistency. To make sure you don't blow up the
transaction log, dividing the update into smaller chunks of updates (e.g.
10,000 rows at a time as you mentioned) is the right approach in general.
I'd like to add that you may have to pace your updates (e.g. sleep for some
time after each iteration) so that your transaction log backups at the
10-minute interval have a chance coming in to clear out the inactive portion
of the log for reuse. Alternative, you can backup the transaction log after
each iteration (or every so many iterations) in the loop. You need to
experiment a bit to find out the best chunk size and whether the loop withou
t
any wait time would be too tight in your particular configuration.
Also, it's certainly possible you can choose to update all the rows in a
single shot, depending on the size of your transaction log.
Linchi
"burt_king" wrote:
[vbcol=seagreen]
> A normal set based operation will do fine. update my table set myfield =
> 'mynewvalue'
> However, this will create significant locking so you're only going to be
> this aggressive on a database that's not in use (Overnight, for most) or
> write a cursor that includes some logic in it but that's going to be slow.
> --
> DatabaseAdmins.com
> Databases Reliable. Available.
>
> "Patrick" wrote:
>|||I have composed the following SQL, could you tell me what is wrong? The
Select getDate() only printed <10 dates! when I am expecting millions of
records to be updated!
DECLARE @.recCount int
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_transactions
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_stocks
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_inventory
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
"Linchi Shea" wrote:
[vbcol=seagreen]
> SQL Server takes care of consistency. To make sure you don't blow up the
> transaction log, dividing the update into smaller chunks of updates (e.g.
> 10,000 rows at a time as you mentioned) is the right approach in general.
> I'd like to add that you may have to pace your updates (e.g. sleep for som
e
> time after each iteration) so that your transaction log backups at the
> 10-minute interval have a chance coming in to clear out the inactive porti
on
> of the log for reuse. Alternative, you can backup the transaction log afte
r
> each iteration (or every so many iterations) in the loop. You need to
> experiment a bit to find out the best chunk size and whether the loop with
out
> any wait time would be too tight in your particular configuration.
> Also, it's certainly possible you can choose to update all the rows in a
> single shot, depending on the size of your transaction log.
> Linchi
> "burt_king" wrote:
>
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Thursday, March 29, 2012
Bulk UPDATE on SQL Server 2000
I need to do a bulk update of 1 field in a data-table (removing
zero-padding). The update is expected to update 52.5 million records.
What techniques could I use so I can ensure
1) Consistency in the transactional log backups (every 10 mins)
2) Consistency in the full DB backup every day
3) Disk drive of Transaction logs do not get filled up completely (I suspect
even if I set rown count to 10,000 and loop through, disk-space would still
be used')
Target environment
SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
(Different disk drives for data and log files)A normal set based operation will do fine. update my table set myfield ='mynewvalue'
However, this will create significant locking so you're only going to be
this aggressive on a database that's not in use (Overnight, for most) or
write a cursor that includes some logic in it but that's going to be slow.
--
DatabaseAdmins.com
Databases Reliable. Available.
"Patrick" wrote:
> I need to do a bulk update of 1 field in a data-table (removing
> zero-padding). The update is expected to update 52.5 million records.
> What techniques could I use so I can ensure
> 1) Consistency in the transactional log backups (every 10 mins)
> 2) Consistency in the full DB backup every day
> 3) Disk drive of Transaction logs do not get filled up completely (I suspect
> even if I set rown count to 10,000 and loop through, disk-space would still
> be used')
> Target environment
> SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
> (Different disk drives for data and log files)|||SQL Server takes care of consistency. To make sure you don't blow up the
transaction log, dividing the update into smaller chunks of updates (e.g.
10,000 rows at a time as you mentioned) is the right approach in general.
I'd like to add that you may have to pace your updates (e.g. sleep for some
time after each iteration) so that your transaction log backups at the
10-minute interval have a chance coming in to clear out the inactive portion
of the log for reuse. Alternative, you can backup the transaction log after
each iteration (or every so many iterations) in the loop. You need to
experiment a bit to find out the best chunk size and whether the loop without
any wait time would be too tight in your particular configuration.
Also, it's certainly possible you can choose to update all the rows in a
single shot, depending on the size of your transaction log.
Linchi
"burt_king" wrote:
> A normal set based operation will do fine. update my table set myfield => 'mynewvalue'
> However, this will create significant locking so you're only going to be
> this aggressive on a database that's not in use (Overnight, for most) or
> write a cursor that includes some logic in it but that's going to be slow.
> --
> DatabaseAdmins.com
> Databases Reliable. Available.
>
> "Patrick" wrote:
> > I need to do a bulk update of 1 field in a data-table (removing
> > zero-padding). The update is expected to update 52.5 million records.
> >
> > What techniques could I use so I can ensure
> > 1) Consistency in the transactional log backups (every 10 mins)
> > 2) Consistency in the full DB backup every day
> > 3) Disk drive of Transaction logs do not get filled up completely (I suspect
> > even if I set rown count to 10,000 and loop through, disk-space would still
> > be used')
> >
> > Target environment
> > SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
> > (Different disk drives for data and log files)|||I have composed the following SQL, could you tell me what is wrong? The
Select getDate() only printed <10 dates! when I am expecting millions of
records to be updated!
DECLARE @.recCount int
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_transactions
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_stocks
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_inventory
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
"Linchi Shea" wrote:
> SQL Server takes care of consistency. To make sure you don't blow up the
> transaction log, dividing the update into smaller chunks of updates (e.g.
> 10,000 rows at a time as you mentioned) is the right approach in general.
> I'd like to add that you may have to pace your updates (e.g. sleep for some
> time after each iteration) so that your transaction log backups at the
> 10-minute interval have a chance coming in to clear out the inactive portion
> of the log for reuse. Alternative, you can backup the transaction log after
> each iteration (or every so many iterations) in the loop. You need to
> experiment a bit to find out the best chunk size and whether the loop without
> any wait time would be too tight in your particular configuration.
> Also, it's certainly possible you can choose to update all the rows in a
> single shot, depending on the size of your transaction log.
> Linchi
> "burt_king" wrote:
> > A normal set based operation will do fine. update my table set myfield => > 'mynewvalue'
> >
> > However, this will create significant locking so you're only going to be
> > this aggressive on a database that's not in use (Overnight, for most) or
> > write a cursor that includes some logic in it but that's going to be slow.
> >
> > --
> > DatabaseAdmins.com
> > Databases Reliable. Available.
> >
> >
> > "Patrick" wrote:
> >
> > > I need to do a bulk update of 1 field in a data-table (removing
> > > zero-padding). The update is expected to update 52.5 million records.
> > >
> > > What techniques could I use so I can ensure
> > > 1) Consistency in the transactional log backups (every 10 mins)
> > > 2) Consistency in the full DB backup every day
> > > 3) Disk drive of Transaction logs do not get filled up completely (I suspect
> > > even if I set rown count to 10,000 and loop through, disk-space would still
> > > be used')
> > >
> > > Target environment
> > > SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
> > > (Different disk drives for data and log files)
zero-padding). The update is expected to update 52.5 million records.
What techniques could I use so I can ensure
1) Consistency in the transactional log backups (every 10 mins)
2) Consistency in the full DB backup every day
3) Disk drive of Transaction logs do not get filled up completely (I suspect
even if I set rown count to 10,000 and loop through, disk-space would still
be used')
Target environment
SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
(Different disk drives for data and log files)A normal set based operation will do fine. update my table set myfield ='mynewvalue'
However, this will create significant locking so you're only going to be
this aggressive on a database that's not in use (Overnight, for most) or
write a cursor that includes some logic in it but that's going to be slow.
--
DatabaseAdmins.com
Databases Reliable. Available.
"Patrick" wrote:
> I need to do a bulk update of 1 field in a data-table (removing
> zero-padding). The update is expected to update 52.5 million records.
> What techniques could I use so I can ensure
> 1) Consistency in the transactional log backups (every 10 mins)
> 2) Consistency in the full DB backup every day
> 3) Disk drive of Transaction logs do not get filled up completely (I suspect
> even if I set rown count to 10,000 and loop through, disk-space would still
> be used')
> Target environment
> SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
> (Different disk drives for data and log files)|||SQL Server takes care of consistency. To make sure you don't blow up the
transaction log, dividing the update into smaller chunks of updates (e.g.
10,000 rows at a time as you mentioned) is the right approach in general.
I'd like to add that you may have to pace your updates (e.g. sleep for some
time after each iteration) so that your transaction log backups at the
10-minute interval have a chance coming in to clear out the inactive portion
of the log for reuse. Alternative, you can backup the transaction log after
each iteration (or every so many iterations) in the loop. You need to
experiment a bit to find out the best chunk size and whether the loop without
any wait time would be too tight in your particular configuration.
Also, it's certainly possible you can choose to update all the rows in a
single shot, depending on the size of your transaction log.
Linchi
"burt_king" wrote:
> A normal set based operation will do fine. update my table set myfield => 'mynewvalue'
> However, this will create significant locking so you're only going to be
> this aggressive on a database that's not in use (Overnight, for most) or
> write a cursor that includes some logic in it but that's going to be slow.
> --
> DatabaseAdmins.com
> Databases Reliable. Available.
>
> "Patrick" wrote:
> > I need to do a bulk update of 1 field in a data-table (removing
> > zero-padding). The update is expected to update 52.5 million records.
> >
> > What techniques could I use so I can ensure
> > 1) Consistency in the transactional log backups (every 10 mins)
> > 2) Consistency in the full DB backup every day
> > 3) Disk drive of Transaction logs do not get filled up completely (I suspect
> > even if I set rown count to 10,000 and loop through, disk-space would still
> > be used')
> >
> > Target environment
> > SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
> > (Different disk drives for data and log files)|||I have composed the following SQL, could you tell me what is wrong? The
Select getDate() only printed <10 dates! when I am expecting millions of
records to be updated!
DECLARE @.recCount int
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_transactions
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_stocks
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_inventory
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
"Linchi Shea" wrote:
> SQL Server takes care of consistency. To make sure you don't blow up the
> transaction log, dividing the update into smaller chunks of updates (e.g.
> 10,000 rows at a time as you mentioned) is the right approach in general.
> I'd like to add that you may have to pace your updates (e.g. sleep for some
> time after each iteration) so that your transaction log backups at the
> 10-minute interval have a chance coming in to clear out the inactive portion
> of the log for reuse. Alternative, you can backup the transaction log after
> each iteration (or every so many iterations) in the loop. You need to
> experiment a bit to find out the best chunk size and whether the loop without
> any wait time would be too tight in your particular configuration.
> Also, it's certainly possible you can choose to update all the rows in a
> single shot, depending on the size of your transaction log.
> Linchi
> "burt_king" wrote:
> > A normal set based operation will do fine. update my table set myfield => > 'mynewvalue'
> >
> > However, this will create significant locking so you're only going to be
> > this aggressive on a database that's not in use (Overnight, for most) or
> > write a cursor that includes some logic in it but that's going to be slow.
> >
> > --
> > DatabaseAdmins.com
> > Databases Reliable. Available.
> >
> >
> > "Patrick" wrote:
> >
> > > I need to do a bulk update of 1 field in a data-table (removing
> > > zero-padding). The update is expected to update 52.5 million records.
> > >
> > > What techniques could I use so I can ensure
> > > 1) Consistency in the transactional log backups (every 10 mins)
> > > 2) Consistency in the full DB backup every day
> > > 3) Disk drive of Transaction logs do not get filled up completely (I suspect
> > > even if I set rown count to 10,000 and loop through, disk-space would still
> > > be used')
> > >
> > > Target environment
> > > SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
> > > (Different disk drives for data and log files)
Tuesday, March 27, 2012
Bulk Load and SQL function defaults
Is it possible to include a field default in the schema file that represent
a database function?
For example, all of our SQL tables includes the following fields...
CreateDate datetime NOT NULL DEFAULT (getdate())
CreateUser char (255) NOT NULL DEFAULT (suser_sname())
When creating our schema we tried the following:
<xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
/>
<xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
/>
The "CreateDate" element fails with an "Invalid character value for cast
specification." error, while the second element will insert the string value
'suser_sname()' into the "CreateUser" field.
Thanks in advance
No, this is not possible. The default is an XML schema default clause and
cannot contain an T-SQL expression.
Instead, define a default on the relational table column to which you map
the element and make sure that there is no value added.
Best regards
Michael
"Cipher" <c@.c.com> wrote in message
news:OU8QUY0TEHA.3988@.tk2msftngp13.phx.gbl...
> Is it possible to include a field default in the schema file that
> represent
> a database function?
> For example, all of our SQL tables includes the following fields...
> CreateDate datetime NOT NULL DEFAULT (getdate())
> CreateUser char (255) NOT NULL DEFAULT (suser_sname())
> When creating our schema we tried the following:
> <xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
> />
> <xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
> />
> The "CreateDate" element fails with an "Invalid character value for cast
> specification." error, while the second element will insert the string
> value
> 'suser_sname()' into the "CreateUser" field.
>
> Thanks in advance
>
sql
a database function?
For example, all of our SQL tables includes the following fields...
CreateDate datetime NOT NULL DEFAULT (getdate())
CreateUser char (255) NOT NULL DEFAULT (suser_sname())
When creating our schema we tried the following:
<xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
/>
<xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
/>
The "CreateDate" element fails with an "Invalid character value for cast
specification." error, while the second element will insert the string value
'suser_sname()' into the "CreateUser" field.
Thanks in advance
No, this is not possible. The default is an XML schema default clause and
cannot contain an T-SQL expression.
Instead, define a default on the relational table column to which you map
the element and make sure that there is no value added.
Best regards
Michael
"Cipher" <c@.c.com> wrote in message
news:OU8QUY0TEHA.3988@.tk2msftngp13.phx.gbl...
> Is it possible to include a field default in the schema file that
> represent
> a database function?
> For example, all of our SQL tables includes the following fields...
> CreateDate datetime NOT NULL DEFAULT (getdate())
> CreateUser char (255) NOT NULL DEFAULT (suser_sname())
> When creating our schema we tried the following:
> <xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
> />
> <xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
> />
> The "CreateDate" element fails with an "Invalid character value for cast
> specification." error, while the second element will insert the string
> value
> 'suser_sname()' into the "CreateUser" field.
>
> Thanks in advance
>
sql
Sunday, March 25, 2012
Bulk Insert With Identity Field
Hi All,
Can I Bulk Insert to to SQL Table with a Identity Column in it?
My Source is a text file with 17 Columns and My Target is a SQL Server table
with 18 Columns (all 17 column of the source + 1 Identity Column as Primary
KEY).
So In this Situation How can i Bulk Insert to the SQL Table from the text
file. Please give small example if possible.
Also If my SQL table is have 2 More Extra Column Can I Boul Insert from the
above source?
Ex: Total 19 Columns ( all 17 columns of the source text file + 1 Identity
Column + 1 Extra column). If I want to Insert into the 17 columns and I want
the Indetity column to generate auto numbers and the Last Extra Column to be
Filled with Some "Char (1)" Value. Is that Possible?
Thanks for any Help or suggestions
Prabhat
using bulk insert there is a keepidentity parameter, using bcp it is -E...
Both are documented in books on line
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:OKa8j8unEHA.3868@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Can I Bulk Insert to to SQL Table with a Identity Column in it?
> My Source is a text file with 17 Columns and My Target is a SQL Server
table
> with 18 Columns (all 17 column of the source + 1 Identity Column as
Primary
> KEY).
> So In this Situation How can i Bulk Insert to the SQL Table from the text
> file. Please give small example if possible.
> Also If my SQL table is have 2 More Extra Column Can I Boul Insert from
the
> above source?
> Ex: Total 19 Columns ( all 17 columns of the source text file + 1 Identity
> Column + 1 Extra column). If I want to Insert into the 17 columns and I
want
> the Indetity column to generate auto numbers and the Last Extra Column to
be
> Filled with Some "Char (1)" Value. Is that Possible?
> Thanks for any Help or suggestions
> Prabhat
>
|||Thanks for the Hint. I have seen that in BOL but did not get any Example.
Can you suggest any site or give me a small Example where the Target table
has a Identity Field but the Source does not have the value for Identity
Column.
Thanks
Prabhat
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:#m#6mmwnEHA.3900@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> using bulk insert there is a keepidentity parameter, using bcp it is -E...
> Both are documented in books on line
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:OKa8j8unEHA.3868@.TK2MSFTNGP11.phx.gbl...
> table
> Primary
text[vbcol=seagreen]
> the
Identity[vbcol=seagreen]
> want
to
> be
>
Can I Bulk Insert to to SQL Table with a Identity Column in it?
My Source is a text file with 17 Columns and My Target is a SQL Server table
with 18 Columns (all 17 column of the source + 1 Identity Column as Primary
KEY).
So In this Situation How can i Bulk Insert to the SQL Table from the text
file. Please give small example if possible.
Also If my SQL table is have 2 More Extra Column Can I Boul Insert from the
above source?
Ex: Total 19 Columns ( all 17 columns of the source text file + 1 Identity
Column + 1 Extra column). If I want to Insert into the 17 columns and I want
the Indetity column to generate auto numbers and the Last Extra Column to be
Filled with Some "Char (1)" Value. Is that Possible?
Thanks for any Help or suggestions
Prabhat
using bulk insert there is a keepidentity parameter, using bcp it is -E...
Both are documented in books on line
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:OKa8j8unEHA.3868@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Can I Bulk Insert to to SQL Table with a Identity Column in it?
> My Source is a text file with 17 Columns and My Target is a SQL Server
table
> with 18 Columns (all 17 column of the source + 1 Identity Column as
Primary
> KEY).
> So In this Situation How can i Bulk Insert to the SQL Table from the text
> file. Please give small example if possible.
> Also If my SQL table is have 2 More Extra Column Can I Boul Insert from
the
> above source?
> Ex: Total 19 Columns ( all 17 columns of the source text file + 1 Identity
> Column + 1 Extra column). If I want to Insert into the 17 columns and I
want
> the Indetity column to generate auto numbers and the Last Extra Column to
be
> Filled with Some "Char (1)" Value. Is that Possible?
> Thanks for any Help or suggestions
> Prabhat
>
|||Thanks for the Hint. I have seen that in BOL but did not get any Example.
Can you suggest any site or give me a small Example where the Target table
has a Identity Field but the Source does not have the value for Identity
Column.
Thanks
Prabhat
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:#m#6mmwnEHA.3900@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> using bulk insert there is a keepidentity parameter, using bcp it is -E...
> Both are documented in books on line
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:OKa8j8unEHA.3868@.TK2MSFTNGP11.phx.gbl...
> table
> Primary
text[vbcol=seagreen]
> the
Identity[vbcol=seagreen]
> want
to
> be
>
Bulk Insert With Identity Field
Hi All,
Can I Bulk Insert to to SQL Table with a Identity Column in it?
My Source is a text file with 17 Columns and My Target is a SQL Server table
with 18 Columns (all 17 column of the source + 1 Identity Column as Primary
KEY).
So In this Situation How can i Bulk Insert to the SQL Table from the text
file. Please give small example if possible.
Also If my SQL table is have 2 More Extra Column Can I Boul Insert from the
above source?
Ex: Total 19 Columns ( all 17 columns of the source text file + 1 Identity
Column + 1 Extra column). If I want to Insert into the 17 columns and I want
the Indetity column to generate auto numbers and the Last Extra Column to be
Filled with Some "Char (1)" Value. Is that Possible?
Thanks for any Help or suggestions
Prabhatusing bulk insert there is a keepidentity parameter, using bcp it is -E...
Both are documented in books on line
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:OKa8j8unEHA.3868@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Can I Bulk Insert to to SQL Table with a Identity Column in it?
> My Source is a text file with 17 Columns and My Target is a SQL Server
table
> with 18 Columns (all 17 column of the source + 1 Identity Column as
Primary
> KEY).
> So In this Situation How can i Bulk Insert to the SQL Table from the text
> file. Please give small example if possible.
> Also If my SQL table is have 2 More Extra Column Can I Boul Insert from
the
> above source?
> Ex: Total 19 Columns ( all 17 columns of the source text file + 1 Identity
> Column + 1 Extra column). If I want to Insert into the 17 columns and I
want
> the Indetity column to generate auto numbers and the Last Extra Column to
be
> Filled with Some "Char (1)" Value. Is that Possible?
> Thanks for any Help or suggestions
> Prabhat
>|||Thanks for the Hint. I have seen that in BOL but did not get any Example.
Can you suggest any site or give me a small Example where the Target table
has a Identity Field but the Source does not have the value for Identity
Column.
Thanks
Prabhat
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:#m#6mmwnEHA.3900@.TK2MSFTNGP10.phx.gbl...
> using bulk insert there is a keepidentity parameter, using bcp it is -E...
> Both are documented in books on line
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:OKa8j8unEHA.3868@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > Can I Bulk Insert to to SQL Table with a Identity Column in it?
> >
> > My Source is a text file with 17 Columns and My Target is a SQL Server
> table
> > with 18 Columns (all 17 column of the source + 1 Identity Column as
> Primary
> > KEY).
> >
> > So In this Situation How can i Bulk Insert to the SQL Table from the
text
> > file. Please give small example if possible.
> >
> > Also If my SQL table is have 2 More Extra Column Can I Boul Insert from
> the
> > above source?
> > Ex: Total 19 Columns ( all 17 columns of the source text file + 1
Identity
> > Column + 1 Extra column). If I want to Insert into the 17 columns and I
> want
> > the Indetity column to generate auto numbers and the Last Extra Column
to
> be
> > Filled with Some "Char (1)" Value. Is that Possible?
> >
> > Thanks for any Help or suggestions
> > Prabhat
> >
> >
>sql
Can I Bulk Insert to to SQL Table with a Identity Column in it?
My Source is a text file with 17 Columns and My Target is a SQL Server table
with 18 Columns (all 17 column of the source + 1 Identity Column as Primary
KEY).
So In this Situation How can i Bulk Insert to the SQL Table from the text
file. Please give small example if possible.
Also If my SQL table is have 2 More Extra Column Can I Boul Insert from the
above source?
Ex: Total 19 Columns ( all 17 columns of the source text file + 1 Identity
Column + 1 Extra column). If I want to Insert into the 17 columns and I want
the Indetity column to generate auto numbers and the Last Extra Column to be
Filled with Some "Char (1)" Value. Is that Possible?
Thanks for any Help or suggestions
Prabhatusing bulk insert there is a keepidentity parameter, using bcp it is -E...
Both are documented in books on line
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:OKa8j8unEHA.3868@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Can I Bulk Insert to to SQL Table with a Identity Column in it?
> My Source is a text file with 17 Columns and My Target is a SQL Server
table
> with 18 Columns (all 17 column of the source + 1 Identity Column as
Primary
> KEY).
> So In this Situation How can i Bulk Insert to the SQL Table from the text
> file. Please give small example if possible.
> Also If my SQL table is have 2 More Extra Column Can I Boul Insert from
the
> above source?
> Ex: Total 19 Columns ( all 17 columns of the source text file + 1 Identity
> Column + 1 Extra column). If I want to Insert into the 17 columns and I
want
> the Indetity column to generate auto numbers and the Last Extra Column to
be
> Filled with Some "Char (1)" Value. Is that Possible?
> Thanks for any Help or suggestions
> Prabhat
>|||Thanks for the Hint. I have seen that in BOL but did not get any Example.
Can you suggest any site or give me a small Example where the Target table
has a Identity Field but the Source does not have the value for Identity
Column.
Thanks
Prabhat
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:#m#6mmwnEHA.3900@.TK2MSFTNGP10.phx.gbl...
> using bulk insert there is a keepidentity parameter, using bcp it is -E...
> Both are documented in books on line
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:OKa8j8unEHA.3868@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > Can I Bulk Insert to to SQL Table with a Identity Column in it?
> >
> > My Source is a text file with 17 Columns and My Target is a SQL Server
> table
> > with 18 Columns (all 17 column of the source + 1 Identity Column as
> Primary
> > KEY).
> >
> > So In this Situation How can i Bulk Insert to the SQL Table from the
text
> > file. Please give small example if possible.
> >
> > Also If my SQL table is have 2 More Extra Column Can I Boul Insert from
> the
> > above source?
> > Ex: Total 19 Columns ( all 17 columns of the source text file + 1
Identity
> > Column + 1 Extra column). If I want to Insert into the 17 columns and I
> want
> > the Indetity column to generate auto numbers and the Last Extra Column
to
> be
> > Filled with Some "Char (1)" Value. Is that Possible?
> >
> > Thanks for any Help or suggestions
> > Prabhat
> >
> >
>sql
bulk insert with a primary key?
i'm using sql2k.
can i do a bulk isnert operation to a table with a primary key
(identity field) on it? i suspect the dts pacakge didn't utilize the
bulk insert because pk is automatically a non-clustering index, and
bulk insert can only work on table w/o any index. in this case, what
should i do to make sure the fastest load possible?
thank you.> bulk insert can only work on table w/o any index.
Since when? I have several applications where BULK INSERT affects a table
with a clustered index on a datetime column and a non-clustered index on a
foreign key column. The only way it differs from your scenario is that all
the data is in the file (there is no surrogate column generated by the
system).
> in this case, what
> should i do to make sure the fastest load possible?
As long as the generation of the IDENTITY values does not need to correspond
directly 1:1 with the physical order of the file, you may wish to bulk
insert into a heap, and then insert real_table(column_list) select * from
heap.
A|||It is not true that bulk insert will only work with non-indexed tables.
In fact, you can achieve better throughput, if you have a clustered index on
the table, and input file is also sorted in the same order as the clustered
index.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1123518093.288607.254270@.g14g2000cwa.googlegroups.com...
> i'm using sql2k.
> can i do a bulk isnert operation to a table with a primary key
> (identity field) on it? i suspect the dts pacakge didn't utilize the
> bulk insert because pk is automatically a non-clustering index, and
> bulk insert can only work on table w/o any index. in this case, what
> should i do to make sure the fastest load possible?
> thank you.
>
can i do a bulk isnert operation to a table with a primary key
(identity field) on it? i suspect the dts pacakge didn't utilize the
bulk insert because pk is automatically a non-clustering index, and
bulk insert can only work on table w/o any index. in this case, what
should i do to make sure the fastest load possible?
thank you.> bulk insert can only work on table w/o any index.
Since when? I have several applications where BULK INSERT affects a table
with a clustered index on a datetime column and a non-clustered index on a
foreign key column. The only way it differs from your scenario is that all
the data is in the file (there is no surrogate column generated by the
system).
> in this case, what
> should i do to make sure the fastest load possible?
As long as the generation of the IDENTITY values does not need to correspond
directly 1:1 with the physical order of the file, you may wish to bulk
insert into a heap, and then insert real_table(column_list) select * from
heap.
A|||It is not true that bulk insert will only work with non-indexed tables.
In fact, you can achieve better throughput, if you have a clustered index on
the table, and input file is also sorted in the same order as the clustered
index.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1123518093.288607.254270@.g14g2000cwa.googlegroups.com...
> i'm using sql2k.
> can i do a bulk isnert operation to a table with a primary key
> (identity field) on it? i suspect the dts pacakge didn't utilize the
> bulk insert because pk is automatically a non-clustering index, and
> bulk insert can only work on table w/o any index. in this case, what
> should i do to make sure the fastest load possible?
> thank you.
>
Thursday, March 22, 2012
Bulk Insert Unicode
Good day,
We are using bulk insert with a formatfile to load a text file into sqlexpress. One field in the text file contains non-ascii (unicode) charaters and the corresponding database field is nvarchar.
When the record and row are specified in the format file as:
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400" COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>
the value "Boca Curá" gets imported as "Boca Cur??". This is true even with datafiletype set to widenative or widechar, and/or codepage set to raw or acp.
When the field is specfied in the record section of the format file as NCharTerm, the bulk insert terminates immediately with:
Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23 (FullName).
This is true regardless of which bulk insert options specified.
What could be going wrong and how can we address it?
Thanks in advance...
Hi,
I see it's been a while since you posted this, but I have just run into this same issue today and I was wondering if you were able to resolve it gracefully?
regards
|||Hi knightEknight!
We solved this with MS using a developer support incident. I should have updated this post when the information was fresh! Just went back through my notes and the readme for the project, and the solution was to convert the input file from UTF8 text encoding to ANSI text encoding. This is a Save As option in Notepad and most text editors. If you have a large file and need some C# code to do this, let me know....
|||Thanks! My input comes from various sources and I've pretty much concluded the same thing. I'll just have to find a way to convert all the input to ANSI. - Regards|||Here is a C# example:
// utf8 in, ansi out
StreamReader inStream = File.OpenText(@."E:\Data\DEM\Panama\panama_canal_final.txt");
FileStream outStream = File.Create(@."E:\Data\DEM\Panama\panama_canal_fsubset.txt");
Encoding ansi = Encoding.Default;
string inLine;
byte[] outBytes;
while (true)
{
inLine = inStream.ReadLine();
if (inLine == null)
break;
outBytes = ansi.GetBytes(inLine + "\r\n");
outStream.Write(outBytes, 0, outBytes.Length);
}
inStream.Close();
outStream.Close();
|||Instead of converting your files to ANSI as suggested you could also fix your delimiter problem for Unicode files. The TERMINATOR should be '\t\0' instead of just '\t' for tabs and '\r\0\n\0' for the row delimiter if you are using standard CR+LF.
Regards,
Lars
sqlBulk Insert Unicode
Good day,
We are using bulk insert with a formatfile to load a text file into sqlexpress. One field in the text file contains non-ascii (unicode) charaters and the corresponding database field is nvarchar.
When the record and row are specified in the format file as:
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400" COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>
the value "Boca Curá" gets imported as "Boca Cur??". This is true even with datafiletype set to widenative or widechar, and/or codepage set to raw or acp.
When the field is specfied in the record section of the format file as NCharTerm, the bulk insert terminates immediately with:
Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23 (FullName).
This is true regardless of which bulk insert options specified.
What could be going wrong and how can we address it?
Thanks in advance...
Hi,
I see it's been a while since you posted this, but I have just run into this same issue today and I was wondering if you were able to resolve it gracefully?
regards
|||Hi knightEknight!
We solved this with MS using a developer support incident. I should have updated this post when the information was fresh! Just went back through my notes and the readme for the project, and the solution was to convert the input file from UTF8 text encoding to ANSI text encoding. This is a Save As option in Notepad and most text editors. If you have a large file and need some C# code to do this, let me know....
|||Thanks! My input comes from various sources and I've pretty much concluded the same thing. I'll just have to find a way to convert all the input to ANSI. - Regards|||Here is a C# example:
// utf8 in, ansi out
StreamReader inStream = File.OpenText(@."E:\Data\DEM\Panama\panama_canal_final.txt");
FileStream outStream = File.Create(@."E:\Data\DEM\Panama\panama_canal_fsubset.txt");
Encoding ansi = Encoding.Default;
string inLine;
byte[] outBytes;
while (true)
{
inLine = inStream.ReadLine();
if (inLine == null)
break;
outBytes = ansi.GetBytes(inLine + "\r\n");
outStream.Write(outBytes, 0, outBytes.Length);
}
inStream.Close();
outStream.Close();
|||Instead of converting your files to ANSI as suggested you could also fix your delimiter problem for Unicode files. The TERMINATOR should be '\t\0' instead of just '\t' for tabs and '\r\0\n\0' for the row delimiter if you are using standard CR+LF.
Regards,
Lars
Bulk Insert Unicode
Good day,
We are using bulk insert with a formatfile to load a text file into sqlexpress. One field in the text file contains non-ascii (unicode) charaters and the corresponding database field is nvarchar.
When the record and row are specified in the format file as:
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400" COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>
the value "Boca Curá" gets imported as "Boca Cur??". This is true even with datafiletype set to widenative or widechar, and/or codepage set to raw or acp.
When the field is specfied in the record section of the format file as NCharTerm, the bulk insert terminates immediately with:
Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23 (FullName).
This is true regardless of which bulk insert options specified.
What could be going wrong and how can we address it?
Thanks in advance...
Hi,
I see it's been a while since you posted this, but I have just run into this same issue today and I was wondering if you were able to resolve it gracefully?
regards
|||Hi knightEknight!
We solved this with MS using a developer support incident. I should have updated this post when the information was fresh! Just went back through my notes and the readme for the project, and the solution was to convert the input file from UTF8 text encoding to ANSI text encoding. This is a Save As option in Notepad and most text editors. If you have a large file and need some C# code to do this, let me know....
|||Thanks! My input comes from various sources and I've pretty much concluded the same thing. I'll just have to find a way to convert all the input to ANSI. - Regards|||Here is a C# example:
// utf8 in, ansi out
StreamReader inStream = File.OpenText(@."E:\Data\DEM\Panama\panama_canal_final.txt");
FileStream outStream = File.Create(@."E:\Data\DEM\Panama\panama_canal_fsubset.txt");
Encoding ansi = Encoding.Default;
string inLine;
byte[] outBytes;
while (true)
{
inLine = inStream.ReadLine();
if (inLine == null)
break;
outBytes = ansi.GetBytes(inLine + "\r\n");
outStream.Write(outBytes, 0, outBytes.Length);
}
inStream.Close();
outStream.Close();
Bulk Insert Unicode
Good day,
We are using bulk insert with a formatfile to load a text file into sqlexpress. One field in the text file contains non-ascii (unicode) charaters and the corresponding database field is nvarchar.
When the record and row are specified in the format file as:
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400" COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>
the value "Boca Curá" gets imported as "Boca Cur??". This is true even with datafiletype set to widenative or widechar, and/or codepage set to raw or acp.
When the field is specfied in the record section of the format file as NCharTerm, the bulk insert terminates immediately with:
Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23 (FullName).
This is true regardless of which bulk insert options specified.
What could be going wrong and how can we address it?
Thanks in advance...
Hi,
I see it's been a while since you posted this, but I have just run into this same issue today and I was wondering if you were able to resolve it gracefully?
regards
|||Hi knightEknight!
We solved this with MS using a developer support incident. I should have updated this post when the information was fresh! Just went back through my notes and the readme for the project, and the solution was to convert the input file from UTF8 text encoding to ANSI text encoding. This is a Save As option in Notepad and most text editors. If you have a large file and need some C# code to do this, let me know....
|||Thanks! My input comes from various sources and I've pretty much concluded the same thing. I'll just have to find a way to convert all the input to ANSI. - Regards|||Here is a C# example:
// utf8 in, ansi out
StreamReader inStream = File.OpenText(@."E:\Data\DEM\Panama\panama_canal_final.txt");
FileStream outStream = File.Create(@."E:\Data\DEM\Panama\panama_canal_fsubset.txt");
Encoding ansi = Encoding.Default;
string inLine;
byte[] outBytes;
while (true)
{
inLine = inStream.ReadLine();
if (inLine == null)
break;
outBytes = ansi.GetBytes(inLine + "\r\n");
outStream.Write(outBytes, 0, outBytes.Length);
}
inStream.Close();
outStream.Close();
Bulk Insert Unicode
Good day,
We are using bulk insert with a formatfile to load a text file into
sqlexpress. One field in the text file contains non-ascii (unicode)
charaters and the corresponding database field is nvarchar.
When the record and row are specified in the format file as:
<FIELD ID=3D"23" xsi:type=3D"CharTerm" TERMINATOR=3D"\t" MAX_LENGTH=3D"400"
COLLATION=3D"Latin1_General_CI_AS"/>
<COLUMN SOURCE=3D"23" NAME=3D"FullName" xsi:type=3D"SQLNVARCHAR"/>
the value "Boca Cur=E1" gets imported as "Boca Cur=C3=A1". This is true
even with datafiletype set to widenative or widechar, and/or codepage
set to raw or acp.
When the field is specfied in the record section of the format file as
NCharTerm, the bulk insert terminates immediately with:
Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23
(FullName).
This is true regardless bulk insert options specified.
What could be going wrong and how can we address it?
Thanks in advance...Hi
Can you specify SQLNVARCHAR(50) for example? If I remember well it truncates
the string if you do not specify a length.As it is NVARCHAR , so it is like
NVARCHAR(2)
<rgreene@.icanmarine.com> wrote in message
news:1143039002.249599.122720@.g10g2000cwb.googlegroups.com...
Good day,
We are using bulk insert with a formatfile to load a text file into
sqlexpress. One field in the text file contains non-ascii (unicode)
charaters and the corresponding database field is nvarchar.
When the record and row are specified in the format file as:
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>
the value "Boca Cur" gets imported as "Boca Curá". This is true
even with datafiletype set to widenative or widechar, and/or codepage
set to raw or acp.
When the field is specfied in the record section of the format file as
NCharTerm, the bulk insert terminates immediately with:
Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23
(FullName).
This is true regardless bulk insert options specified.
What could be going wrong and how can we address it?
Thanks in advance...|||Hi Uri. Thanks for the quick reply.
When I use NCharTerm in the record section of the formatfile and
SQLNVARCHAR(200) in the row section I get:
Msg 4858, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Line 49 in format file "C:\Documents and Settings\rgreene\My
Documents\PlaceName
s\format.xml": bad value SQLNVARCHAR(200) for attribute "xsi:type".
Any other ideas?|||Well, can you show us a format file? Is it a XML file?
<rgreene@.icanmarine.com> wrote in message
news:1143039873.112229.184880@.g10g2000cwb.googlegroups.com...
> Hi Uri. Thanks for the quick reply.
> When I use NCharTerm in the record section of the formatfile and
> SQLNVARCHAR(200) in the row section I get:
> Msg 4858, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
> Line 49 in format file "C:\Documents and Settings\rgreene\My
> Documents\PlaceName
> s\format.xml": bad value SQLNVARCHAR(200) for attribute "xsi:type".
> Any other ideas?
>|||Here is the original XML format file (the one that imports, but
changes, the unicode text):
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="5"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="7"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="8"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="4"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="7"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="2"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="10"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="5"/>
<FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="4"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="14" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="4"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="15" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="16" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="12"/>
<FIELD ID="17" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="4"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="18" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="2"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="19" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="6"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="20" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="128"/>
<FIELD ID="21" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="128"/>
<FIELD ID="22" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="24" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="25" xsi:type="CharTerm" TERMINATOR="\r\n"
MAX_LENGTH="24"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="RegionCode" xsi:type="SQLTINYINT"/>
<COLUMN SOURCE="2" NAME="UniqueFeatureIdentifier"
xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="UniqueNameIdentifier" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Lat" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="5" NAME="Lon" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="10" NAME="FeatureClassificationCode"
xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="11" NAME="FeatureDesignationCode"
xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="12" NAME="PopulatedPlaceClassification"
xsi:type="SQLTINYINT"/>
<COLUMN SOURCE="13" NAME="PrimaryCountryCode"
xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="14" NAME="ADM1Code" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="15" NAME="ADM2" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="16" NAME="Dimension" xsi:type="SQLINT"/>
<COLUMN SOURCE="17" NAME="SecondaryCountryCode"
xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="18" NAME="NameType" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="19" NAME="LanguageCode" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="22" NAME="SortName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR()"/>
<COLUMN SOURCE="24" NAME="FullNameND" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="25" NAME="ModificationDate"
xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>
We are using bulk insert with a formatfile to load a text file into
sqlexpress. One field in the text file contains non-ascii (unicode)
charaters and the corresponding database field is nvarchar.
When the record and row are specified in the format file as:
<FIELD ID=3D"23" xsi:type=3D"CharTerm" TERMINATOR=3D"\t" MAX_LENGTH=3D"400"
COLLATION=3D"Latin1_General_CI_AS"/>
<COLUMN SOURCE=3D"23" NAME=3D"FullName" xsi:type=3D"SQLNVARCHAR"/>
the value "Boca Cur=E1" gets imported as "Boca Cur=C3=A1". This is true
even with datafiletype set to widenative or widechar, and/or codepage
set to raw or acp.
When the field is specfied in the record section of the format file as
NCharTerm, the bulk insert terminates immediately with:
Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23
(FullName).
This is true regardless bulk insert options specified.
What could be going wrong and how can we address it?
Thanks in advance...Hi
Can you specify SQLNVARCHAR(50) for example? If I remember well it truncates
the string if you do not specify a length.As it is NVARCHAR , so it is like
NVARCHAR(2)
<rgreene@.icanmarine.com> wrote in message
news:1143039002.249599.122720@.g10g2000cwb.googlegroups.com...
Good day,
We are using bulk insert with a formatfile to load a text file into
sqlexpress. One field in the text file contains non-ascii (unicode)
charaters and the corresponding database field is nvarchar.
When the record and row are specified in the format file as:
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR"/>
the value "Boca Cur" gets imported as "Boca Curá". This is true
even with datafiletype set to widenative or widechar, and/or codepage
set to raw or acp.
When the field is specfied in the record section of the format file as
NCharTerm, the bulk insert terminates immediately with:
Msg 4863, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Bulk load data conversion error (truncation) for row 2, column 23
(FullName).
This is true regardless bulk insert options specified.
What could be going wrong and how can we address it?
Thanks in advance...|||Hi Uri. Thanks for the quick reply.
When I use NCharTerm in the record section of the formatfile and
SQLNVARCHAR(200) in the row section I get:
Msg 4858, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
Line 49 in format file "C:\Documents and Settings\rgreene\My
Documents\PlaceName
s\format.xml": bad value SQLNVARCHAR(200) for attribute "xsi:type".
Any other ideas?|||Well, can you show us a format file? Is it a XML file?
<rgreene@.icanmarine.com> wrote in message
news:1143039873.112229.184880@.g10g2000cwb.googlegroups.com...
> Hi Uri. Thanks for the quick reply.
> When I use NCharTerm in the record section of the formatfile and
> SQLNVARCHAR(200) in the row section I get:
> Msg 4858, Level 16, State 1, Server MINT\SQLEXPRESS, Line 1
> Line 49 in format file "C:\Documents and Settings\rgreene\My
> Documents\PlaceName
> s\format.xml": bad value SQLNVARCHAR(200) for attribute "xsi:type".
> Any other ideas?
>|||Here is the original XML format file (the one that imports, but
changes, the unicode text):
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="5"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="7"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="8"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="4"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="7"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="2"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="10"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="5"/>
<FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="4"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="14" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="4"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="15" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="16" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="12"/>
<FIELD ID="17" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="4"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="18" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="2"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="19" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="6"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="20" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="128"/>
<FIELD ID="21" xsi:type="CharTerm" TERMINATOR="\t"
MAX_LENGTH="128"/>
<FIELD ID="22" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="24" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="400"
COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="25" xsi:type="CharTerm" TERMINATOR="\r\n"
MAX_LENGTH="24"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="RegionCode" xsi:type="SQLTINYINT"/>
<COLUMN SOURCE="2" NAME="UniqueFeatureIdentifier"
xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="UniqueNameIdentifier" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Lat" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="5" NAME="Lon" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="10" NAME="FeatureClassificationCode"
xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="11" NAME="FeatureDesignationCode"
xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="12" NAME="PopulatedPlaceClassification"
xsi:type="SQLTINYINT"/>
<COLUMN SOURCE="13" NAME="PrimaryCountryCode"
xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="14" NAME="ADM1Code" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="15" NAME="ADM2" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="16" NAME="Dimension" xsi:type="SQLINT"/>
<COLUMN SOURCE="17" NAME="SecondaryCountryCode"
xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="18" NAME="NameType" xsi:type="SQLNCHAR"/>
<COLUMN SOURCE="19" NAME="LanguageCode" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="22" NAME="SortName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="23" NAME="FullName" xsi:type="SQLNVARCHAR()"/>
<COLUMN SOURCE="24" NAME="FullNameND" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="25" NAME="ModificationDate"
xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>
Bulk Insert Questions
I'm using Bulk Insert for the first time and have a question.
I was able to successfully insert the sample rows you posted.
I'm getting an error message about a field being truncated:
Bulk insert data conversion error (truncation) for row 2, column 12
(Depleted)
The data type for the "Depleted" column is Char(1). Looking at the
file, there is indeed only one character in the column. I'm not sure
how to fix this, or what I can do about it. Any suggestions would be
appreciated.
Thanks!
Jennifer
The table:
CREATE TABLE [dbo].[parSalesDetailTemp] (
[parSalesHdrID] [int] NOT NULL ,
[parSalesDetailID] [int] NOT NULL ,
[Before] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[After] [int] NOT NULL ,
[Promo] [money] NOT NULL ,
[PromoBefore] [money] NOT NULL ,
[ItemPrice] [money] NOT NULL ,
[PromoAfter] [money] NOT NULL ,
[POSItem] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitNumber] [int] NOT NULL ,
[Depleted] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON
[PRIMARY]
GO
The SQL:
BULK INSERT parSalesDetailTemp
FROM '\\wbhq.com\dfsdv\iDataInt\TLDFiles\Extract\SalesD tl.csv'
WITH (FIELDTERMINATOR =',')
The file contents (1st few rows):
8032753,37312006,0,1,0,0,0,4.39,0,"WB-ML",2,N
8032753,37312007,0,1,0,0,0,4.39,0,"WB-ML",2,N
8032753,37312008,0,2,0,0,0,.00,0,"ML-M-COK",2,NJennifer (J.Evans.1970@.gmail.com) writes:
Quote:
Originally Posted by
I'm using Bulk Insert for the first time and have a question.
>
I'm getting an error message about a field being truncated:
Bulk insert data conversion error (truncation) for row 2, column 12
(Depleted)
>
The data type for the "Depleted" column is Char(1). Looking at the
file, there is indeed only one character in the column. I'm not sure
how to fix this, or what I can do about it. Any suggestions would be
appreciated.
I was able to successfully insert the sample rows you posted.
I can think of two things:
1) There are trailing blanks.
2) The line terminator is not CR-LF, but only CR or only LF.
Since it was the second line that failed, the first seems more likely to me.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
Bulk Insert Question
I need to move some data from an ascii file to a database. I want to use
BULK INSERT. The problem I have is there is no field seperation in the
ascii file. For example, columns 1-5 constitute a number, 6-28 constitute a
description, etc. Is there a way to use BULK INSERT without having to put a
comma or something between each field of data. Below is a small example of
data :
item description cost retail
12345thisistheitemdescription00245903599On Fri, 25 Feb 2005 16:13:13 -0500, sqlnewbie wrote:
>I need to move some data from an ascii file to a database. I want to use
>BULK INSERT. The problem I have is there is no field seperation in the
>ascii file. For example, columns 1-5 constitute a number, 6-28 constitute
a
>description, etc. Is there a way to use BULK INSERT without having to put
a
>comma or something between each field of data. Below is a small example of
>data :
>item description cost retail
> 12345thisistheitemdescription00245903599
>
Hi sqlnewbie,
I think you can do this with a formatfile. The easiest way to do this,
is to first run the bcp utility from a DOS prompt, answering all
questions and saving the information in a format file. Then, use a text
editor to check the contents of the format file and tweak it as needed.
Finally, use the FORMATFILE option of the BULK INSERT to specify this
format file for your date import.
Check out the subjects "bcp Utility (overview)" and "Using Format Files"
in Books Online for more information.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks, I'll play around with it...
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:g77v11dgleakllnsplfl2aacookufkbcum@.
4ax.com...
> On Fri, 25 Feb 2005 16:13:13 -0500, sqlnewbie wrote:
>
> Hi sqlnewbie,
> I think you can do this with a formatfile. The easiest way to do this,
> is to first run the bcp utility from a DOS prompt, answering all
> questions and saving the information in a format file. Then, use a text
> editor to check the contents of the format file and tweak it as needed.
> Finally, use the FORMATFILE option of the BULK INSERT to specify this
> format file for your date import.
> Check out the subjects "bcp Utility (overview)" and "Using Format Files"
> in Books Online for more information.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Use a format file. Here is part of the description:
If specifying a prefix length of 0 and no terminator, bcp allocates the
maximum amount of space shown in the field length prompt because this is the
maximum space that may be needed for the data type in question. The field is
treated as if it were of fixed length so that it is possible to determine
where one field ends and the next begins.
RLF
"sqlnewbie" <1@.1.com> wrote in message
news:uGbyT73GFHA.2736@.TK2MSFTNGP09.phx.gbl...
>I need to move some data from an ascii file to a database. I want to use
>BULK INSERT. The problem I have is there is no field seperation in the
>ascii file. For example, columns 1-5 constitute a number, 6-28 constitute
>a description, etc. Is there a way to use BULK INSERT without having to
>put a comma or something between each field of data. Below is a small
>example of data :
> item description cost retail
> 12345thisistheitemdescription00245903599
>
BULK INSERT. The problem I have is there is no field seperation in the
ascii file. For example, columns 1-5 constitute a number, 6-28 constitute a
description, etc. Is there a way to use BULK INSERT without having to put a
comma or something between each field of data. Below is a small example of
data :
item description cost retail
12345thisistheitemdescription00245903599On Fri, 25 Feb 2005 16:13:13 -0500, sqlnewbie wrote:
>I need to move some data from an ascii file to a database. I want to use
>BULK INSERT. The problem I have is there is no field seperation in the
>ascii file. For example, columns 1-5 constitute a number, 6-28 constitute
a
>description, etc. Is there a way to use BULK INSERT without having to put
a
>comma or something between each field of data. Below is a small example of
>data :
>item description cost retail
> 12345thisistheitemdescription00245903599
>
Hi sqlnewbie,
I think you can do this with a formatfile. The easiest way to do this,
is to first run the bcp utility from a DOS prompt, answering all
questions and saving the information in a format file. Then, use a text
editor to check the contents of the format file and tweak it as needed.
Finally, use the FORMATFILE option of the BULK INSERT to specify this
format file for your date import.
Check out the subjects "bcp Utility (overview)" and "Using Format Files"
in Books Online for more information.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks, I'll play around with it...
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:g77v11dgleakllnsplfl2aacookufkbcum@.
4ax.com...
> On Fri, 25 Feb 2005 16:13:13 -0500, sqlnewbie wrote:
>
> Hi sqlnewbie,
> I think you can do this with a formatfile. The easiest way to do this,
> is to first run the bcp utility from a DOS prompt, answering all
> questions and saving the information in a format file. Then, use a text
> editor to check the contents of the format file and tweak it as needed.
> Finally, use the FORMATFILE option of the BULK INSERT to specify this
> format file for your date import.
> Check out the subjects "bcp Utility (overview)" and "Using Format Files"
> in Books Online for more information.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Use a format file. Here is part of the description:
If specifying a prefix length of 0 and no terminator, bcp allocates the
maximum amount of space shown in the field length prompt because this is the
maximum space that may be needed for the data type in question. The field is
treated as if it were of fixed length so that it is possible to determine
where one field ends and the next begins.
RLF
"sqlnewbie" <1@.1.com> wrote in message
news:uGbyT73GFHA.2736@.TK2MSFTNGP09.phx.gbl...
>I need to move some data from an ascii file to a database. I want to use
>BULK INSERT. The problem I have is there is no field seperation in the
>ascii file. For example, columns 1-5 constitute a number, 6-28 constitute
>a description, etc. Is there a way to use BULK INSERT without having to
>put a comma or something between each field of data. Below is a small
>example of data :
> item description cost retail
> 12345thisistheitemdescription00245903599
>
Tuesday, March 20, 2012
BULK Insert problem with DateTime field
I am trying to perform a bulk insert in SQL Query analyzer. This is the Schema for my table:
Taxonomic_Units Table:INT 4 tsn
CHAR 1 unit_ind1
CHAR 35 unit_name1
CHAR 1 unit_ind2
CHAR 34 unit_name2
CHAR 7 unit_ind3
CHAR 35 unit_name3
CHAR 7 unit_ind4
CHAR 35 unit_name4
CHAR 1 unnamed_taxon_ind
CHAR 12 usage
CHAR 50 unaccept_reason
CHAR 40 credibility_rtng
CHAR 10 completeness_rtng
CHAR 7 currency_rating
SMALLINT 2 phylo_sort_seq
DATETIME 8 initial_time_stamp
INT 4 parent_tsn
INT 4 taxon_author_id
INT 4 hybrid_author_id
SMALLINT 2 kingdom_id
SMALLINT 2 rank_id
DATETIME 4 update_date
CHAR 3 uncertain_prnt_ind
I use the following SQL Statement to BULK INSERT:
BULK INSERT itis.taxonomic_units
FROM '<dir path to input file>/taxonomic_units.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n',
KEEPIDENTITY,
KEEPNULLS
)
Here is a sample of a row that I get an error when it is processed through the above BULK INSERT statement:
50||Bacteria||||||||invalid||No review; untreated NODC data|unknown|unknown||1996-06-13 14:51:08.0||||1|10|07/29/1996||
The error is:Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 17 (initial_time_stamp).
Just to make things easier on anyone who tries to help me solve this problem, the field that causes my Bulk insert statement to choke contains the data: "1996-06-13 14:51:08.0". Why is this happening? Any thoughts on how to solve it? I have been scouring help articles all day with no resolution to this problem.
For me, that works just fine...llzamboni wrote:
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 17 (initial_time_stamp).Just to make things easier on anyone who tries to help me solve this problem, the field that causes my Bulk insert statement to choke contains the data: "1996-06-13 14:51:08.0". Why is this happening? Any thoughts on how to solve it? I have been scouring help articles all day with no resolution to this problem.
SELECT CAST('1996-06-13 14:51:08.0' AS DATETIME)
Are you *sure* that row is the problem?
|||It seems like BULK INSERT is having difficulties with the (sort of) malformed date 1996-06-13 14:51:08.0
If you remove the last .0 or add two zeroes so it becomes .000 then BULK INSERT will insert the row.
However, if you instead use BCP, no changes are needed.
Apparently, BCP isn't as cranky as BULK INSERT in this case.
/Kenneth
Sunday, March 11, 2012
Bulk Insert Format File
Hi ,
I was wondering if there was a way in a format file to load a host file data field to more than one column in a table?
Thanks
Are you using SSIS?|||Yes, and I am using a bulk insert task with a format file, I am really kinda new to this, and I have figured out how to skip columns and data fields, but not this.Thursday, March 8, 2012
BULK INSERT FIELD TERMINATOR
I need to be able to insert data from a file that may be tab delimited or delimited with spaces. Right now I am using fieldterminator = '\t' but I also need to account if there are just spaces there. Can this be done?any bites?|||
I don't believe that you will be able to use multiple terminitors.
The way that I have dealth with issues like this is to create a 'data washing' step prior to the bulk load. If you are using a JOB, DTS, or SSIS, create a step that runs a small batch file that pre-processes the data, changing whatever characters to what other characters are needed, and then move on to the bulk load step.
Bulk Insert does not handle text qualifier
I'm trying to use Bulk Insert to upload a flat file.
The file is comma delimited but contains one field that has quotation marks around it. That field typically contains a comma (which of course, I don't want to consider as a field delimeter)
here is an example of the layout.
1,test,D,"OCB, England",450727,8575
I use the DTS Bulk Insert task to help me generate a format file to use with the bulk insert statement. I tell the wizard that the file is comma delimited with a text qualifer and it's fine with that. But when I run the bulk insert, it bombs with a "String or Binary data would be trunctated error.
So, any idea's on how to bulk insert to be smart enough to deal with this situation?"String or Binary data would be trunctated" usually means that the string you are trying to insert is longer than that allowed by the field in your destination table. If, for instance, your table's field is defined as 50 characters (the default) and one of your quoted strings is longer than 50 characters, you will get this error.
Either increase the size of your destination field, or cast your string as a field of the proper width before attempting to insert it.
blindman
The file is comma delimited but contains one field that has quotation marks around it. That field typically contains a comma (which of course, I don't want to consider as a field delimeter)
here is an example of the layout.
1,test,D,"OCB, England",450727,8575
I use the DTS Bulk Insert task to help me generate a format file to use with the bulk insert statement. I tell the wizard that the file is comma delimited with a text qualifer and it's fine with that. But when I run the bulk insert, it bombs with a "String or Binary data would be trunctated error.
So, any idea's on how to bulk insert to be smart enough to deal with this situation?"String or Binary data would be trunctated" usually means that the string you are trying to insert is longer than that allowed by the field in your destination table. If, for instance, your table's field is defined as 50 characters (the default) and one of your quoted strings is longer than 50 characters, you will get this error.
Either increase the size of your destination field, or cast your string as a field of the proper width before attempting to insert it.
blindman
Saturday, February 25, 2012
Bulk Insert (type mismatch) on datetime field containing NULL
Can anyone help please, I am using bulk insert for the first time.
The statement I am running is:
BULK INSERT Titles
FROM 'c:\Titles.txt'
WITH (FIRSTROW = 3,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
KEEPNULLS,
FORMATFILE = 'c:\Titles.fmt')
Titles.txt contains tab delimited data like:
ID Description StartDate ExpiryDate ParentItemID
-- --
-- -- --
440 Doctor 1 Jan 1997 0:00 NULL NULL
441 Mr 1 Jan 1990 0:00 NULL 1
If I run the bulk insert statement I get the message:
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 3, column 4
(ExpiryDate)
In the file Titles.txt, if I find and replace NULL with nothing and
then execute the statement the data inserts into the Titles table.
I need to be able to insert without having to do find and replace as I
have hundreds of files to bulk insert.
The format file Titles.fmt looks like this:
8.0
5
1 SQLCHAR 0 12 "\t" 1
ID ""
2 SQLCHAR 0 100 "\t" 2
Description Latin1_General_CI_AS
3 SQLCHAR 0 24 "\t" 3
StartDate ""
4 SQLCHAR 0 24 "\t" 4
ExpiryDate ""
5 SQLCHAR 0 12 "\t" 5
ParentItemID ""If this is not a one time deal, you'd be better off making sure that when
these files are generated, the value for column ExpireDate that is null does
not contain a string NULL.
With the existing data files, personally, I'd write a little utility to
find/replace all the 'NULL' string in the ExpireDate column with an empty
string. This can be esily done with any tool that supports regular
expressions.
Linchi
"rai_sk@.hotmail.com" wrote:
> Can anyone help please, I am using bulk insert for the first time.
> The statement I am running is:
> BULK INSERT Titles
> FROM 'c:\Titles.txt'
> WITH (FIRSTROW = 3,
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n',
> KEEPNULLS,
> FORMATFILE = 'c:\Titles.fmt')
> Titles.txt contains tab delimited data like:
> ID Description StartDate ExpiryDate ParentItemID
> -- --
> -- -- --
> 440 Doctor 1 Jan 1997 0:00 NULL NULL
> 441 Mr 1 Jan 1990 0:00 NULL 1
> If I run the bulk insert statement I get the message:
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 4
> (ExpiryDate)
> In the file Titles.txt, if I find and replace NULL with nothing and
> then execute the statement the data inserts into the Titles table.
> I need to be able to insert without having to do find and replace as I
> have hundreds of files to bulk insert.
> The format file Titles.fmt looks like this:
> 8.0
> 5
> 1 SQLCHAR 0 12 "\t" 1
> ID ""
> 2 SQLCHAR 0 100 "\t" 2
> Description Latin1_General_CI_AS
> 3 SQLCHAR 0 24 "\t" 3
> StartDate ""
> 4 SQLCHAR 0 24 "\t" 4
> ExpiryDate ""
> 5 SQLCHAR 0 12 "\t" 5
> ParentItemID ""
>
The statement I am running is:
BULK INSERT Titles
FROM 'c:\Titles.txt'
WITH (FIRSTROW = 3,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
KEEPNULLS,
FORMATFILE = 'c:\Titles.fmt')
Titles.txt contains tab delimited data like:
ID Description StartDate ExpiryDate ParentItemID
-- --
-- -- --
440 Doctor 1 Jan 1997 0:00 NULL NULL
441 Mr 1 Jan 1990 0:00 NULL 1
If I run the bulk insert statement I get the message:
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 3, column 4
(ExpiryDate)
In the file Titles.txt, if I find and replace NULL with nothing and
then execute the statement the data inserts into the Titles table.
I need to be able to insert without having to do find and replace as I
have hundreds of files to bulk insert.
The format file Titles.fmt looks like this:
8.0
5
1 SQLCHAR 0 12 "\t" 1
ID ""
2 SQLCHAR 0 100 "\t" 2
Description Latin1_General_CI_AS
3 SQLCHAR 0 24 "\t" 3
StartDate ""
4 SQLCHAR 0 24 "\t" 4
ExpiryDate ""
5 SQLCHAR 0 12 "\t" 5
ParentItemID ""If this is not a one time deal, you'd be better off making sure that when
these files are generated, the value for column ExpireDate that is null does
not contain a string NULL.
With the existing data files, personally, I'd write a little utility to
find/replace all the 'NULL' string in the ExpireDate column with an empty
string. This can be esily done with any tool that supports regular
expressions.
Linchi
"rai_sk@.hotmail.com" wrote:
> Can anyone help please, I am using bulk insert for the first time.
> The statement I am running is:
> BULK INSERT Titles
> FROM 'c:\Titles.txt'
> WITH (FIRSTROW = 3,
> FIELDTERMINATOR = '\t',
> ROWTERMINATOR = '\n',
> KEEPNULLS,
> FORMATFILE = 'c:\Titles.fmt')
> Titles.txt contains tab delimited data like:
> ID Description StartDate ExpiryDate ParentItemID
> -- --
> -- -- --
> 440 Doctor 1 Jan 1997 0:00 NULL NULL
> 441 Mr 1 Jan 1990 0:00 NULL 1
> If I run the bulk insert statement I get the message:
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 4
> (ExpiryDate)
> In the file Titles.txt, if I find and replace NULL with nothing and
> then execute the statement the data inserts into the Titles table.
> I need to be able to insert without having to do find and replace as I
> have hundreds of files to bulk insert.
> The format file Titles.fmt looks like this:
> 8.0
> 5
> 1 SQLCHAR 0 12 "\t" 1
> ID ""
> 2 SQLCHAR 0 100 "\t" 2
> Description Latin1_General_CI_AS
> 3 SQLCHAR 0 24 "\t" 3
> StartDate ""
> 4 SQLCHAR 0 24 "\t" 4
> ExpiryDate ""
> 5 SQLCHAR 0 12 "\t" 5
> ParentItemID ""
>
BULK INSERT - FIELDTERMINATOR
Hi, I need to bulk insert a .csv file into a database table. However, the problem I am encounter now is the in one of my field which is nvarchar(50) ... the data in .csv has ',' - comma ... which my FIELDTERMINATOR in BULK INSERT is also comma. It gives me error because the next field is an integer field. How do I solve this problem? I hereby give few data example as follow:
In .csv file:
============
"J2825JA","FEB22,MAR1,8/05 - RE","22FEB05",45,20000
"J2825JB","FEB22,MAR1,8/05 - RE","22FEB06",765,435653
Query
=========
SQL = "BULK INSERT [" & sTableName & "]" & _
" FROM '" & sNewFile & "'" & _
" WITH ( FIELDTERMINATOR = ',', " & _
" FIRSTROW = 1, " & _
" ROWTERMINATOR = '\n' ) "
BTW, this is written in ASP. But I this is DB problem. So I posted here. Thanks in advance.
Personally I never use comma-delimited files because of this sort of thing; there is nothing wrong with the format per se, but it seems like many software programs do not know how to handle it correctly. Is there any way you can have the file supplied in tab-delimited format? SQL Server will handle this for you a lot more gracefully. It will also fixed-width format readily.If this is not a possibility, Access should be able to import the file as-is. FoxPro will also handle this format. And then you can export it in a SQL Server-friendly format.
Friday, February 24, 2012
bulk insert
Hi there !
a pretty silly question about bulk insert...
i'm trying to insert data into a table, but the first field is
quotation marked..so I wrote this in the fileformat
8.0
2
1 SQLCHAR 0 0 "" 0
dum1
2 SQLCHAR 0 50 "\"" 1
px_orig SQL_Latin1_General_CP1_CI_AS
where px_orig is the name of my field..
Whan I try to bulk insert using this, I get this error:
Cannot perform bulk insert. Invalid collation name for source 1 in
format file '...etc'
any idea'
thanks a lot...
++
VinceYou need to specify the leading quote as well as the row terminator in the
format file. Try something like:
8.0
3
1 SQLCHAR 0 0 "\"" 0 quote ""
2 SQLCHAR 0 50 "\"" 1 px_orig
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\r\n" 0 crlf ""
Hope this helps.
Dan Guzman
SQL Server MVP
"Vince .>" <vincent@.<remove> wrote in message
news:guai41dtlfg3qfi3ad7o6s9rpmt7f5afq4@.
4ax.com...
> Hi there !
> a pretty silly question about bulk insert...
> i'm trying to insert data into a table, but the first field is
> quotation marked..so I wrote this in the fileformat
> 8.0
> 2
> 1 SQLCHAR 0 0 "" 0
> dum1
> 2 SQLCHAR 0 50 "\"" 1
> px_orig SQL_Latin1_General_CP1_CI_AS
> where px_orig is the name of my field..
> Whan I try to bulk insert using this, I get this error:
> Cannot perform bulk insert. Invalid collation name for source 1 in
> format file '...etc'
> any idea'
> thanks a lot...
> ++
> Vince|||thanks :)
a pretty silly question about bulk insert...
i'm trying to insert data into a table, but the first field is
quotation marked..so I wrote this in the fileformat
8.0
2
1 SQLCHAR 0 0 "" 0
dum1
2 SQLCHAR 0 50 "\"" 1
px_orig SQL_Latin1_General_CP1_CI_AS
where px_orig is the name of my field..
Whan I try to bulk insert using this, I get this error:
Cannot perform bulk insert. Invalid collation name for source 1 in
format file '...etc'
any idea'
thanks a lot...
++
VinceYou need to specify the leading quote as well as the row terminator in the
format file. Try something like:
8.0
3
1 SQLCHAR 0 0 "\"" 0 quote ""
2 SQLCHAR 0 50 "\"" 1 px_orig
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\r\n" 0 crlf ""
Hope this helps.
Dan Guzman
SQL Server MVP
"Vince .>" <vincent@.<remove> wrote in message
news:guai41dtlfg3qfi3ad7o6s9rpmt7f5afq4@.
4ax.com...
> Hi there !
> a pretty silly question about bulk insert...
> i'm trying to insert data into a table, but the first field is
> quotation marked..so I wrote this in the fileformat
> 8.0
> 2
> 1 SQLCHAR 0 0 "" 0
> dum1
> 2 SQLCHAR 0 50 "\"" 1
> px_orig SQL_Latin1_General_CP1_CI_AS
> where px_orig is the name of my field..
> Whan I try to bulk insert using this, I get this error:
> Cannot perform bulk insert. Invalid collation name for source 1 in
> format file '...etc'
> any idea'
> thanks a lot...
> ++
> Vince|||thanks :)
Subscribe to:
Posts (Atom)