Newbie question on BULK INSERT of text file

A non-SQL application appends messages to a plain text file.
I want to read the rows of this text file into a table.
The text is in a general format and should be considered one column.
The lines are terminated with a typical CR/LF.
I'd like to do more analysis on the text after getting it into a table.
My Problem: BULK INSERT will skip every other line
with the code below. It seems to take the CR/LF as a column
terminator and then skip over the next row to the new CR/LF
to consider the row to be complete.
How do I get rows of plain text from a text file to a table?
Create Table #MyTempFile ( FileLine varchar(250) )
BULK INSERT #MyTempFile
FROM 'MyTextFile.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r',
ROWTERMINATOR = '\n'
)try taking out the field terminator parameter and make the row teminator the
combined "\r\n" instead.
If that fails, It's possible you might have to use "\n\r".
"Don Anthony" wrote:

> A non-SQL application appends messages to a plain text file.
> I want to read the rows of this text file into a table.
> The text is in a general format and should be considered one column.
> The lines are terminated with a typical CR/LF.
> I'd like to do more analysis on the text after getting it into a table.
> My Problem: BULK INSERT will skip every other line
> with the code below. It seems to take the CR/LF as a column
> terminator and then skip over the next row to the new CR/LF
> to consider the row to be complete.
> How do I get rows of plain text from a text file to a table?
> Create Table #MyTempFile ( FileLine varchar(250) )
> BULK INSERT #MyTempFile
> FROM 'MyTextFile.txt'
> WITH
> (
> BATCHSIZE = 50,
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r',
> ROWTERMINATOR = '\n'
> )
>|||When I take out the FIELDTERMINATOR line I get the error show below
(tried various combinations of ROWTERMINATOR but get the same error).
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1.
Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
0x80004005: The provider did not give any information about the error.].
The statement has been terminated.|||You're right. My mistake. But I did get two different versions of bulk
insert ot work
including your original code. Are you sure there are no other stray
caharacters at the end of the lines other than Cr/LF? If you have a text
editor, check the hex display to make sure. Also, is it possible there are
data lines that are more than 250 bytes? Your table definition allows for
varchar(250).
This worked for me:
Create Table #MyTempFile ( FileLine varchar(250) )
BULK INSERT #MyTempFile
FROM 'e:\state_calls\texttest.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
fieldterminator = '\r',
ROWTERMINATOR = '\n'
)
Textest.txt contains for records each with cr/lf line terminator:
1234567890
0987654321
abcdefghij
wxyzabcdef
This version worked too:
BULK INSERT #MyTempFile
FROM 'e:\state_calls\texttest.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
fieldterminator = '\r\n'
)
"Don Anthony" wrote:

> When I take out the FIELDTERMINATOR line I get the error show below
> (tried various combinations of ROWTERMINATOR but get the same error).
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1, column 1
.
> Make sure the field terminator and row terminator are specified correctly.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
> 0x80004005: The provider did not give any information about the error.].
> The statement has been terminated.
>|||Your code works perfectly.
My code wasn't working because it wasn't quite what I originally indicated
(I did say I was a newbie...)
The real table definition was
CREATE TABLE #MyTempTable ( FileLine varchar(250), RowID int IDENTITY(1, 1)
)
not
CREATE TABLE #MyTempTable ( FileLine varchar(250) )
The bulk insert apparently threw away every other line after
failing to fit it into the identity column.
Everything works fine after I take out the extra column.
Thanks for your help.
"tthrone" wrote:sql

Newbie problem with counting rows

This is a refinement of my previous problem. I have the following
table :

C0 | C1 | C2
--+--+--
A | 1 | X
--+--+--
A | 1 | X
--+--+--
A | 2 | X
--+--+--
A | 1 | Y
--+--+--
B | 1 | X
--+--+--
B | 1 | X

I want to write a request which counts the number of different (C0,
C1, C2) where C2 is X. Here, the result should be 3. What would be the
request ? A kind of :

select count(*) from (select distinct C0, C1 from T where C2 = 'X')

but this doesn't work.You almost had it :-)

select count(*) from (select distinct C0, C1 from T where C2 = 'X') AS X

An alias is mandatory for a derived table even if it isn't referenced
anywhere.

--
David Portas
SQL Server MVP
--|||>
> select count(*) from (select distinct C0, C1 from T where C2 = 'X')
> but this doesn't work.

You need to give your table expression a correlation name:

select count(*) from (select distinct C0, C1 from T where C2 = 'X') as t

Christian.

Newbie - Store jpg file in Image Data Type Field

I assume that this is really simple to do once you know how to do it.
I have a very small table, only 5 rows and two columns. Column 0 is
an integer from 1 to 5, and column 1 is supposed to store an image
corresponding to each of the values in column 0. I have the five
different image files in a jpg format. I tried simply copying and
pasting these files into the table, but that didn't work. Can
somebody tell me how to save these jpg files in the table so that I
can use them in my VB.NET application?
Thanks,
RandySQL 2000?
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182898391.523504.187750@.i38g2000prf.googlegroups.com...
>I assume that this is really simple to do once you know how to do it.
> I have a very small table, only 5 rows and two columns. Column 0 is
> an integer from 1 to 5, and column 1 is supposed to store an image
> corresponding to each of the values in column 0. I have the five
> different image files in a jpg format. I tried simply copying and
> pasting these files into the table, but that didn't work. Can
> somebody tell me how to save these jpg files in the table so that I
> can use them in my VB.NET application?
> Thanks,
> Randy
>|||Actually, SQL Server Management Studio Express|||In SQL Server 2005 you can use OPENROWSET with the SINGLE_BLOB option, like
this:
CREATE TABLE Foobar (
image_data VARBINARY(MAX));
INSERT INTO Foobar
(image_data)
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image.jpg',
SINGLE_BLOB)
AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks, but I don't know how to implement this. I'd like to actually
store the files within the datatable rather than referencing them from
a file location. Is that possible? Otherwise, can you explain this
to me in further detail?
Thanks again.
Randy|||That will store them in the database, it is insert script. You do it onces
they are there for access. If you want to do it from .NET code.. I don't
have .NET example, but here is example on how to do it in VB6, maybe you can
convert it.
Private Sub InsertFile()
On Error GoTo ErrorHandler
Dim Index As Long
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
strSQL = "SELECT * FROM TableName"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile FileNameToLoadWithFullPath
rs.AddNew
rs.Fields('FileData').Value = mstream.Read
rs.Update
rs.Close
End Sub
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Randy" wrote:

> Thanks, but I don't know how to implement this. I'd like to actually
> store the files within the datatable rather than referencing them from
> a file location. Is that possible? Otherwise, can you explain this
> to me in further detail?
> Thanks again.
> Randy
>|||You just need to run this as a query in SQL Server Management Studio. It
will actually store the image data into the table, the reference to the file
location is needed just to load the images.
Here is a more detailed example that may fit better your case. The code
below creates a table with keys and then updates the image column for each
key (based on your initial post I assume this is what you want to do).
CREATE TABLE Foobar (
keycol INTEGER,
image_data VARBINARY(MAX));
-- Insert the keys.
INSERT INTO Foobar (keycol)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image1.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 1;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image2.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 2;
-- Continue to load all images...
SELECT keycol, image_data
FROM Foobar;
DROP TABLE Foobar;
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks to both of you. I'm working with Plamen's query and got it to
execute successfully, however I'm still not all the way there. A
couple of questions:
1. I can see that the table was created in the Results pane, but I
don't know where this table is actually stored. Of course, the table
is of no use until I can make it part of my db, and I don't see it
listed among the tables in the db. How can I create this table so
that it is a permanent member of my db?
2. In the Results pane, I see a two column table. Column 1 is called
keycol and col 2 is called image_data, as created by the query. There
are five rows, each of which contains an integer value from 1 to 5 in
keycol, also as created by the query. However, the fields in
image_data are blank, at least as viewed through the Results pane.
I'm not sure if the images have actually loaded correctly. To
clarify, I changed the query language to include tha path names for
each of the 5 images that I am trying to import, so I don't think that
is part of the problem.
Thanks a lot for sticking with me on this. I'm sorry that I am so
clueless, but working direclty in SQL Server is completely new to me.
Randy|||Ignore that last reply. Apparently, I wasn't looking at refreshed
view of the db. The table is there, as are both columns. There was
no data in the table, so I added the key column values and just ran
the UPDATE part of the query, which seems to have populated the
image_data values. Now, i just have to figure out how to pull this
onto my VB form. When I look at the table data, it just says <Binary
Data> in each of the fields, so I'm not certain that I have everything
in place just yet. If I have trouble, I'll re-post.
Thanks for everybody's help!
Randy|||"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182979823.310482.88750@.e16g2000pri.googlegroups.com...
> Thanks to both of you. I'm working with Plamen's query and got it to
> execute successfully, however I'm still not all the way there. A
> couple of questions:
> 1. I can see that the table was created in the Results pane, but I
> don't know where this table is actually stored. Of course, the table
> is of no use until I can make it part of my db, and I don't see it
> listed among the tables in the db. How can I create this table so
> that it is a permanent member of my db?
If you just copied my sample query, then at the end of it there is a DROP
TABLE statement. You can comment it out or remove it and then run again to
keep the table. This is the line you need to comment out or remove:
--DROP TABLE Foobar;

> 2. In the Results pane, I see a two column table. Column 1 is called
> keycol and col 2 is called image_data, as created by the query. There
> are five rows, each of which contains an integer value from 1 to 5 in
> keycol, also as created by the query. However, the fields in
> image_data are blank, at least as viewed through the Results pane.
> I'm not sure if the images have actually loaded correctly. To
> clarify, I changed the query language to include tha path names for
> each of the 5 images that I am trying to import, so I don't think that
> is part of the problem.
>
You cannot see the image in the result pane, but rather the binary
representation. If you just right click the table and select Open Table, you
should see something like <Binary data> in the image_data column. If you run
the query and look in Result, you should see something like 0xFFD8... If you
see NULL, then the images were not uploaded successfully. I would suggest to
check the path for the files and if the file names are correct.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Newbie - Store jpg file in Image Data Type Field

I assume that this is really simple to do once you know how to do it.
I have a very small table, only 5 rows and two columns. Column 0 is
an integer from 1 to 5, and column 1 is supposed to store an image
corresponding to each of the values in column 0. I have the five
different image files in a jpg format. I tried simply copying and
pasting these files into the table, but that didn't work. Can
somebody tell me how to save these jpg files in the table so that I
can use them in my VB.NET application?
Thanks,
Randy
SQL 2000?
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182898391.523504.187750@.i38g2000prf.googlegr oups.com...
>I assume that this is really simple to do once you know how to do it.
> I have a very small table, only 5 rows and two columns. Column 0 is
> an integer from 1 to 5, and column 1 is supposed to store an image
> corresponding to each of the values in column 0. I have the five
> different image files in a jpg format. I tried simply copying and
> pasting these files into the table, but that didn't work. Can
> somebody tell me how to save these jpg files in the table so that I
> can use them in my VB.NET application?
> Thanks,
> Randy
>
|||Actually, SQL Server Management Studio Express
|||In SQL Server 2005 you can use OPENROWSET with the SINGLE_BLOB option, like
this:
CREATE TABLE Foobar (
image_data VARBINARY(MAX));
INSERT INTO Foobar
(image_data)
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image.jpg',
SINGLE_BLOB)
AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Thanks, but I don't know how to implement this. I'd like to actually
store the files within the datatable rather than referencing them from
a file location. Is that possible? Otherwise, can you explain this
to me in further detail?
Thanks again.
Randy
|||That will store them in the database, it is insert script. You do it onces
they are there for access. If you want to do it from .NET code.. I don't
have .NET example, but here is example on how to do it in VB6, maybe you can
convert it.
Private Sub InsertFile()
On Error GoTo ErrorHandler
Dim Index As Long
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
strSQL = "SELECT * FROM TableName"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile FileNameToLoadWithFullPath
rs.AddNew
rs.Fields('FileData').Value = mstream.Read
rs.Update
rs.Close
End Sub
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Randy" wrote:

> Thanks, but I don't know how to implement this. I'd like to actually
> store the files within the datatable rather than referencing them from
> a file location. Is that possible? Otherwise, can you explain this
> to me in further detail?
> Thanks again.
> Randy
>
|||You just need to run this as a query in SQL Server Management Studio. It
will actually store the image data into the table, the reference to the file
location is needed just to load the images.
Here is a more detailed example that may fit better your case. The code
below creates a table with keys and then updates the image column for each
key (based on your initial post I assume this is what you want to do).
CREATE TABLE Foobar (
keycol INTEGER,
image_data VARBINARY(MAX));
-- Insert the keys.
INSERT INTO Foobar (keycol)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image1.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 1;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image2.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 2;
-- Continue to load all images...
SELECT keycol, image_data
FROM Foobar;
DROP TABLE Foobar;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Thanks to both of you. I'm working with Plamen's query and got it to
execute successfully, however I'm still not all the way there. A
couple of questions:
1. I can see that the table was created in the Results pane, but I
don't know where this table is actually stored. Of course, the table
is of no use until I can make it part of my db, and I don't see it
listed among the tables in the db. How can I create this table so
that it is a permanent member of my db?
2. In the Results pane, I see a two column table. Column 1 is called
keycol and col 2 is called image_data, as created by the query. There
are five rows, each of which contains an integer value from 1 to 5 in
keycol, also as created by the query. However, the fields in
image_data are blank, at least as viewed through the Results pane.
I'm not sure if the images have actually loaded correctly. To
clarify, I changed the query language to include tha path names for
each of the 5 images that I am trying to import, so I don't think that
is part of the problem.
Thanks a lot for sticking with me on this. I'm sorry that I am so
clueless, but working direclty in SQL Server is completely new to me.
Randy
|||Ignore that last reply. Apparently, I wasn't looking at refreshed
view of the db. The table is there, as are both columns. There was
no data in the table, so I added the key column values and just ran
the UPDATE part of the query, which seems to have populated the
image_data values. Now, i just have to figure out how to pull this
onto my VB form. When I look at the table data, it just says <Binary
Data> in each of the fields, so I'm not certain that I have everything
in place just yet. If I have trouble, I'll re-post.
Thanks for everybody's help!
Randy
|||"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182979823.310482.88750@.e16g2000pri.googlegro ups.com...
> Thanks to both of you. I'm working with Plamen's query and got it to
> execute successfully, however I'm still not all the way there. A
> couple of questions:
> 1. I can see that the table was created in the Results pane, but I
> don't know where this table is actually stored. Of course, the table
> is of no use until I can make it part of my db, and I don't see it
> listed among the tables in the db. How can I create this table so
> that it is a permanent member of my db?
If you just copied my sample query, then at the end of it there is a DROP
TABLE statement. You can comment it out or remove it and then run again to
keep the table. This is the line you need to comment out or remove:
--DROP TABLE Foobar;

> 2. In the Results pane, I see a two column table. Column 1 is called
> keycol and col 2 is called image_data, as created by the query. There
> are five rows, each of which contains an integer value from 1 to 5 in
> keycol, also as created by the query. However, the fields in
> image_data are blank, at least as viewed through the Results pane.
> I'm not sure if the images have actually loaded correctly. To
> clarify, I changed the query language to include tha path names for
> each of the 5 images that I am trying to import, so I don't think that
> is part of the problem.
>
You cannot see the image in the result pane, but rather the binary
representation. If you just right click the table and select Open Table, you
should see something like <Binary data> in the image_data column. If you run
the query and look in Result, you should see something like 0xFFD8... If you
see NULL, then the images were not uploaded successfully. I would suggest to
check the path for the files and if the file names are correct.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Newbie - Store jpg file in Image Data Type Field

I assume that this is really simple to do once you know how to do it.
I have a very small table, only 5 rows and two columns. Column 0 is
an integer from 1 to 5, and column 1 is supposed to store an image
corresponding to each of the values in column 0. I have the five
different image files in a jpg format. I tried simply copying and
pasting these files into the table, but that didn't work. Can
somebody tell me how to save these jpg files in the table so that I
can use them in my VB.NET application?
Thanks,
RandySQL 2000?
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182898391.523504.187750@.i38g2000prf.googlegroups.com...
>I assume that this is really simple to do once you know how to do it.
> I have a very small table, only 5 rows and two columns. Column 0 is
> an integer from 1 to 5, and column 1 is supposed to store an image
> corresponding to each of the values in column 0. I have the five
> different image files in a jpg format. I tried simply copying and
> pasting these files into the table, but that didn't work. Can
> somebody tell me how to save these jpg files in the table so that I
> can use them in my VB.NET application?
> Thanks,
> Randy
>|||Actually, SQL Server Management Studio Express|||In SQL Server 2005 you can use OPENROWSET with the SINGLE_BLOB option, like
this:
CREATE TABLE Foobar (
image_data VARBINARY(MAX));
INSERT INTO Foobar
(image_data)
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image.jpg',
SINGLE_BLOB)
AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks, but I don't know how to implement this. I'd like to actually
store the files within the datatable rather than referencing them from
a file location. Is that possible? Otherwise, can you explain this
to me in further detail?
Thanks again.
Randy|||That will store them in the database, it is insert script. You do it onces
they are there for access. If you want to do it from .NET code.. I don't
have .NET example, but here is example on how to do it in VB6, maybe you can
convert it.
Private Sub InsertFile()
On Error GoTo ErrorHandler
Dim Index As Long
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
strSQL = "SELECT * FROM TableName"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile FileNameToLoadWithFullPath
rs.AddNew
rs.Fields('FileData').Value = mstream.Read
rs.Update
rs.Close
End Sub
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Randy" wrote:
> Thanks, but I don't know how to implement this. I'd like to actually
> store the files within the datatable rather than referencing them from
> a file location. Is that possible? Otherwise, can you explain this
> to me in further detail?
> Thanks again.
> Randy
>|||You just need to run this as a query in SQL Server Management Studio. It
will actually store the image data into the table, the reference to the file
location is needed just to load the images.
Here is a more detailed example that may fit better your case. The code
below creates a table with keys and then updates the image column for each
key (based on your initial post I assume this is what you want to do).
CREATE TABLE Foobar (
keycol INTEGER,
image_data VARBINARY(MAX));
-- Insert the keys.
INSERT INTO Foobar (keycol)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image1.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 1;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image2.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 2;
-- Continue to load all images...
SELECT keycol, image_data
FROM Foobar;
DROP TABLE Foobar;
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks to both of you. I'm working with Plamen's query and got it to
execute successfully, however I'm still not all the way there. A
couple of questions:
1. I can see that the table was created in the Results pane, but I
don't know where this table is actually stored. Of course, the table
is of no use until I can make it part of my db, and I don't see it
listed among the tables in the db. How can I create this table so
that it is a permanent member of my db?
2. In the Results pane, I see a two column table. Column 1 is called
keycol and col 2 is called image_data, as created by the query. There
are five rows, each of which contains an integer value from 1 to 5 in
keycol, also as created by the query. However, the fields in
image_data are blank, at least as viewed through the Results pane.
I'm not sure if the images have actually loaded correctly. To
clarify, I changed the query language to include tha path names for
each of the 5 images that I am trying to import, so I don't think that
is part of the problem.
Thanks a lot for sticking with me on this. I'm sorry that I am so
clueless, but working direclty in SQL Server is completely new to me.
Randy|||Ignore that last reply. Apparently, I wasn't looking at refreshed
view of the db. The table is there, as are both columns. There was
no data in the table, so I added the key column values and just ran
the UPDATE part of the query, which seems to have populated the
image_data values. Now, i just have to figure out how to pull this
onto my VB form. When I look at the table data, it just says <Binary
Data> in each of the fields, so I'm not certain that I have everything
in place just yet. If I have trouble, I'll re-post.
Thanks for everybody's help!
Randy|||"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182979823.310482.88750@.e16g2000pri.googlegroups.com...
> Thanks to both of you. I'm working with Plamen's query and got it to
> execute successfully, however I'm still not all the way there. A
> couple of questions:
> 1. I can see that the table was created in the Results pane, but I
> don't know where this table is actually stored. Of course, the table
> is of no use until I can make it part of my db, and I don't see it
> listed among the tables in the db. How can I create this table so
> that it is a permanent member of my db?
If you just copied my sample query, then at the end of it there is a DROP
TABLE statement. You can comment it out or remove it and then run again to
keep the table. This is the line you need to comment out or remove:
--DROP TABLE Foobar;
> 2. In the Results pane, I see a two column table. Column 1 is called
> keycol and col 2 is called image_data, as created by the query. There
> are five rows, each of which contains an integer value from 1 to 5 in
> keycol, also as created by the query. However, the fields in
> image_data are blank, at least as viewed through the Results pane.
> I'm not sure if the images have actually loaded correctly. To
> clarify, I changed the query language to include tha path names for
> each of the 5 images that I am trying to import, so I don't think that
> is part of the problem.
>
You cannot see the image in the result pane, but rather the binary
representation. If you just right click the table and select Open Table, you
should see something like <Binary data> in the image_data column. If you run
the query and look in Result, you should see something like 0xFFD8... If you
see NULL, then the images were not uploaded successfully. I would suggest to
check the path for the files and if the file names are correct.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||VB.NET you should be able to grab the binary data from the database, store
it in a Byte() array and create a Graphics object from it. I'm doing
something similar right now in VB 2005 with dynamically generated images
being passed from SQL Server to a client-side VB app where the binary
content is converted to a bitmap and displayed on a form. Just be sure to
properly dispose of your Graphics objects, etc., when you're done with them.
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182982110.182760.129310@.e16g2000pri.googlegroups.com...
> Ignore that last reply. Apparently, I wasn't looking at refreshed
> view of the db. The table is there, as are both columns. There was
> no data in the table, so I added the key column values and just ran
> the UPDATE part of the query, which seems to have populated the
> image_data values. Now, i just have to figure out how to pull this
> onto my VB form. When I look at the table data, it just says <Binary
> Data> in each of the fields, so I'm not certain that I have everything
> in place just yet. If I have trouble, I'll re-post.
> Thanks for everybody's help!
> Randy
>|||I need to do the exact same thing but in SQL Server 2000.
I have been tinkering with BULK INSERT and OPENROWSET but have not been able
to get anything to load the image data.
Actually in my case, the image data can be a Word document, Excel
spreadsheet, PDF file, text file, etc.
Thank you in advance for any information that you can provide.
Joe|||Hi Joe,
The BULK rowset provider functionality to load BLOBs is only available since
SQL Server 2005.
See the following example by Erland Sommarskog on how this can be done is
SQL Server 2000:
http://www.sommarskog.se/blobload.txt
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Newbie - How to update multiple rows with select statment

Newbie to the SQL Server.

UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)

Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.

Immd help will be greatly appreciated.

PamI am disappointed that all the experts here have no time for my basic question ...|||you need to write a query like

update a
set a.col = b.col
from yourtbl a, (your select query) b
where a.primarykey = b.primarykey|||Thanks for the reply.

Thats too basic. Are you suggesting me to create new table and then associate the values as you quoted above ??|||enigma just enjoys giving generic syntax which might solve your problem and letting you fit your problem to the generic syntax

in this case, enigma, i think you need to actually try it yourself, and see whether your solution fits the stated problem

here's my solution (note: i tested it, including all alpha and all numeric column values) --update GOLDIE
set GOLDIE_ID =
case when patindex('%[A-Z,a-z]%',GOLDIE_ID) > 1
then left(GOLDIE_ID
,patindex('%[A-Z,a-z]%',GOLDIE_ID)-1)
else 0 end
where patindex('%[A-Z,a-z]%',GOLDIE_ID) > 0|||I'd suggest:DROP TABLE Pam24
GO

CREATE TABLE Pam24 (
Pam24id INT IDENTITY
CONSTRAINT XPKPam24
PRIMARY KEY (Pam24id)
, thingie VARCHAR(50) NULL
)

INSERT INTO Pam24 (thingie)
SELECT '123 Main Street' UNION ALL
SELECT NULL UNION ALL
SELECT '456Any Road' UNION ALL
SELECT '' UNION ALL
SELECT '789 My Place'

UPDATE Pam24
SET thingie = Left(thingie, PatIndex('%[^0-9]%', thingie) - 1)
WHERE thingie LIKE '[0-9]%'

SELECT *
FROM Pam24Note that the two patterns are different, in fact exact opposites. I dislike having two patterns, but it was better than any alternative I could think of on short notice.

-PatP|||pat, nice try, but if the string does not start with a number, the original spec (yes, i realize it's buried inside some non-functional sql) required that you reset the entire value to 0

:)|||actually, now that i look at it more closely, the 0 was actually the length parameter of the substring function, so i think maybe it's supposed to reset all alpha-only strings to empty strings|||enigma just enjoys giving generic syntax which might solve your problem and letting you fit your problem to the generic syntax

in this case, enigma, i think you need to actually try it yourself, and see whether your solution fits the stated problem

here's my solution (note: i tested it, including all alpha and all numeric column values) --update GOLDIE
set GOLDIE_ID =
case when patindex('%[A-Z,a-z]%',GOLDIE_ID) > 1
then left(GOLDIE_ID
,patindex('%[A-Z,a-z]%',GOLDIE_ID)-1)
else 0 end
where patindex('%[A-Z,a-z]%',GOLDIE_ID) > 0

Thanks r937. It does answers my question.

Cheers

newbie - Display rows with identical values

Hello,
how can i retrieve duplicate records?
I have this table with about 6500 records, and i know that there are a few
where a combination (Column1 - Column2) is identical
how can i retrieve these?SELECT Column1, Column2
FROM YourTable
GROUP BY Column1 - Column2
HAVING COUNT(*) > 1
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"benoit" <benoit@.discussions.microsoft.com> wrote in message
news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||hi benoit,
Just a question, your table own a indentity field?
If so, use this:
delete from table1
where <identityfield> not in
(select max(<identityfiedl> ) from table1
group by [<field1>,<field2>]
Otherwise, let me know or post DDL
regards,
"benoit" wrote:

> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||i was thinking the same way as roji
use northwind
select lastname,firstname into x from employees
union all
select top 5 lastname,firstname from employees
go
select lastname, firstname from x
group by lastname,firstname
having count(*)>1
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"benoit" wrote:

> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||works great !
thx
"Roji. P. Thomas" wrote:

> SELECT Column1, Column2
> FROM YourTable
> GROUP BY Column1 - Column2
> HAVING COUNT(*) > 1
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "benoit" <benoit@.discussions.microsoft.com> wrote in message
> news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
>
>|||Hi
This article had written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"benoit" <benoit@.discussions.microsoft.com> wrote in message
news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>

Newbie - Changing ordered list

Hi,

I would like to programmatically move rows up or down an ordered list. I
have created a column to ORDER BY and filled with ascending integer values.
Swapping the column values with the row above or the row below changes the
row position OK but things start getting complicated when rows are deleted
or new rows are added (e.g. what column value to assign to the new row). Is
there a simple way of doing this?

ThanksJackT (turnbull.jack@.ntlworld.com) writes:
> I would like to programmatically move rows up or down an ordered list. I
> have created a column to ORDER BY and filled with ascending integer
> values. Swapping the column values with the row above or the row below
> changes the row position OK but things start getting complicated when
> rows are deleted or new rows are added (e.g. what column value to assign
> to the new row). Is there a simple way of doing this?

I am sorry, but you need to explain a lot more of what you are doing.
Are you moving rows in a table, or are they rows in a screen form?

Generally, for many types of question in this newsgroup it is a good
idea to include:

o CREATE TABLE statements for the involved tables.
o INSERT statements with sample data.
o The desired result with the sample data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

CREATE TABLE [CategoryList] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryIndex] [int] NULL ,
[CategoryName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('First',0)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Second',1)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Third',2)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Fourth',3)
GO
SELECT * FROM [CategoryList] ORDER BY CategoryIndex
GO

Gives Output

1 0 First
2 1 Second
3 2 Third
4 3 Fourth

UPDATE [CategoryList]
SET [CategoryIndex]=2 WHERE [CategoryID] = 2
UPDATE [CategoryList]
SET [CategoryIndex]=1 WHERE [CategoryID] = 3
SELECT * FROM dbo.CategoryList ORDER BY CategoryIndex
GO

Gives Output

1 0 First
3 1 Third
2 2 Second
4 4 Fourth

Is this the best method of setting up a list so you can swap the ordering
programmatically?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns953BACDF25089Yazorman@.127.0.0.1...
> JackT (turnbull.jack@.ntlworld.com) writes:
> > I would like to programmatically move rows up or down an ordered list. I
> > have created a column to ORDER BY and filled with ascending integer
> > values. Swapping the column values with the row above or the row below
> > changes the row position OK but things start getting complicated when
> > rows are deleted or new rows are added (e.g. what column value to assign
> > to the new row). Is there a simple way of doing this?
> I am sorry, but you need to explain a lot more of what you are doing.
> Are you moving rows in a table, or are they rows in a screen form?
> Generally, for many types of question in this newsgroup it is a good
> idea to include:
> o CREATE TABLE statements for the involved tables.
> o INSERT statements with sample data.
> o The desired result with the sample data.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||JackT (turnbull.jack@.ntlworld.com) writes:
> UPDATE [CategoryList]
> SET [CategoryIndex]=2 WHERE [CategoryID] = 2
> UPDATE [CategoryList]
> SET [CategoryIndex]=1 WHERE [CategoryID] = 3
> SELECT * FROM dbo.CategoryList ORDER BY CategoryIndex
> GO
> Gives Output
> 1 0 First
> 3 1 Third
> 2 2 Second
> 4 4 Fourth
> Is this the best method of setting up a list so you can swap the ordering
> programmatically?

OK, so what you basically after is a sorter value who tells you in
which order to present the data?

I can't think of any radically different way of doing this, although
some varitions are possible, for instance using 100, 200 etc as values
initially. On the other hand, having a contiguous series, can actually
make it easier to maintain the list.

Insert a value at point n:

BEGIN TRANSACTION

UPDATE CategoryList SET CategoryIndex = CategoryIndex + 1
WHERE CategoryIndex >= @.Indexfornew

INSERT CategoryList(CategoryName, CategoryIndex)
VALUES (@.newname, @.Indexfornew)

COMMIT TRANSACTION

Delete an entry:

BEGIN TRANSACTION

SELECT @.indexforold = CategoryIndex FROM CategoryList
WHERE CategoryID = @.idtodelete

DELETE CategoryIndex FROM CategoryList WHERE CategoryID = @.idtodelete

UPDATE CategoryList SET CategoryIndex = CategoryIndex - 1
WHERE CategoryIndex > @.indexforold

COMMIT TRANSACTION

Move an entry to position @.n:

BEGIN TRANSACTION

SELECT @.currentindex = CategoryIndex FROM CategoryList
WHERE CategoryID = @.idtomove

UPDATE CategoryIndex SET CategoryIndex = 10000000
WHERE CategoryID = @.idtomove

IF @.n > @.currentindex
BEGIN
UPDATE CategoryList SET CategoryIndex = CategoryIndex - 1
WHERE CategoryIndex > @.currentindex AND CategoryIndex <= @.n
END
ELSE
BEGIN
UPDATE CategoryList SET CategoryIndex = CategoryIndex + 1
WHERE CategoryIndex < @.currentindex AND CategoryIndex >= @.n
END

UPDATE CategoryIndex SET CategoryIndex = 10000000
WHERE CategoryID = @.n

COMMIT TRANSACTION

Here I have assumed that you don't to have ties, and thus a UNIQUE
constraint on the index column is a good idea.

All the above is untested - you should have some fun too! :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

This looks pretty much what I'm after. I'll try it out ASAP and tune as
required. Grateful thanks.

Jack

Newbee needs help

Hi,
I have the following statement:
select
dbo.udfCustomerName(CustomerID)
, Count(*)
from
Orders
group by
CustomerID
If Orders table has 1 million rows, does udfCustomerName get executed 1
million times?
Or does SQL server do the grouping in CustomerID first then call udf
function?
How can I be sure? I tried to use Print @.CustomerID inside udfCustomerName,
but SQL server rejected.
TIAIt does the grouping first. You can check the query plan and see that the
function is activated in the last step.
Adi
"Raymond Du" <rdrd@.yahoo.com> wrote in message
news:%23WnqpdKdGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have the following statement:
> select
> dbo.udfCustomerName(CustomerID)
> , Count(*)
> from
> Orders
> group by
> CustomerID
> If Orders table has 1 million rows, does udfCustomerName get executed 1
> million times?
> Or does SQL server do the grouping in CustomerID first then call udf
> function?
> How can I be sure? I tried to use Print @.CustomerID inside
> udfCustomerName, but SQL server rejected.
> TIA
>