BULK INSERT to a Temporary Table

I wanto to execute a BULK INSERT command into a Temporary Table:
CREATE TABLE #textfile (line varchar(8000))
-- Read the text file into the temp table
BULK INSERT #textfile FROM 'c\Projects.txt'
But when I execute this command a recieve the following error message:
"The current user is not the database or object owner of table
'#textfile'. Cannot perform SET operation."
Im logged in a Microsoft SQL Server 2000, with a login account that
belogs to the role db_owner of the database Im working, but does not
belong to the role db_owner of tempdb database. Is it necessary?
What am I doing wrong?
Thanks,
Paulo
*** Sent via Developersdex http://www.codecomments.com ***
Hi
You may wantr to check out
http://support.microsoft.com/default...b;en-us;302621 regarding
permissions needed.
John
"Paulo Andre Ortega Ribeiro" wrote:

> I wanto to execute a BULK INSERT command into a Temporary Table:
> CREATE TABLE #textfile (line varchar(8000))
> -- Read the text file into the temp table
> BULK INSERT #textfile FROM 'c\Projects.txt'
> But when I execute this command a recieve the following error message:
> "The current user is not the database or object owner of table
> '#textfile'. Cannot perform SET operation."
> I4m logged in a Microsoft SQL Server 2000, with a login account that
> belogs to the role db_owner of the database I4m working, but does not
> belong to the role db_owner of tempdb database. Is it necessary?
> What am I doing wrong?
> Thanks,
> Paulo
>
> *** Sent via Developersdex http://www.codecomments.com ***
>

BULK INSERT to a Temporary Table

I wanto to execute a BULK INSERT command into a Temporary Table:
CREATE TABLE #textfile (line varchar(8000))
-- Read the text file into the temp table
BULK INSERT #textfile FROM 'c\Projects.txt'
But when I execute this command a recieve the following error message:
"The current user is not the database or object owner of table
'#textfile'. Cannot perform SET operation."
Im logged in a Microsoft SQL Server 2000, with a login account that
belogs to the role db_owner of the database Im working, but does not
belong to the role db_owner of tempdb database. Is it necessary?
What am I doing wrong?
Thanks,
Paulo
*** Sent via Developersdex http://www.codecomments.com ***Hi
You may wantr to check out
http://support.microsoft.com/defaul...kb;en-us;302621 regarding
permissions needed.
John
"Paulo Andre Ortega Ribeiro" wrote:

> I wanto to execute a BULK INSERT command into a Temporary Table:
> CREATE TABLE #textfile (line varchar(8000))
> -- Read the text file into the temp table
> BULK INSERT #textfile FROM 'c\Projects.txt'
> But when I execute this command a recieve the following error message:
> "The current user is not the database or object owner of table
> '#textfile'. Cannot perform SET operation."
> I4m logged in a Microsoft SQL Server 2000, with a login account that
> belogs to the role db_owner of the database I4m working, but does not
> belong to the role db_owner of tempdb database. Is it necessary?
> What am I doing wrong?
> Thanks,
> Paulo
>
> *** Sent via Developersdex http://www.codecomments.com ***
>

BULK INSERT to a Temporary Table

I wanto to execute a BULK INSERT command into a Temporary Table:
CREATE TABLE #textfile (line varchar(8000))
-- Read the text file into the temp table
BULK INSERT #textfile FROM 'c\Projects.txt'
But when I execute this command a recieve the following error message:
"The current user is not the database or object owner of table
'#textfile'. Cannot perform SET operation."
I´m logged in a Microsoft SQL Server 2000, with a login account that
belogs to the role db_owner of the database I´m working, but does not
belong to the role db_owner of tempdb database. Is it necessary?
What am I doing wrong?
Thanks,
Paulo
*** Sent via Developersdex http://www.developersdex.com ***Hi
You may wantr to check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;302621 regarding
permissions needed.
John
"Paulo Andre Ortega Ribeiro" wrote:
> I wanto to execute a BULK INSERT command into a Temporary Table:
> CREATE TABLE #textfile (line varchar(8000))
> -- Read the text file into the temp table
> BULK INSERT #textfile FROM 'c\Projects.txt'
> But when I execute this command a recieve the following error message:
> "The current user is not the database or object owner of table
> '#textfile'. Cannot perform SET operation."
> I4m logged in a Microsoft SQL Server 2000, with a login account that
> belogs to the role db_owner of the database I4m working, but does not
> belong to the role db_owner of tempdb database. Is it necessary?
> What am I doing wrong?
> Thanks,
> Paulo
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

Bulk Insert of comma seperated textfile with text qualifiers

I want to insert a text file with comma seperated fields. De text-field have text qualifiers:
"field1","field2","field3"
I made a Bulk Insert with FMT file. I choose comma seperated with text qualifiers. In the example screen the data looked perfect. After the insert however the text qualifiers where imported into the colimns as well. (The first column contained "field1" instead of field1)
Does anybody have suggestions on how to solve this?Frank
Try
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> I want to insert a text file with comma seperated fields. De text-field
have text qualifiers:
> "field1","field2","field3"
> I made a Bulk Insert with FMT file. I choose comma seperated with text
qualifiers. In the example screen the data looked perfect. After the insert
however the text qualifiers where imported into the colimns as well. (The
first column contained "field1" instead of field1)
> Does anybody have suggestions on how to solve this?|||Sorry
It should be
exec master..xp_cmdshell 'BCP northwind..ww OUT
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7owpFNZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Frank
> Try
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> exec master..xp_cmdshell 'BCP northwind..ww IN
> d:\test1.txt -c -C850 -SServer -Usa -Pp'
>
> "FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
> news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> > I want to insert a text file with comma seperated fields. De text-field
> have text qualifiers:
> > "field1","field2","field3"
> > I made a Bulk Insert with FMT file. I choose comma seperated with text
> qualifiers. In the example screen the data looked perfect. After the
insert
> however the text qualifiers where imported into the colimns as well. (The
> first column contained "field1" instead of field1)
> >
> > Does anybody have suggestions on how to solve this?
>|||Hi Uri,
I don't think I understand your reply to my question. I will firts try to better explain what I want, maybe you can than explain how your reply fits into that.
My goal is an import from a textfile. This textfile is comma-delimited with text qualifiers for the char-fields.
I tried to do this Import sing the Bulk Insert Task, privided by DTS. Within this Task it is possible to define a format file (fmt) in which the wizard writes information about the import-file. Following the wizard it will ask you if the file is fixed length or delimited. I choose delimited, choose the text qualifier and hit the Next-key. Than I choose Comma as field seperator. In the example list the file was correctly shown.
When I executed the Bulk Insert Task it imported the file without errors. In the destination column however you could see that the text qualifiers were ignored. The double-quotes were in the fields and every comma in the file was used as a field seperator.
Obviously the above is not what I desired. I tested the same in Excel. In Excel the Import went very well. To me it looks like a bug in SQL-Server.
"Uri Dimant" wrote:
> Sorry
> It should be
> exec master..xp_cmdshell 'BCP northwind..ww OUT
> d:\test1.txt -c -C850 -SServer -Usa -Pp'
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e7owpFNZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> > Frank
> > Try
> > create table ww
> > (
> > col1 int,
> > col2 varchar(50),
> > col3 varchar (50)
> > )
> >
> > insert into ww values (47,'ReadyShip','(503)888-999')
> > insert into ww values (48,'MyShipper','(503)1212-454')
> > insert into ww values (49,'ReadyShip','(45)888-999')
> > insert into ww values (50,'MyShipper','(545)1212-454')
> >
> > exec master..xp_cmdshell 'BCP northwind..ww IN
> > d:\test1.txt -c -C850 -SServer -Usa -Pp'
> >
> >
> > "FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
> > news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> > > I want to insert a text file with comma seperated fields. De text-field
> > have text qualifiers:
> > > "field1","field2","field3"
> > > I made a Bulk Insert with FMT file. I choose comma seperated with text
> > qualifiers. In the example screen the data looked perfect. After the
> insert
> > however the text qualifiers where imported into the colimns as well. (The
> > first column contained "field1" instead of field1)
> > >
> > > Does anybody have suggestions on how to solve this?
> >
> >
>
>|||Under [Transform Data Task Properties] -> [Options] tab specify [Text
qualifier] as [Double Quote {"}].
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ ×
ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> Hi Uri,
> I don't think I understand your reply to my question. I will firts try to
better explain what I want, maybe you can than explain how your reply fits
into that.
> My goal is an import from a textfile. This textfile is comma-delimited
with text qualifiers for the char-fields.
> I tried to do this Import sing the Bulk Insert Task, privided by DTS.
Within this Task it is possible to define a format file (fmt) in which the
wizard writes information about the import-file. Following the wizard it
will ask you if the file is fixed length or delimited. I choose delimited,
choose the text qualifier and hit the Next-key. Than I choose Comma as field
seperator. In the example list the file was correctly shown.
> When I executed the Bulk Insert Task it imported the file without errors.
In the destination column however you could see that the text qualifiers
were ignored. The double-quotes were in the fields and every comma in the
file was used as a field seperator.
> Obviously the above is not what I desired. I tested the same in Excel. In
Excel the Import went very well. To me it looks like a bug in SQL-Server.
> "Uri Dimant" wrote:
> > Sorry
> > It should be
> > exec master..xp_cmdshell 'BCP northwind..ww OUT
> > d:\test1.txt -c -C850 -SServer -Usa -Pp'
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:e7owpFNZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> > > Frank
> > > Try
> > > create table ww
> > > (
> > > col1 int,
> > > col2 varchar(50),
> > > col3 varchar (50)
> > > )
> > >
> > > insert into ww values (47,'ReadyShip','(503)888-999')
> > > insert into ww values (48,'MyShipper','(503)1212-454')
> > > insert into ww values (49,'ReadyShip','(45)888-999')
> > > insert into ww values (50,'MyShipper','(545)1212-454')
> > >
> > > exec master..xp_cmdshell 'BCP northwind..ww IN
> > > d:\test1.txt -c -C850 -SServer -Usa -Pp'
> > >
> > >
> > > "FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
> > > news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> > > > I want to insert a text file with comma seperated fields. De
text-field
> > > have text qualifiers:
> > > > "field1","field2","field3"
> > > > I made a Bulk Insert with FMT file. I choose comma seperated with
text
> > > qualifiers. In the example screen the data looked perfect. After the
> > insert
> > > however the text qualifiers where imported into the colimns as well.
(The
> > > first column contained "field1" instead of field1)
> > > >
> > > > Does anybody have suggestions on how to solve this?
> > >
> > >
> >
> >
> >|||Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk Insert Task is supposed to be much faster in loading the data.
As mentioned in my posts I did select the Text qualifier within the Bulk Insert Task.
"Ramon" wrote:
> Under [Transform Data Task Properties] -> [Options] tab specify [Text
> qualifier] as [Double Quote {"}].
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> Ã?Ã?Ã?Ã?Ã?Ã?Ã?/Ã?Ã?Ã?Ã?Ã?Ã?Ã?Ã? Ã?
> Ã?Ã?Ã?Ã?Ã?Ã?Ã?Ã? Ã?Ã?Ã?Ã?Ã?Ã?Ã?Ã?Ã?:
> news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> > Hi Uri,
> >
> > I don't think I understand your reply to my question. I will firts try to
> better explain what I want, maybe you can than explain how your reply fits
> into that.
> >
> > My goal is an import from a textfile. This textfile is comma-delimited
> with text qualifiers for the char-fields.
> >
> > I tried to do this Import sing the Bulk Insert Task, privided by DTS.
> Within this Task it is possible to define a format file (fmt) in which the
> wizard writes information about the import-file. Following the wizard it
> will ask you if the file is fixed length or delimited. I choose delimited,
> choose the text qualifier and hit the Next-key. Than I choose Comma as field
> seperator. In the example list the file was correctly shown.
> >
> > When I executed the Bulk Insert Task it imported the file without errors.
> In the destination column however you could see that the text qualifiers
> were ignored. The double-quotes were in the fields and every comma in the
> file was used as a field seperator.
> >
> > Obviously the above is not what I desired. I tested the same in Excel. In
> Excel the Import went very well. To me it looks like a bug in SQL-Server.
> >
> > "Uri Dimant" wrote:
> >
> > > Sorry
> > > It should be
> > > exec master..xp_cmdshell 'BCP northwind..ww OUT
> > > d:\test1.txt -c -C850 -SServer -Usa -Pp'
> > >
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:e7owpFNZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> > > > Frank
> > > > Try
> > > > create table ww
> > > > (
> > > > col1 int,
> > > > col2 varchar(50),
> > > > col3 varchar (50)
> > > > )
> > > >
> > > > insert into ww values (47,'ReadyShip','(503)888-999')
> > > > insert into ww values (48,'MyShipper','(503)1212-454')
> > > > insert into ww values (49,'ReadyShip','(45)888-999')
> > > > insert into ww values (50,'MyShipper','(545)1212-454')
> > > >
> > > > exec master..xp_cmdshell 'BCP northwind..ww IN
> > > > d:\test1.txt -c -C850 -SServer -Usa -Pp'
> > > >
> > > >
> > > > "FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
> > > > news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> > > > > I want to insert a text file with comma seperated fields. De
> text-field
> > > > have text qualifiers:
> > > > > "field1","field2","field3"
> > > > > I made a Bulk Insert with FMT file. I choose comma seperated with
> text
> > > > qualifiers. In the example screen the data looked perfect. After the
> > > insert
> > > > however the text qualifiers where imported into the colimns as well.
> (The
> > > > first column contained "field1" instead of field1)
> > > > >
> > > > > Does anybody have suggestions on how to solve this?
> > > >
> > > >
> > >
> > >
> > >
>
>|||Bulk Insert does not support behavior you required (but this is not a bug).
You could use more sophisticated Transform Data for this case.
Personally I load data by BULK INSERT command into the intermediate table
and then load formatted data into the target table.
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ ×
ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk
Insert Task is supposed to be much faster in loading the data.
> As mentioned in my posts I did select the Text qualifier within the Bulk
Insert Task.
> "Ramon" wrote:
> > Under [Transform Data Task Properties] -> [Options] tab specify [Text
> > qualifier] as [Double Quote {"}].
> >
> >
> > --
> > Ramon @. Havana Club,
> > SQL Server 2000 MCDBA
> >
> >
> >
> > "freterink" <freterink@.discussions.microsoft.com> OIIAYEI/OIIAYEIA ?
> > II?IOONE OIAAOAYAA:
> > news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> > > Hi Uri,
> > >
> > > I don't think I understand your reply to my question. I will firts try
to
> > better explain what I want, maybe you can than explain how your reply
fits
> > into that.
> > >
> > > My goal is an import from a textfile. This textfile is comma-delimited
> > with text qualifiers for the char-fields.
> > >
> > > I tried to do this Import sing the Bulk Insert Task, privided by DTS.
> > Within this Task it is possible to define a format file (fmt) in which
the
> > wizard writes information about the import-file. Following the wizard it
> > will ask you if the file is fixed length or delimited. I choose
delimited,
> > choose the text qualifier and hit the Next-key. Than I choose Comma as
field
> > seperator. In the example list the file was correctly shown.
> > >
> > > When I executed the Bulk Insert Task it imported the file without
errors.
> > In the destination column however you could see that the text qualifiers
> > were ignored. The double-quotes were in the fields and every comma in
the
> > file was used as a field seperator.
> > >
> > > Obviously the above is not what I desired. I tested the same in Excel.
In
> > Excel the Import went very well. To me it looks like a bug in
SQL-Server.
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > Sorry
> > > > It should be
> > > > exec master..xp_cmdshell 'BCP northwind..ww OUT
> > > > d:\test1.txt -c -C850 -SServer -Usa -Pp'
> > > >
> > > >
> > > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > > news:e7owpFNZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> > > > > Frank
> > > > > Try
> > > > > create table ww
> > > > > (
> > > > > col1 int,
> > > > > col2 varchar(50),
> > > > > col3 varchar (50)
> > > > > )
> > > > >
> > > > > insert into ww values (47,'ReadyShip','(503)888-999')
> > > > > insert into ww values (48,'MyShipper','(503)1212-454')
> > > > > insert into ww values (49,'ReadyShip','(45)888-999')
> > > > > insert into ww values (50,'MyShipper','(545)1212-454')
> > > > >
> > > > > exec master..xp_cmdshell 'BCP northwind..ww IN
> > > > > d:\test1.txt -c -C850 -SServer -Usa -Pp'
> > > > >
> > > > >
> > > > > "FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
> > > > > news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> > > > > > I want to insert a text file with comma seperated fields. De
> > text-field
> > > > > have text qualifiers:
> > > > > > "field1","field2","field3"
> > > > > > I made a Bulk Insert with FMT file. I choose comma seperated
with
> > text
> > > > > qualifiers. In the example screen the data looked perfect. After
the
> > > > insert
> > > > > however the text qualifiers where imported into the colimns as
well.
> > (The
> > > > > first column contained "field1" instead of field1)
> > > > > >
> > > > > > Does anybody have suggestions on how to solve this?
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||You can also achieve your goal with Bulk Insert some tricky way:
* Suppose you have table:
CREATE TABLE [dbo].[Table1] (
[ColA] [varchar] (50) NOT NULL,
[ColB] [varchar] (50) NOT NULL,
[ColC] [varchar] (50) NOT NULL)
* and file to fe imported contents:
"Value11","Value12","Value13"
"Value21","Value22","Value23"
* then your format file contents should be:
7.0
4
1 SQLCHAR 0 50 "\"" 0 ColA
2 SQLCHAR 0 50 "\",\"" 1 ColA
3 SQLCHAR 0 50 "\",\"" 2 ColB
4 SQLCHAR 0 50 "\"\r\n" 3 ColC
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"Ramon" <Nickolaychuk@.Mail.Ru> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:urpv5MZZEHA.2444@.tk2msftngp13.phx.gbl...
> Bulk Insert does not support behavior you required (but this is not a
bug).
> You could use more sophisticated Transform Data for this case.
> Personally I load data by BULK INSERT command into the intermediate table
> and then load formatted data into the target table.
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ ×
> ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
> news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> > Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk
> Insert Task is supposed to be much faster in loading the data.
> >
> > As mentioned in my posts I did select the Text qualifier within the Bulk
> Insert Task.
> >
> > "Ramon" wrote:
> >
> > > Under [Transform Data Task Properties] -> [Options] tab specify [Text
> > > qualifier] as [Double Quote {"}].
> > >
> > >
> > > --
> > > Ramon @. Havana Club,
> > > SQL Server 2000 MCDBA
> > >
> > >
> > >
> > > "freterink" <freterink@.discussions.microsoft.com> OIIAYEI/OIIAYEIA ?
> > > II?IOONE OIAAOAYAA:
> > > news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> > > > Hi Uri,
> > > >
> > > > I don't think I understand your reply to my question. I will firts
try
> to
> > > better explain what I want, maybe you can than explain how your reply
> fits
> > > into that.
> > > >
> > > > My goal is an import from a textfile. This textfile is
comma-delimited
> > > with text qualifiers for the char-fields.
> > > >
> > > > I tried to do this Import sing the Bulk Insert Task, privided by
DTS.
> > > Within this Task it is possible to define a format file (fmt) in which
> the
> > > wizard writes information about the import-file. Following the wizard
it
> > > will ask you if the file is fixed length or delimited. I choose
> delimited,
> > > choose the text qualifier and hit the Next-key. Than I choose Comma as
> field
> > > seperator. In the example list the file was correctly shown.
> > > >
> > > > When I executed the Bulk Insert Task it imported the file without
> errors.
> > > In the destination column however you could see that the text
qualifiers
> > > were ignored. The double-quotes were in the fields and every comma in
> the
> > > file was used as a field seperator.
> > > >
> > > > Obviously the above is not what I desired. I tested the same in
Excel.
> In
> > > Excel the Import went very well. To me it looks like a bug in
> SQL-Server.
> > > >
> > > > "Uri Dimant" wrote:
> > > >
> > > > > Sorry
> > > > > It should be
> > > > > exec master..xp_cmdshell 'BCP northwind..ww OUT
> > > > > d:\test1.txt -c -C850 -SServer -Usa -Pp'
> > > > >
> > > > >
> > > > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > > > news:e7owpFNZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> > > > > > Frank
> > > > > > Try
> > > > > > create table ww
> > > > > > (
> > > > > > col1 int,
> > > > > > col2 varchar(50),
> > > > > > col3 varchar (50)
> > > > > > )
> > > > > >
> > > > > > insert into ww values (47,'ReadyShip','(503)888-999')
> > > > > > insert into ww values (48,'MyShipper','(503)1212-454')
> > > > > > insert into ww values (49,'ReadyShip','(45)888-999')
> > > > > > insert into ww values (50,'MyShipper','(545)1212-454')
> > > > > >
> > > > > > exec master..xp_cmdshell 'BCP northwind..ww IN
> > > > > > d:\test1.txt -c -C850 -SServer -Usa -Pp'
> > > > > >
> > > > > >
> > > > > > "FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
> > > > > > news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> > > > > > > I want to insert a text file with comma seperated fields. De
> > > text-field
> > > > > > have text qualifiers:
> > > > > > > "field1","field2","field3"
> > > > > > > I made a Bulk Insert with FMT file. I choose comma seperated
> with
> > > text
> > > > > > qualifiers. In the example screen the data looked perfect. After
> the
> > > > > insert
> > > > > > however the text qualifiers where imported into the colimns as
> well.
> > > (The
> > > > > > first column contained "field1" instead of field1)
> > > > > > >
> > > > > > > Does anybody have suggestions on how to solve this?
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>

Bulk Insert of comma seperated textfile with text qualifiers

I want to insert a text file with comma seperated fields. De text-field have
text qualifiers:
"field1","field2","field3"
I made a Bulk Insert with FMT file. I choose comma seperated with text quali
fiers. In the example screen the data looked perfect. After the insert howev
er the text qualifiers where imported into the colimns as well. (The first c
olumn contained "field1" in
stead of field1)
Does anybody have suggestions on how to solve this?Frank
Try
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> I want to insert a text file with comma seperated fields. De text-field
have text qualifiers:
> "field1","field2","field3"
> I made a Bulk Insert with FMT file. I choose comma seperated with text
qualifiers. In the example screen the data looked perfect. After the insert
however the text qualifiers where imported into the colimns as well. (The
first column contained "field1" instead of field1)
> Does anybody have suggestions on how to solve this?|||Sorry
It should be
exec master..xp_cmdshell 'BCP northwind..ww OUT
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7owpFNZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Frank
> Try
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> exec master..xp_cmdshell 'BCP northwind..ww IN
> d:\test1.txt -c -C850 -SServer -Usa -Pp'
>
> "FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
> news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> have text qualifiers:
> qualifiers. In the example screen the data looked perfect. After the
insert
> however the text qualifiers where imported into the colimns as well. (The
> first column contained "field1" instead of field1)
>|||Hi Uri,
I don't think I understand your reply to my question. I will firts try to be
tter explain what I want, maybe you can than explain how your reply fits int
o that.
My goal is an import from a textfile. This textfile is comma-delimited with
text qualifiers for the char-fields.
I tried to do this Import sing the Bulk Insert Task, privided by DTS. Within
this Task it is possible to define a format file (fmt) in which the wizard
writes information about the import-file. Following the wizard it will ask y
ou if the file is fixed len
gth or delimited. I choose delimited, choose the text qualifier and hit the
Next-key. Than I choose Comma as field seperator. In the example list the fi
le was correctly shown.
When I executed the Bulk Insert Task it imported the file without errors. In
the destination column however you could see that the text qualifiers were
ignored. The double-quotes were in the fields and every comma in the file wa
s used as a field seperator|||Under [Transform Data Task Properties] -> [Options] tab specify [
;Text
qualifier] as [Double Quote {"}].
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> /
:
news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> Hi Uri,
> I don't think I understand your reply to my question. I will firts try to
better explain what I want, maybe you can than explain how your reply fits
into that.
> My goal is an import from a textfile. This textfile is comma-delimited
with text qualifiers for the char-fields.
> I tried to do this Import sing the Bulk Insert Task, privided by DTS.
Within this Task it is possible to define a format file (fmt) in which the
wizard writes information about the import-file. Following the wizard it
will ask you if the file is fixed length or delimited. I choose delimited,
choose the text qualifier and hit the Next-key. Than I choose Comma as field
seperator. In the example list the file was correctly shown.
> When I executed the Bulk Insert Task it imported the file without errors.
In the destination column however you could see that the text qualifiers
were ignored. The double-quotes were in the fields and every comma in the
file was used as a field seperator.
> Obviously the above is not what I desired. I tested the same in Excel. In
Excel the Import went very well. To me it looks like a bug in SQL-Server.[vbcol=seagreen]
> "Uri Dimant" wrote:
>
text-field[vbcol=seagreen]
text[vbcol=seagreen]
(The[vbcol=seagreen]|||Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk Inser
t Task is supposed to be much faster in loading the data.
As mentioned in my posts I did select the Text qualifier within the Bulk Ins
ert Task.
"Ramon" wrote:

> Under [Transform Data Task Properties] -> [Options] tab specify &#
91;Text
> qualifier] as [Double Quote {"}].
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> ó???Yéì/ó???
éìá ×
> ??×?ó??è óì???àY??:
> news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> better explain what I want, maybe you can than explain how your reply fits
> into that.
> with text qualifiers for the char-fields.
> Within this Task it is possible to define a format file (fmt) in which the
> wizard writes information about the import-file. Following the wizard it
> will ask you if the file is fixed length or delimited. I choose delimited,
> choose the text qualifier and hit the Next-key. Than I choose Comma as fie
ld
> seperator. In the example list the file was correctly shown.
> In the destination column however you could see that the text qualifiers
> were ignored. The double-quotes were in the fields and every comma in the
> file was used as a field seperator.
> Excel the Import went very well. To me it looks like a bug in SQL-Server.
> text-field
> text
> (The
>
>|||Bulk Insert does not support behavior you required (but this is not a bug).
You could use more sophisticated Transform Data for this case.
Personally I load data by BULK INSERT command into the intermediate table
and then load formatted data into the target table.
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> /
:
news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk
Insert Task is supposed to be much faster in loading the data.
> As mentioned in my posts I did select the Text qualifier within the Bulk
Insert Task.[vbcol=seagreen]
> "Ramon" wrote:
>
to[vbcol=seagreen]
fits[vbcol=seagreen]
the[vbcol=seagreen]
delimited,[vbcol=seagreen]
field[vbcol=seagreen]
errors.[vbcol=seagreen]
the[vbcol=seagreen]
In[vbcol=seagreen]
SQL-Server.[vbcol=seagreen]
with[vbcol=seagreen]
the[vbcol=seagreen]
well.[vbcol=seagreen]|||You can also achieve your goal with Bulk Insert some tricky way:
* Suppose you have table:
CREATE TABLE [dbo].[Table1] (
[ColA] [varchar] (50) NOT NULL,
[ColB] [varchar] (50) NOT NULL,
[ColC] [varchar] (50) NOT NULL)
* and file to fe imported contents:
"Value11","Value12","Value13"
"Value21","Value22","Value23"
* then your format file contents should be:
7.0
4
1 SQLCHAR 0 50 "\"" 0 ColA
2 SQLCHAR 0 50 "\",\"" 1 ColA
3 SQLCHAR 0 50 "\",\"" 2 ColB
4 SQLCHAR 0 50 "\"\r\n" 3 ColC
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"Ramon" <Nickolaychuk@.Mail.Ru> / :
news:urpv5MZZEHA.2444@.tk2msftngp13.phx.gbl...
> Bulk Insert does not support behavior you required (but this is not a
bug).
> You could use more sophisticated Transform Data for this case.
> Personally I load data by BULK INSERT command into the intermediate table
> and then load formatted data into the target table.
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> /
> :
> news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Insert Task is supposed to be much faster in loading the data.
> Insert Task.
try[vbcol=seagreen]
> to
> fits
comma-delimited[vbcol=seagreen]
DTS.[vbcol=seagreen]
> the
it[vbcol=seagreen]
> delimited,
> field
> errors.
qualifiers[vbcol=seagreen]
> the
Excel.[vbcol=seagreen]
> In
> SQL-Server.
> with
> the
> well.
>

Bulk Insert of comma seperated textfile with text qualifiers

I want to insert a text file with comma seperated fields. De text-field have text qualifiers:
"field1","field2","field3"
I made a Bulk Insert with FMT file. I choose comma seperated with text qualifiers. In the example screen the data looked perfect. After the insert however the text qualifiers where imported into the colimns as well. (The first column contained "field1" in
stead of field1)
Does anybody have suggestions on how to solve this?
Frank
Try
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> I want to insert a text file with comma seperated fields. De text-field
have text qualifiers:
> "field1","field2","field3"
> I made a Bulk Insert with FMT file. I choose comma seperated with text
qualifiers. In the example screen the data looked perfect. After the insert
however the text qualifiers where imported into the colimns as well. (The
first column contained "field1" instead of field1)
> Does anybody have suggestions on how to solve this?
|||Sorry
It should be
exec master..xp_cmdshell 'BCP northwind..ww OUT
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7owpFNZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Frank
> Try
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> exec master..xp_cmdshell 'BCP northwind..ww IN
> d:\test1.txt -c -C850 -SServer -Usa -Pp'
>
> "FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
> news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> have text qualifiers:
> qualifiers. In the example screen the data looked perfect. After the
insert
> however the text qualifiers where imported into the colimns as well. (The
> first column contained "field1" instead of field1)
>
|||Hi Uri,
I don't think I understand your reply to my question. I will firts try to better explain what I want, maybe you can than explain how your reply fits into that.
My goal is an import from a textfile. This textfile is comma-delimited with text qualifiers for the char-fields.
I tried to do this Import sing the Bulk Insert Task, privided by DTS. Within this Task it is possible to define a format file (fmt) in which the wizard writes information about the import-file. Following the wizard it will ask you if the file is fixed len
gth or delimited. I choose delimited, choose the text qualifier and hit the Next-key. Than I choose Comma as field seperator. In the example list the file was correctly shown.
When I executed the Bulk Insert Task it imported the file without errors. In the destination column however you could see that the text qualifiers were ignored. The double-quotes were in the fields and every comma in the file was used as a field seperator
|||Under [Transform Data Task Properties] -> [Options] tab specify [Text
qualifier] as [Double Quote {"}].
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> /
:
news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> Hi Uri,
> I don't think I understand your reply to my question. I will firts try to
better explain what I want, maybe you can than explain how your reply fits
into that.
> My goal is an import from a textfile. This textfile is comma-delimited
with text qualifiers for the char-fields.
> I tried to do this Import sing the Bulk Insert Task, privided by DTS.
Within this Task it is possible to define a format file (fmt) in which the
wizard writes information about the import-file. Following the wizard it
will ask you if the file is fixed length or delimited. I choose delimited,
choose the text qualifier and hit the Next-key. Than I choose Comma as field
seperator. In the example list the file was correctly shown.
> When I executed the Bulk Insert Task it imported the file without errors.
In the destination column however you could see that the text qualifiers
were ignored. The double-quotes were in the fields and every comma in the
file was used as a field seperator.
> Obviously the above is not what I desired. I tested the same in Excel. In
Excel the Import went very well. To me it looks like a bug in SQL-Server.[vbcol=seagreen]
> "Uri Dimant" wrote:
text-field[vbcol=seagreen]
text[vbcol=seagreen]
(The[vbcol=seagreen]
|||Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk Insert Task is supposed to be much faster in loading the data.
As mentioned in my posts I did select the Text qualifier within the Bulk Insert Task.
"Ramon" wrote:

> Under [Transform Data Task Properties] -> [Options] tab specify [Text
> qualifier] as [Double Quote {"}].
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> ó???Yéì/ó???Yéìá ×
> ??×?ó??è óì???àY??:
> news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> better explain what I want, maybe you can than explain how your reply fits
> into that.
> with text qualifiers for the char-fields.
> Within this Task it is possible to define a format file (fmt) in which the
> wizard writes information about the import-file. Following the wizard it
> will ask you if the file is fixed length or delimited. I choose delimited,
> choose the text qualifier and hit the Next-key. Than I choose Comma as field
> seperator. In the example list the file was correctly shown.
> In the destination column however you could see that the text qualifiers
> were ignored. The double-quotes were in the fields and every comma in the
> file was used as a field seperator.
> Excel the Import went very well. To me it looks like a bug in SQL-Server.
> text-field
> text
> (The
>
>
|||Bulk Insert does not support behavior you required (but this is not a bug).
You could use more sophisticated Transform Data for this case.
Personally I load data by BULK INSERT command into the intermediate table
and then load formatted data into the target table.
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> /
:
news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk
Insert Task is supposed to be much faster in loading the data.
> As mentioned in my posts I did select the Text qualifier within the Bulk
Insert Task.[vbcol=seagreen]
> "Ramon" wrote:
to[vbcol=seagreen]
fits[vbcol=seagreen]
the[vbcol=seagreen]
delimited,[vbcol=seagreen]
field[vbcol=seagreen]
errors.[vbcol=seagreen]
the[vbcol=seagreen]
In[vbcol=seagreen]
SQL-Server.[vbcol=seagreen]
with[vbcol=seagreen]
the[vbcol=seagreen]
well.[vbcol=seagreen]
|||You can also achieve your goal with Bulk Insert some tricky way:
* Suppose you have table:
CREATE TABLE [dbo].[Table1] (
[ColA] [varchar] (50) NOT NULL,
[ColB] [varchar] (50) NOT NULL,
[ColC] [varchar] (50) NOT NULL)
* and file to fe imported contents:
"Value11","Value12","Value13"
"Value21","Value22","Value23"
* then your format file contents should be:
7.0
4
1 SQLCHAR 0 50 "\"" 0 ColA
2 SQLCHAR 0 50 "\",\"" 1 ColA
3 SQLCHAR 0 50 "\",\"" 2 ColB
4 SQLCHAR 0 50 "\"\r\n" 3 ColC
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"Ramon" <Nickolaychuk@.Mail.Ru> / :
news:urpv5MZZEHA.2444@.tk2msftngp13.phx.gbl...
> Bulk Insert does not support behavior you required (but this is not a
bug).[vbcol=seagreen]
> You could use more sophisticated Transform Data for this case.
> Personally I load data by BULK INSERT command into the intermediate table
> and then load formatted data into the target table.
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> /
> :
> news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Insert Task is supposed to be much faster in loading the data.
> Insert Task.
try[vbcol=seagreen]
> to
> fits
comma-delimited[vbcol=seagreen]
DTS.[vbcol=seagreen]
> the
it[vbcol=seagreen]
> delimited,
> field
> errors.
qualifiers[vbcol=seagreen]
> the
Excel.
> In
> SQL-Server.
> with
> the
> well.
>

Bulk insert from textfile

Hello,

I'm having the following situation. To exchange data between software-programs we have to use textfiles. These files are like: "2100880000095500400600000329000 00000329000 WNOW0121102B 1121". I have to cut the textfiles into lines of 256 characters. Next thing i have to do is cutting that line in chunks (by a defined structure) and use these chunks to insert them into colums in SQL Server. Example, the line above will become something like: "INSERT INTO table (21, 008800000955, '004006', etc, etc) ".

The code now is: i've opened a connection, and then I'm doing an INSERT per row, but when i have to insert 800.000 rows like this, it's a real bottleneck. Is there any way to perform the INSERTS quicker ?? By a bulk or something ?? Can anyone help me ??

Check out BULK INSERT or the BCP tool in the documentation.