How to make the order by date fine enough in SQL

hi,

I was pulling up a report in SQL, and I wanted the records to be ordered by dates descending. However, I found this ordering was only fine enough to order records by dates (not hours or minutes) (within the same date, records were ordered so that the latest entered were at the bottom). I wonder if anyone else has encouted this problem before, or I am doing something wrong.

Thanks very much.

Then you're trying to do a select order by datedesc, and group by date, within each group you wanna order by timeasc, right? If so you’re trying to do sorting within grouped records. In SQL2005 you can useRANK Functions, for example:

SELECT*,RANK()OVER(PARTITIONBYCONVERT(VARCHAR,CDATE,102)

ORDERBYCONVERT(VARCHAR,CDATE,108))AS RANK

FROM testP

ORDERBYCONVERT(VARCHAR,CDATE,102)DESC

|||Thanks!

How to make OrderBy by Parameters

Hello All,
I need to make a select in a table, but i need to do a order by by
parameter.
Exemple:
SELECT * FROM CUSTOMERS
ORDER BY @.COLUMN
This select is a stored procedure.
@.Column is the name of the column in my table.
@.Column i need to send by parameter.
I need to make a dynamic sort, but i don't can to use EXEC(''), because of
SqlInject.
Somebody to know how to make this task?
Thanks.
Dexter"Dexter" <projenet@.yahoo.com.br> schrieb im Newsbeitrag
news:uPgavPERFHA.248@.TK2MSFTNGP15.phx.gbl...
> Hello All,
> I need to make a select in a table, but i need to do a order by by
> parameter.
> Exemple:
> SELECT * FROM CUSTOMERS
> ORDER BY @.COLUMN
> This select is a stored procedure.
> @.Column is the name of the column in my table.
> @.Column i need to send by parameter.
> I need to make a dynamic sort, but i don't can to use EXEC(''), because of
> SqlInject.
> Somebody to know how to make this task?
> Thanks.
> Dexter
>|||DOnt think there is prober way to do this without dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Dexter" <projenet@.yahoo.com.br> schrieb im Newsbeitrag
news:uPgavPERFHA.248@.TK2MSFTNGP15.phx.gbl...
> Hello All,
> I need to make a select in a table, but i need to do a order by by
> parameter.
> Exemple:
> SELECT * FROM CUSTOMERS
> ORDER BY @.COLUMN
> This select is a stored procedure.
> @.Column is the name of the column in my table.
> @.Column i need to send by parameter.
> I need to make a dynamic sort, but i don't can to use EXEC(''), because of
> SqlInject.
> Somebody to know how to make this task?
> Thanks.
> Dexter
>|||http://www.4guysfromrolla.com/webtech/010704-1.shtml
"Dexter" <projenet@.yahoo.com.br> wrote in message
news:uPgavPERFHA.248@.TK2MSFTNGP15.phx.gbl...
> Hello All,
> I need to make a select in a table, but i need to do a order by by
> parameter.
> Exemple:
> SELECT * FROM CUSTOMERS
> ORDER BY @.COLUMN
> This select is a stored procedure.
> @.Column is the name of the column in my table.
> @.Column i need to send by parameter.
> I need to make a dynamic sort, but i don't can to use EXEC(''), because of
> SqlInject.
> Somebody to know how to make this task?
> Thanks.
> Dexter
>|||How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"Dexter" wrote:

> Hello All,
> I need to make a select in a table, but i need to do a order by by
> parameter.
> Exemple:
> SELECT * FROM CUSTOMERS
> ORDER BY @.COLUMN
> This select is a stored procedure.
> @.Column is the name of the column in my table.
> @.Column i need to send by parameter.
> I need to make a dynamic sort, but i don't can to use EXEC(''), because of
> SqlInject.
> Somebody to know how to make this task?
> Thanks.
> Dexter
>
>sql

How to make a random order query ?

If we use: select * from .... , normally, it will return an ordered result ( may be order by ID ), but how can we make an random order select statement. It mean every time we run the query, the result will be different from the other ?Hi,

FYI:http://www.datawebcontrols.com/faqs/Data/ReturningDataInRandomOrder.shtml

Regards,

How to make 2 columns in datagrid with random records?

Hi
I want to make random record from both columns
Not just like this
"SELECT Firstname,Lasename FROM rndnames ORDER BY NewID()"
but more like this but this code dont work becouse i dont know how to put it into the code:
"SELECT Firstname FROM rndnames ORDER BY NewID()"
"SELECT Lasename FROM rndnames ORDER BY NewID()"
As you see i want both columns to be random placed..
Please help me...
Well i found one way that was 2 tables the code ended like this
SqlConnection1.Open()
Dim sqlcon As NewSqlCommand("Select Firstname,spillernavn From rndnames CROSS JOINspillere ORDER BY NewID()", SqlConnection1)
Dim sqlrd As SqlDataReader
sqlrd = sqlcon.ExecuteReader(CommandBehavior.CloseConnection)
DataGrid1.DataSource = sqlrd
DataGrid1.DataBind()
Well Well at least it works;)

How to load a Unicode file into the database in the same order as the file order

The data file is a simple Unicode file with lines of text. BCP
apparently doesn't guarantee this ordering, and neither does the
import tool. I want to be able to load the data either sequentially or
add line numbering to large Unicode file (1 million lines). I don't
want to deal with another programming language if possible and I
wonder if there's a trick in SQL Server to get this accomplished.
Thanks for any help.
Mark Leary
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
no-email wrote:
> The data file is a simple Unicode file with lines of text. BCP
> apparently doesn't guarantee this ordering, and neither does the
> import tool. I want to be able to load the data either sequentially or
> add line numbering to large Unicode file (1 million lines). I don't
> want to deal with another programming language if possible and I
> wonder if there's a trick in SQL Server to get this accomplished.
> Thanks for any help.
> Mark Leary
>
Why does the order of the rows inserted into the table matter in your
case? Relational databases don't understand row order. If you need them
sorted in some way after the import, you can create a clustered index on
the table to get the rows ordered in a way that helps your queries
perform better.
In general, I think BCP processes the rows in the file sequentially. But
again, I'm not clear on why this matters.
Could you elaborate on the exact issue you are trying to avoid.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> Why does the order of the rows inserted into the table matter in your
> case? Relational databases don't understand row order. If you need them
> sorted in some way after the import, you can create a clustered index on
> the table to get the rows ordered in a way that helps your queries perform
> better.
> In general, I think BCP processes the rows in the file sequentially. But
> again, I'm not clear on why this matters.
> Could you elaborate on the exact issue you are trying to avoid.
I am trying to load a text file sequentially in order to perform text
manipulations using T-SQL that do depend on the exact order. I would be
happy with simply adding a line number to each line of the Unicode text
file, and then loading the file with line number determining the order, but
I want to avoid programming in another language if possible. Eventually the
loaded text would be converted to proper relational tables. This doesn't
have to do with improving performance. Does this help?
Thanks.
|||no-email wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote:
>
> I am trying to load a text file sequentially in order to perform text
> manipulations using T-SQL that do depend on the exact order. I would
> be happy with simply adding a line number to each line of the Unicode
> text file, and then loading the file with line number determining the
> order, but I want to avoid programming in another language if
> possible. Eventually the loaded text would be converted to proper
> relational tables. This doesn't have to do with improving
> performance. Does this help?
> Thanks.
Yes. It sounds like you have rows in a specific order that will need to
be processed once on SQL Server. You want to preserve the order of rows
in the file so the rows can be processed in the same order once on SQL
Server.
In order to do this in any relational database, you need a sort key.
There is never a guarantee that a query you run without an ORDER BY will
return rows in the same order in any consistent way.
My understanding is that BCP feeds the rows in the order they appear in
a file. I can't imagine any reason it would or could do it differently.
In that case, you want to insert the data into a table that contains an
IDENTITY column. You can then use that key for your ORDER BY when
processing the rows from whatever process does that.
David Gugick
Imceda Software
www.imceda.com
|||Do you know what order the source file is sorted in? If so, and if the
sort order column(s) are included then you may not need to know the
line number since it is (theoretically anyway) possible to derive that
information from the other data.
If not, then this article has a useful suggestion:
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
not sure if that will work with unicode data though.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message:

> Do you know what order the source file is sorted in? If so, and if
the
> sort order column(s) are included then you may not need to know the
> line number since it is (theoretically anyway) possible to derive
that
> information from the other data.
Unfortunately the data file consists of simple lines of text with no
other way to extract potential column information before loading it
into the database.

> If not, then this article has a useful suggestion:
>
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
> not sure if that will work with unicode data though.
Good suggestion but it does fail with Unicode. Thanks anyway.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> My understanding is that BCP feeds the rows in the order they appear
in
> a file. I can't imagine any reason it would or could do it
differently.
> In that case, you want to insert the data into a table that contains
an
> IDENTITY column. You can then use that key for your ORDER BY when
> processing the rows from whatever process does that.
In general BCP loads the data in the same order as the file but not
always. The ordering sometimes reverses for thousands of rows, or
skips certain rows, but you need to check it carefully to find the
misordering. You can create a table with an identity column and load
the data, but again if the rows are not loaded in the same sequence as
the file this won't matter. You will end up with an ordered table that
is unfortunately not in the same order as the original file.
To be honest I have tried all these suggestions in the past. My
typical solution would be to open the original file in Excel, add a
rownumber column and then save the resulting file as a Unicode file.
This works up until around a maximum of 63,000 rows. You can break a
file into 63,000 row subfiles, but this would be too tedious if you
have row counts approaching a million.
Thanks for the suggestions but I may have to learn some C#.
Unfortunately Visual Basic has problems with Unicode, as I suspect
also C++ and C have similar problems.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||What about using the CTS Import Wizard to import the data from the flat
file into a table with an identity.
Where is this data coming from? Is there any way to recreate it with a
counter column included in the output?
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> What about using the CTS Import Wizard to import the data from the
flat
> file into a table with an identity.
It's the same problem. The table order generally follows the order in
the file but not always.

> Where is this data coming from? Is there any way to recreate it with
a
> counter column included in the output?
It's foreign language dictionary data that cannot be recreated. I
could manipulate the data on the file level but I am trying to avoid
potential problems with Unicode. Once the data gets into SQL Server I
don't have any problems, as long as the table order exactly matches
the file order.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||no-email wrote:
> It's foreign language dictionary data that cannot be recreated. I
> could manipulate the data on the file level but I am trying to avoid
> potential problems with Unicode. Once the data gets into SQL Server I
> don't have any problems, as long as the table order exactly matches
> the file order.
I'm not sure what problems you would have as long as the tool you are
using to edit the data is unicode aware. I use TextEdit for editing
(www.textpad.com) and it has simple replacement expressions.
Assuming you had each row of data on a single line, you could simply do
the following:
1- Add a leading CARRIAGE RETURN to the file
2- Open the Replace dialog
3- Check the Regular Expression option
4- Type "\n" - WITHOUT QUOTES in the Find What entry- means New Line
character
5- Type "\n\i\t" - WITHOUT QUOTES in the Replace With entry - means New
Line + Auto Number + TAB
6- Click Replace All
7 - Remove the leading carriage return in the file
8 - Click FILE SAVE AS and make sure the UNICODE option is selected
You can replace the TAB character with whatever your file requires or
add DOUBLE QUOTES around the Auto Number, etc.
You can download a free trial of TextPad on the web site.
David Gugick
Imceda Software
www.imceda.com

How to load a Unicode file into the database in the same order as the file order

The data file is a simple Unicode file with lines of text. BCP
apparently doesn't guarantee this ordering, and neither does the
import tool. I want to be able to load the data either sequentially or
add line numbering to large Unicode file (1 million lines). I don't
want to deal with another programming language if possible and I
wonder if there's a trick in SQL Server to get this accomplished.
Thanks for any help.
Mark Leary
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
no-email wrote:
> The data file is a simple Unicode file with lines of text. BCP
> apparently doesn't guarantee this ordering, and neither does the
> import tool. I want to be able to load the data either sequentially or
> add line numbering to large Unicode file (1 million lines). I don't
> want to deal with another programming language if possible and I
> wonder if there's a trick in SQL Server to get this accomplished.
> Thanks for any help.
> Mark Leary
>
Why does the order of the rows inserted into the table matter in your
case? Relational databases don't understand row order. If you need them
sorted in some way after the import, you can create a clustered index on
the table to get the rows ordered in a way that helps your queries
perform better.
In general, I think BCP processes the rows in the file sequentially. But
again, I'm not clear on why this matters.
Could you elaborate on the exact issue you are trying to avoid.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> Why does the order of the rows inserted into the table matter in your
> case? Relational databases don't understand row order. If you need them
> sorted in some way after the import, you can create a clustered index on
> the table to get the rows ordered in a way that helps your queries perform
> better.
> In general, I think BCP processes the rows in the file sequentially. But
> again, I'm not clear on why this matters.
> Could you elaborate on the exact issue you are trying to avoid.
I am trying to load a text file sequentially in order to perform text
manipulations using T-SQL that do depend on the exact order. I would be
happy with simply adding a line number to each line of the Unicode text
file, and then loading the file with line number determining the order, but
I want to avoid programming in another language if possible. Eventually the
loaded text would be converted to proper relational tables. This doesn't
have to do with improving performance. Does this help?
Thanks.
|||no-email wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote:
>
> I am trying to load a text file sequentially in order to perform text
> manipulations using T-SQL that do depend on the exact order. I would
> be happy with simply adding a line number to each line of the Unicode
> text file, and then loading the file with line number determining the
> order, but I want to avoid programming in another language if
> possible. Eventually the loaded text would be converted to proper
> relational tables. This doesn't have to do with improving
> performance. Does this help?
> Thanks.
Yes. It sounds like you have rows in a specific order that will need to
be processed once on SQL Server. You want to preserve the order of rows
in the file so the rows can be processed in the same order once on SQL
Server.
In order to do this in any relational database, you need a sort key.
There is never a guarantee that a query you run without an ORDER BY will
return rows in the same order in any consistent way.
My understanding is that BCP feeds the rows in the order they appear in
a file. I can't imagine any reason it would or could do it differently.
In that case, you want to insert the data into a table that contains an
IDENTITY column. You can then use that key for your ORDER BY when
processing the rows from whatever process does that.
David Gugick
Imceda Software
www.imceda.com
|||Do you know what order the source file is sorted in? If so, and if the
sort order column(s) are included then you may not need to know the
line number since it is (theoretically anyway) possible to derive that
information from the other data.
If not, then this article has a useful suggestion:
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
not sure if that will work with unicode data though.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message:

> Do you know what order the source file is sorted in? If so, and if
the
> sort order column(s) are included then you may not need to know the
> line number since it is (theoretically anyway) possible to derive
that
> information from the other data.
Unfortunately the data file consists of simple lines of text with no
other way to extract potential column information before loading it
into the database.

> If not, then this article has a useful suggestion:
>
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
> not sure if that will work with unicode data though.
Good suggestion but it does fail with Unicode. Thanks anyway.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> My understanding is that BCP feeds the rows in the order they appear
in
> a file. I can't imagine any reason it would or could do it
differently.
> In that case, you want to insert the data into a table that contains
an
> IDENTITY column. You can then use that key for your ORDER BY when
> processing the rows from whatever process does that.
In general BCP loads the data in the same order as the file but not
always. The ordering sometimes reverses for thousands of rows, or
skips certain rows, but you need to check it carefully to find the
misordering. You can create a table with an identity column and load
the data, but again if the rows are not loaded in the same sequence as
the file this won't matter. You will end up with an ordered table that
is unfortunately not in the same order as the original file.
To be honest I have tried all these suggestions in the past. My
typical solution would be to open the original file in Excel, add a
rownumber column and then save the resulting file as a Unicode file.
This works up until around a maximum of 63,000 rows. You can break a
file into 63,000 row subfiles, but this would be too tedious if you
have row counts approaching a million.
Thanks for the suggestions but I may have to learn some C#.
Unfortunately Visual Basic has problems with Unicode, as I suspect
also C++ and C have similar problems.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||What about using the CTS Import Wizard to import the data from the flat
file into a table with an identity.
Where is this data coming from? Is there any way to recreate it with a
counter column included in the output?
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> What about using the CTS Import Wizard to import the data from the
flat
> file into a table with an identity.
It's the same problem. The table order generally follows the order in
the file but not always.

> Where is this data coming from? Is there any way to recreate it with
a
> counter column included in the output?
It's foreign language dictionary data that cannot be recreated. I
could manipulate the data on the file level but I am trying to avoid
potential problems with Unicode. Once the data gets into SQL Server I
don't have any problems, as long as the table order exactly matches
the file order.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||no-email wrote:
> It's foreign language dictionary data that cannot be recreated. I
> could manipulate the data on the file level but I am trying to avoid
> potential problems with Unicode. Once the data gets into SQL Server I
> don't have any problems, as long as the table order exactly matches
> the file order.
I'm not sure what problems you would have as long as the tool you are
using to edit the data is unicode aware. I use TextEdit for editing
(www.textpad.com) and it has simple replacement expressions.
Assuming you had each row of data on a single line, you could simply do
the following:
1- Add a leading CARRIAGE RETURN to the file
2- Open the Replace dialog
3- Check the Regular Expression option
4- Type "\n" - WITHOUT QUOTES in the Find What entry- means New Line
character
5- Type "\n\i\t" - WITHOUT QUOTES in the Replace With entry - means New
Line + Auto Number + TAB
6- Click Replace All
7 - Remove the leading carriage return in the file
8 - Click FILE SAVE AS and make sure the UNICODE option is selected
You can replace the TAB character with whatever your file requires or
add DOUBLE QUOTES around the Auto Number, etc.
You can download a free trial of TextPad on the web site.
David Gugick
Imceda Software
www.imceda.com

How to load a Unicode file into the database in the same order as the file order

The data file is a simple Unicode file with lines of text. BCP
apparently doesn't guarantee this ordering, and neither does the
import tool. I want to be able to load the data either sequentially or
add line numbering to large Unicode file (1 million lines). I don't
want to deal with another programming language if possible and I
wonder if there's a trick in SQL Server to get this accomplished.
Thanks for any help.
Mark Leary
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
no-email wrote:
> The data file is a simple Unicode file with lines of text. BCP
> apparently doesn't guarantee this ordering, and neither does the
> import tool. I want to be able to load the data either sequentially or
> add line numbering to large Unicode file (1 million lines). I don't
> want to deal with another programming language if possible and I
> wonder if there's a trick in SQL Server to get this accomplished.
> Thanks for any help.
> Mark Leary
>
Why does the order of the rows inserted into the table matter in your
case? Relational databases don't understand row order. If you need them
sorted in some way after the import, you can create a clustered index on
the table to get the rows ordered in a way that helps your queries
perform better.
In general, I think BCP processes the rows in the file sequentially. But
again, I'm not clear on why this matters.
Could you elaborate on the exact issue you are trying to avoid.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> Why does the order of the rows inserted into the table matter in your
> case? Relational databases don't understand row order. If you need them
> sorted in some way after the import, you can create a clustered index on
> the table to get the rows ordered in a way that helps your queries perform
> better.
> In general, I think BCP processes the rows in the file sequentially. But
> again, I'm not clear on why this matters.
> Could you elaborate on the exact issue you are trying to avoid.
I am trying to load a text file sequentially in order to perform text
manipulations using T-SQL that do depend on the exact order. I would be
happy with simply adding a line number to each line of the Unicode text
file, and then loading the file with line number determining the order, but
I want to avoid programming in another language if possible. Eventually the
loaded text would be converted to proper relational tables. This doesn't
have to do with improving performance. Does this help?
Thanks.
|||no-email wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote:
>
> I am trying to load a text file sequentially in order to perform text
> manipulations using T-SQL that do depend on the exact order. I would
> be happy with simply adding a line number to each line of the Unicode
> text file, and then loading the file with line number determining the
> order, but I want to avoid programming in another language if
> possible. Eventually the loaded text would be converted to proper
> relational tables. This doesn't have to do with improving
> performance. Does this help?
> Thanks.
Yes. It sounds like you have rows in a specific order that will need to
be processed once on SQL Server. You want to preserve the order of rows
in the file so the rows can be processed in the same order once on SQL
Server.
In order to do this in any relational database, you need a sort key.
There is never a guarantee that a query you run without an ORDER BY will
return rows in the same order in any consistent way.
My understanding is that BCP feeds the rows in the order they appear in
a file. I can't imagine any reason it would or could do it differently.
In that case, you want to insert the data into a table that contains an
IDENTITY column. You can then use that key for your ORDER BY when
processing the rows from whatever process does that.
David Gugick
Imceda Software
www.imceda.com
|||Do you know what order the source file is sorted in? If so, and if the
sort order column(s) are included then you may not need to know the
line number since it is (theoretically anyway) possible to derive that
information from the other data.
If not, then this article has a useful suggestion:
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
not sure if that will work with unicode data though.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message:

> Do you know what order the source file is sorted in? If so, and if
the
> sort order column(s) are included then you may not need to know the
> line number since it is (theoretically anyway) possible to derive
that
> information from the other data.
Unfortunately the data file consists of simple lines of text with no
other way to extract potential column information before loading it
into the database.

> If not, then this article has a useful suggestion:
>
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
> not sure if that will work with unicode data though.
Good suggestion but it does fail with Unicode. Thanks anyway.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> My understanding is that BCP feeds the rows in the order they appear
in
> a file. I can't imagine any reason it would or could do it
differently.
> In that case, you want to insert the data into a table that contains
an
> IDENTITY column. You can then use that key for your ORDER BY when
> processing the rows from whatever process does that.
In general BCP loads the data in the same order as the file but not
always. The ordering sometimes reverses for thousands of rows, or
skips certain rows, but you need to check it carefully to find the
misordering. You can create a table with an identity column and load
the data, but again if the rows are not loaded in the same sequence as
the file this won't matter. You will end up with an ordered table that
is unfortunately not in the same order as the original file.
To be honest I have tried all these suggestions in the past. My
typical solution would be to open the original file in Excel, add a
rownumber column and then save the resulting file as a Unicode file.
This works up until around a maximum of 63,000 rows. You can break a
file into 63,000 row subfiles, but this would be too tedious if you
have row counts approaching a million.
Thanks for the suggestions but I may have to learn some C#.
Unfortunately Visual Basic has problems with Unicode, as I suspect
also C++ and C have similar problems.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||What about using the CTS Import Wizard to import the data from the flat
file into a table with an identity.
Where is this data coming from? Is there any way to recreate it with a
counter column included in the output?
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> What about using the CTS Import Wizard to import the data from the
flat
> file into a table with an identity.
It's the same problem. The table order generally follows the order in
the file but not always.

> Where is this data coming from? Is there any way to recreate it with
a
> counter column included in the output?
It's foreign language dictionary data that cannot be recreated. I
could manipulate the data on the file level but I am trying to avoid
potential problems with Unicode. Once the data gets into SQL Server I
don't have any problems, as long as the table order exactly matches
the file order.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||no-email wrote:
> It's foreign language dictionary data that cannot be recreated. I
> could manipulate the data on the file level but I am trying to avoid
> potential problems with Unicode. Once the data gets into SQL Server I
> don't have any problems, as long as the table order exactly matches
> the file order.
I'm not sure what problems you would have as long as the tool you are
using to edit the data is unicode aware. I use TextEdit for editing
(www.textpad.com) and it has simple replacement expressions.
Assuming you had each row of data on a single line, you could simply do
the following:
1- Add a leading CARRIAGE RETURN to the file
2- Open the Replace dialog
3- Check the Regular Expression option
4- Type "\n" - WITHOUT QUOTES in the Find What entry- means New Line
character
5- Type "\n\i\t" - WITHOUT QUOTES in the Replace With entry - means New
Line + Auto Number + TAB
6- Click Replace All
7 - Remove the leading carriage return in the file
8 - Click FILE SAVE AS and make sure the UNICODE option is selected
You can replace the TAB character with whatever your file requires or
add DOUBLE QUOTES around the Auto Number, etc.
You can download a free trial of TextPad on the web site.
David Gugick
Imceda Software
www.imceda.com

How to load a Unicode file into the database in the same order as the file order

The data file is a simple Unicode file with lines of text. BCP
apparently doesn't guarantee this ordering, and neither does the
import tool. I want to be able to load the data either sequentially or
add line numbering to large Unicode file (1 million lines). I don't
want to deal with another programming language if possible and I
wonder if there's a trick in SQL Server to get this accomplished.
Thanks for any help.
Mark Leary
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet New
s==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000
Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--no-email wrote:
> The data file is a simple Unicode file with lines of text. BCP
> apparently doesn't guarantee this ordering, and neither does the
> import tool. I want to be able to load the data either sequentially or
> add line numbering to large Unicode file (1 million lines). I don't
> want to deal with another programming language if possible and I
> wonder if there's a trick in SQL Server to get this accomplished.
> Thanks for any help.
> Mark Leary
>
Why does the order of the rows inserted into the table matter in your
case? Relational databases don't understand row order. If you need them
sorted in some way after the import, you can create a clustered index on
the table to get the rows ordered in a way that helps your queries
perform better.
In general, I think BCP processes the rows in the file sequentially. But
again, I'm not clear on why this matters.
Could you elaborate on the exact issue you are trying to avoid.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> Why does the order of the rows inserted into the table matter in your
> case? Relational databases don't understand row order. If you need them
> sorted in some way after the import, you can create a clustered index on
> the table to get the rows ordered in a way that helps your queries perform
> better.
> In general, I think BCP processes the rows in the file sequentially. But
> again, I'm not clear on why this matters.
> Could you elaborate on the exact issue you are trying to avoid.
I am trying to load a text file sequentially in order to perform text
manipulations using T-SQL that do depend on the exact order. I would be
happy with simply adding a line number to each line of the Unicode text
file, and then loading the file with line number determining the order, but
I want to avoid programming in another language if possible. Eventually the
loaded text would be converted to proper relational tables. This doesn't
have to do with improving performance. Does this help?
Thanks.|||no-email wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote:
>
> I am trying to load a text file sequentially in order to perform text
> manipulations using T-SQL that do depend on the exact order. I would
> be happy with simply adding a line number to each line of the Unicode
> text file, and then loading the file with line number determining the
> order, but I want to avoid programming in another language if
> possible. Eventually the loaded text would be converted to proper
> relational tables. This doesn't have to do with improving
> performance. Does this help?
> Thanks.
Yes. It sounds like you have rows in a specific order that will need to
be processed once on SQL Server. You want to preserve the order of rows
in the file so the rows can be processed in the same order once on SQL
Server.
In order to do this in any relational database, you need a sort key.
There is never a guarantee that a query you run without an ORDER BY will
return rows in the same order in any consistent way.
My understanding is that BCP feeds the rows in the order they appear in
a file. I can't imagine any reason it would or could do it differently.
In that case, you want to insert the data into a table that contains an
IDENTITY column. You can then use that key for your ORDER BY when
processing the rows from whatever process does that.
David Gugick
Imceda Software
www.imceda.com|||Do you know what order the source file is sorted in? If so, and if the
sort order column(s) are included then you may not need to know the
line number since it is (theoretically anyway) possible to derive that
information from the other data.
If not, then this article has a useful suggestion:
http://www.google.co.uk/groups?selm...FTNGP11.phx.gbl
not sure if that will work with unicode data though.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message:

> Do you know what order the source file is sorted in? If so, and if
the
> sort order column(s) are included then you may not need to know the
> line number since it is (theoretically anyway) possible to derive
that
> information from the other data.
Unfortunately the data file consists of simple lines of text with no
other way to extract potential column information before loading it
into the database.

> If not, then this article has a useful suggestion:
>
http://www.google.co.uk/groups?selm...FTNGP11.phx.gbl
> not sure if that will work with unicode data though.
Good suggestion but it does fail with Unicode. Thanks anyway.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet New
s==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000
Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> My understanding is that BCP feeds the rows in the order they appear
in
> a file. I can't imagine any reason it would or could do it
differently.
> In that case, you want to insert the data into a table that contains
an
> IDENTITY column. You can then use that key for your ORDER BY when
> processing the rows from whatever process does that.
In general BCP loads the data in the same order as the file but not
always. The ordering sometimes reverses for thousands of rows, or
skips certain rows, but you need to check it carefully to find the
misordering. You can create a table with an identity column and load
the data, but again if the rows are not loaded in the same sequence as
the file this won't matter. You will end up with an ordered table that
is unfortunately not in the same order as the original file.
To be honest I have tried all these suggestions in the past. My
typical solution would be to open the original file in Excel, add a
rownumber column and then save the resulting file as a Unicode file.
This works up until around a maximum of 63,000 rows. You can break a
file into 63,000 row subfiles, but this would be too tedious if you
have row counts approaching a million.
Thanks for the suggestions but I may have to learn some C#.
Unfortunately Visual Basic has problems with Unicode, as I suspect
also C++ and C have similar problems.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet New
s==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000
Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||no-email wrote:
> It's foreign language dictionary data that cannot be recreated. I
> could manipulate the data on the file level but I am trying to avoid
> potential problems with Unicode. Once the data gets into SQL Server I
> don't have any problems, as long as the table order exactly matches
> the file order.
I'm not sure what problems you would have as long as the tool you are
using to edit the data is unicode aware. I use TextEdit for editing
(www.textpad.com) and it has simple replacement expressions.
Assuming you had each row of data on a single line, you could simply do
the following:
1- Add a leading CARRIAGE RETURN to the file
2- Open the Replace dialog
3- Check the Regular Expression option
4- Type "\n" - WITHOUT QUOTES in the Find What entry- means New Line
character
5- Type "\n\i\t" - WITHOUT QUOTES in the Replace With entry - means New
Line + Auto Number + TAB
6- Click Replace All
7 - Remove the leading carriage return in the file
8 - Click FILE SAVE AS and make sure the UNICODE option is selected
You can replace the TAB character with whatever your file requires or
add DOUBLE QUOTES around the Auto Number, etc.
You can download a free trial of TextPad on the web site.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#ZNRubc$EHA.4004@.tk2msftngp13.phx.gbl...
> no-email wrote:
avoid[vbcol=seagreen]
Server I[vbcol=seagreen]
matches[vbcol=seagreen]
> I'm not sure what problems you would have as long as the tool you
are
> using to edit the data is unicode aware. I use TextEdit for editing
> (www.textpad.com) and it has simple replacement expressions.
Let me give it a try. With Word it just locks up after a few minutes
and dies. Notepad is way too slow on the replacements. I have 512 Meg
of memory but I still have problems. I'll try textpad.
Thanks for the suggestion.|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#ZNRubc$EHA.4004@.tk2msftngp13.phx.gbl...
> no-email wrote:
avoid[vbcol=seagreen]
Server I[vbcol=seagreen]
matches[vbcol=seagreen]
> I'm not sure what problems you would have as long as the tool you
are
> using to edit the data is unicode aware. I use TextEdit for editing
> (www.textpad.com) and it has simple replacement expressions.
I tried it but it doesn't properly handle Unicode.
Thanks anyway.

How to load a Unicode file into the database in the same order as the file order

The data file is a simple Unicode file with lines of text. BCP
apparently doesn't guarantee this ordering, and neither does the
import tool. I want to be able to load the data either sequentially or
add line numbering to large Unicode file (1 million lines). I don't
want to deal with another programming language if possible and I
wonder if there's a trick in SQL Server to get this accomplished.
Thanks for any help.
Mark Leary
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--no-email wrote:
> The data file is a simple Unicode file with lines of text. BCP
> apparently doesn't guarantee this ordering, and neither does the
> import tool. I want to be able to load the data either sequentially or
> add line numbering to large Unicode file (1 million lines). I don't
> want to deal with another programming language if possible and I
> wonder if there's a trick in SQL Server to get this accomplished.
> Thanks for any help.
> Mark Leary
>
Why does the order of the rows inserted into the table matter in your
case? Relational databases don't understand row order. If you need them
sorted in some way after the import, you can create a clustered index on
the table to get the rows ordered in a way that helps your queries
perform better.
In general, I think BCP processes the rows in the file sequentially. But
again, I'm not clear on why this matters.
Could you elaborate on the exact issue you are trying to avoid.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote:
> Why does the order of the rows inserted into the table matter in your
> case? Relational databases don't understand row order. If you need them
> sorted in some way after the import, you can create a clustered index on
> the table to get the rows ordered in a way that helps your queries perform
> better.
> In general, I think BCP processes the rows in the file sequentially. But
> again, I'm not clear on why this matters.
> Could you elaborate on the exact issue you are trying to avoid.
I am trying to load a text file sequentially in order to perform text
manipulations using T-SQL that do depend on the exact order. I would be
happy with simply adding a line number to each line of the Unicode text
file, and then loading the file with line number determining the order, but
I want to avoid programming in another language if possible. Eventually the
loaded text would be converted to proper relational tables. This doesn't
have to do with improving performance. Does this help?
Thanks.|||no-email wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote:
>> Why does the order of the rows inserted into the table matter in your
>> case? Relational databases don't understand row order. If you need
>> them sorted in some way after the import, you can create a clustered
>> index on the table to get the rows ordered in a way that helps your
>> queries perform better.
>> In general, I think BCP processes the rows in the file sequentially.
>> But again, I'm not clear on why this matters.
>> Could you elaborate on the exact issue you are trying to avoid.
> I am trying to load a text file sequentially in order to perform text
> manipulations using T-SQL that do depend on the exact order. I would
> be happy with simply adding a line number to each line of the Unicode
> text file, and then loading the file with line number determining the
> order, but I want to avoid programming in another language if
> possible. Eventually the loaded text would be converted to proper
> relational tables. This doesn't have to do with improving
> performance. Does this help?
> Thanks.
Yes. It sounds like you have rows in a specific order that will need to
be processed once on SQL Server. You want to preserve the order of rows
in the file so the rows can be processed in the same order once on SQL
Server.
In order to do this in any relational database, you need a sort key.
There is never a guarantee that a query you run without an ORDER BY will
return rows in the same order in any consistent way.
My understanding is that BCP feeds the rows in the order they appear in
a file. I can't imagine any reason it would or could do it differently.
In that case, you want to insert the data into a table that contains an
IDENTITY column. You can then use that key for your ORDER BY when
processing the rows from whatever process does that.
David Gugick
Imceda Software
www.imceda.com|||Do you know what order the source file is sorted in? If so, and if the
sort order column(s) are included then you may not need to know the
line number since it is (theoretically anyway) possible to derive that
information from the other data.
If not, then this article has a useful suggestion:
http://www.google.co.uk/groups?selm=uKOCiqtDEHA.1604%40TK2MSFTNGP11.phx.gbl
not sure if that will work with unicode data though.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message:
> Do you know what order the source file is sorted in? If so, and if
the
> sort order column(s) are included then you may not need to know the
> line number since it is (theoretically anyway) possible to derive
that
> information from the other data.
Unfortunately the data file consists of simple lines of text with no
other way to extract potential column information before loading it
into the database.
> If not, then this article has a useful suggestion:
>
http://www.google.co.uk/groups?selm=uKOCiqtDEHA.1604%40TK2MSFTNGP11.phx.gbl
> not sure if that will work with unicode data though.
Good suggestion but it does fail with Unicode. Thanks anyway.
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||"David Gugick" <davidg-nospam@.imceda.com> wrote:
> My understanding is that BCP feeds the rows in the order they appear
in
> a file. I can't imagine any reason it would or could do it
differently.
> In that case, you want to insert the data into a table that contains
an
> IDENTITY column. You can then use that key for your ORDER BY when
> processing the rows from whatever process does that.
In general BCP loads the data in the same order as the file but not
always. The ordering sometimes reverses for thousands of rows, or
skips certain rows, but you need to check it carefully to find the
misordering. You can create a table with an identity column and load
the data, but again if the rows are not loaded in the same sequence as
the file this won't matter. You will end up with an ordered table that
is unfortunately not in the same order as the original file.
To be honest I have tried all these suggestions in the past. My
typical solution would be to open the original file in Excel, add a
rownumber column and then save the resulting file as a Unicode file.
This works up until around a maximum of 63,000 rows. You can break a
file into 63,000 row subfiles, but this would be too tedious if you
have row counts approaching a million.
Thanks for the suggestions but I may have to learn some C#.
Unfortunately Visual Basic has problems with Unicode, as I suspect
also C++ and C have similar problems.
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||What about using the CTS Import Wizard to import the data from the flat
file into a table with an identity.
Where is this data coming from? Is there any way to recreate it with a
counter column included in the output?
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote:
> What about using the CTS Import Wizard to import the data from the
flat
> file into a table with an identity.
It's the same problem. The table order generally follows the order in
the file but not always.
> Where is this data coming from? Is there any way to recreate it with
a
> counter column included in the output?
It's foreign language dictionary data that cannot be recreated. I
could manipulate the data on the file level but I am trying to avoid
potential problems with Unicode. Once the data gets into SQL Server I
don't have any problems, as long as the table order exactly matches
the file order.
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||no-email wrote:
> It's foreign language dictionary data that cannot be recreated. I
> could manipulate the data on the file level but I am trying to avoid
> potential problems with Unicode. Once the data gets into SQL Server I
> don't have any problems, as long as the table order exactly matches
> the file order.
I'm not sure what problems you would have as long as the tool you are
using to edit the data is unicode aware. I use TextEdit for editing
(www.textpad.com) and it has simple replacement expressions.
Assuming you had each row of data on a single line, you could simply do
the following:
1- Add a leading CARRIAGE RETURN to the file
2- Open the Replace dialog
3- Check the Regular Expression option
4- Type "\n" - WITHOUT QUOTES in the Find What entry- means New Line
character
5- Type "\n\i\t" - WITHOUT QUOTES in the Replace With entry - means New
Line + Auto Number + TAB
6- Click Replace All
7 - Remove the leading carriage return in the file
8 - Click FILE SAVE AS and make sure the UNICODE option is selected
You can replace the TAB character with whatever your file requires or
add DOUBLE QUOTES around the Auto Number, etc.
You can download a free trial of TextPad on the web site.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#ZNRubc$EHA.4004@.tk2msftngp13.phx.gbl...
> no-email wrote:
> > It's foreign language dictionary data that cannot be recreated. I
> > could manipulate the data on the file level but I am trying to
avoid
> > potential problems with Unicode. Once the data gets into SQL
Server I
> > don't have any problems, as long as the table order exactly
matches
> > the file order.
> I'm not sure what problems you would have as long as the tool you
are
> using to edit the data is unicode aware. I use TextEdit for editing
> (www.textpad.com) and it has simple replacement expressions.
Let me give it a try. With Word it just locks up after a few minutes
and dies. Notepad is way too slow on the replacements. I have 512 Meg
of memory but I still have problems. I'll try textpad.
Thanks for the suggestion.|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#ZNRubc$EHA.4004@.tk2msftngp13.phx.gbl...
> no-email wrote:
> > It's foreign language dictionary data that cannot be recreated. I
> > could manipulate the data on the file level but I am trying to
avoid
> > potential problems with Unicode. Once the data gets into SQL
Server I
> > don't have any problems, as long as the table order exactly
matches
> > the file order.
> I'm not sure what problems you would have as long as the tool you
are
> using to edit the data is unicode aware. I use TextEdit for editing
> (www.textpad.com) and it has simple replacement expressions.
I tried it but it doesn't properly handle Unicode.
Thanks anyway.|||no-email wrote:
> I tried it but it doesn't properly handle Unicode.
> Thanks anyway.
Textpad does handle unicode. I use it with unicode data all the time.
What problems are you having with it? Just because it doesn't look right
in the editor doesn't mean it's not saving the file properly.
Could you elaborate on the issue you are seeing?
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eBFSCDe$EHA.1408@.TK2MSFTNGP10.phx.gbl...
> no-email wrote:
> > I tried it but it doesn't properly handle Unicode.
> >
> > Thanks anyway.
> Textpad does handle unicode. I use it with unicode data all the
time.
> What problems are you having with it? Just because it doesn't look
right
> in the editor doesn't mean it's not saving the file properly.
> Could you elaborate on the issue you are seeing?
I open the original Unicode file and save it using UTF-8 encoding.
I then open the file with Textpad using File->Open, with UTF-8
encoding, and I get the following error message:
"WARNING: "filename" contains characters that do not exist in code
page 1253 (ANSI-Greek). They will be converted to the system default
character, if you click OK."
All the Unicode characters are converted to question marks.
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||David Gugick (davidg-nospam@.imceda.com) writes:
> Textpad does handle unicode. I use it with unicode data all the time.
> What problems are you having with it? Just because it doesn't look right
> in the editor doesn't mean it's not saving the file properly.
Are you using any version 5 beta?
Textpad 4.7 can read Unicode files, but if there actually is data outside
you ANSI code pages, that data will be mutilated. I have had problems
with as simple things as BKS files (control files for NT backup). If
I edit them with Textpad, NT backup does not like the file after I've
been to it.
See http://www.abaris.se/abaperls/doc/textpad.html for a couple of
links to similar tools. I have not evaulated them with regards to
Unicode, but I have a vague recollection that UltraEdit may cut it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||> In general BCP loads the data in the same order as the file but not
> always. The ordering sometimes reverses for thousands of rows, or
> skips certain rows, but you need to check it carefully to find the
> misordering. You can create a table with an identity column and load
> the data, but again if the rows are not loaded in the same sequence as
> the file this won't matter. You will end up with an ordered table that
> is unfortunately not in the same order as the original file.
Have you tried loading to a table with an identity column with BCP with
batch size set to 1?
Craig

How to load a Unicode file into the database in the same order as the file order

The data file is a simple Unicode file with lines of text. BCP
apparently doesn't guarantee this ordering, and neither does the
import tool. I want to be able to load the data either sequentially or
add line numbering to large Unicode file (1 million lines). I don't
want to deal with another programming language if possible and I
wonder if there's a trick in SQL Server to get this accomplished.

Thanks for any help.

Mark Leary

--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--no-email wrote:
> The data file is a simple Unicode file with lines of text. BCP
> apparently doesn't guarantee this ordering, and neither does the
> import tool. I want to be able to load the data either sequentially or
> add line numbering to large Unicode file (1 million lines). I don't
> want to deal with another programming language if possible and I
> wonder if there's a trick in SQL Server to get this accomplished.
> Thanks for any help.
> Mark Leary

Why does the order of the rows inserted into the table matter in your
case? Relational databases don't understand row order. If you need them
sorted in some way after the import, you can create a clustered index on
the table to get the rows ordered in a way that helps your queries
perform better.

In general, I think BCP processes the rows in the file sequentially. But
again, I'm not clear on why this matters.

Could you elaborate on the exact issue you are trying to avoid.

--
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> Why does the order of the rows inserted into the table matter in your
> case? Relational databases don't understand row order. If you need them
> sorted in some way after the import, you can create a clustered index on
> the table to get the rows ordered in a way that helps your queries perform
> better.
> In general, I think BCP processes the rows in the file sequentially. But
> again, I'm not clear on why this matters.
> Could you elaborate on the exact issue you are trying to avoid.

I am trying to load a text file sequentially in order to perform text
manipulations using T-SQL that do depend on the exact order. I would be
happy with simply adding a line number to each line of the Unicode text
file, and then loading the file with line number determining the order, but
I want to avoid programming in another language if possible. Eventually the
loaded text would be converted to proper relational tables. This doesn't
have to do with improving performance. Does this help?

Thanks.|||no-email wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote:
>> Why does the order of the rows inserted into the table matter in your
>> case? Relational databases don't understand row order. If you need
>> them sorted in some way after the import, you can create a clustered
>> index on the table to get the rows ordered in a way that helps your
>> queries perform better.
>>
>> In general, I think BCP processes the rows in the file sequentially.
>> But again, I'm not clear on why this matters.
>>
>> Could you elaborate on the exact issue you are trying to avoid.
> I am trying to load a text file sequentially in order to perform text
> manipulations using T-SQL that do depend on the exact order. I would
> be happy with simply adding a line number to each line of the Unicode
> text file, and then loading the file with line number determining the
> order, but I want to avoid programming in another language if
> possible. Eventually the loaded text would be converted to proper
> relational tables. This doesn't have to do with improving
> performance. Does this help?
> Thanks.

Yes. It sounds like you have rows in a specific order that will need to
be processed once on SQL Server. You want to preserve the order of rows
in the file so the rows can be processed in the same order once on SQL
Server.

In order to do this in any relational database, you need a sort key.
There is never a guarantee that a query you run without an ORDER BY will
return rows in the same order in any consistent way.

My understanding is that BCP feeds the rows in the order they appear in
a file. I can't imagine any reason it would or could do it differently.
In that case, you want to insert the data into a table that contains an
IDENTITY column. You can then use that key for your ORDER BY when
processing the rows from whatever process does that.

--
David Gugick
Imceda Software
www.imceda.com|||Do you know what order the source file is sorted in? If so, and if the
sort order column(s) are included then you may not need to know the
line number since it is (theoretically anyway) possible to derive that
information from the other data.

If not, then this article has a useful suggestion:

http://www.google.co.uk/groups?selm...NGP11.phx.gb l

not sure if that will work with unicode data though.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message:

> Do you know what order the source file is sorted in? If so, and if
the
> sort order column(s) are included then you may not need to know the
> line number since it is (theoretically anyway) possible to derive
that
> information from the other data.

Unfortunately the data file consists of simple lines of text with no
other way to extract potential column information before loading it
into the database.

> If not, then this article has a useful suggestion:
>
http://www.google.co.uk/groups?selm...NGP11.phx.gb l
> not sure if that will work with unicode data though.

Good suggestion but it does fail with Unicode. Thanks anyway.

--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> My understanding is that BCP feeds the rows in the order they appear
in
> a file. I can't imagine any reason it would or could do it
differently.
> In that case, you want to insert the data into a table that contains
an
> IDENTITY column. You can then use that key for your ORDER BY when
> processing the rows from whatever process does that.

In general BCP loads the data in the same order as the file but not
always. The ordering sometimes reverses for thousands of rows, or
skips certain rows, but you need to check it carefully to find the
misordering. You can create a table with an identity column and load
the data, but again if the rows are not loaded in the same sequence as
the file this won't matter. You will end up with an ordered table that
is unfortunately not in the same order as the original file.

To be honest I have tried all these suggestions in the past. My
typical solution would be to open the original file in Excel, add a
rownumber column and then save the resulting file as a Unicode file.
This works up until around a maximum of 63,000 rows. You can break a
file into 63,000 row subfiles, but this would be too tedious if you
have row counts approaching a million.

Thanks for the suggestions but I may have to learn some C#.
Unfortunately Visual Basic has problems with Unicode, as I suspect
also C++ and C have similar problems.

--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||What about using the CTS Import Wizard to import the data from the flat
file into a table with an identity.

Where is this data coming from? Is there any way to recreate it with a
counter column included in the output?

--
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> What about using the CTS Import Wizard to import the data from the
flat
> file into a table with an identity.

It's the same problem. The table order generally follows the order in
the file but not always.

> Where is this data coming from? Is there any way to recreate it with
a
> counter column included in the output?

It's foreign language dictionary data that cannot be recreated. I
could manipulate the data on the file level but I am trying to avoid
potential problems with Unicode. Once the data gets into SQL Server I
don't have any problems, as long as the table order exactly matches
the file order.

--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||no-email wrote:
> It's foreign language dictionary data that cannot be recreated. I
> could manipulate the data on the file level but I am trying to avoid
> potential problems with Unicode. Once the data gets into SQL Server I
> don't have any problems, as long as the table order exactly matches
> the file order.

I'm not sure what problems you would have as long as the tool you are
using to edit the data is unicode aware. I use TextEdit for editing
(www.textpad.com) and it has simple replacement expressions.

Assuming you had each row of data on a single line, you could simply do
the following:

1- Add a leading CARRIAGE RETURN to the file
2- Open the Replace dialog
3- Check the Regular Expression option
4- Type "\n" - WITHOUT QUOTES in the Find What entry- means New Line
character
5- Type "\n\i\t" - WITHOUT QUOTES in the Replace With entry - means New
Line + Auto Number + TAB
6- Click Replace All
7 - Remove the leading carriage return in the file
8 - Click FILE SAVE AS and make sure the UNICODE option is selected

You can replace the TAB character with whatever your file requires or
add DOUBLE QUOTES around the Auto Number, etc.

You can download a free trial of TextPad on the web site.

--
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#ZNRubc$EHA.4004@.tk2msftngp13.phx.gbl...
> no-email wrote:
> > It's foreign language dictionary data that cannot be recreated. I
> > could manipulate the data on the file level but I am trying to
avoid
> > potential problems with Unicode. Once the data gets into SQL
Server I
> > don't have any problems, as long as the table order exactly
matches
> > the file order.
> I'm not sure what problems you would have as long as the tool you
are
> using to edit the data is unicode aware. I use TextEdit for editing
> (www.textpad.com) and it has simple replacement expressions.

Let me give it a try. With Word it just locks up after a few minutes
and dies. Notepad is way too slow on the replacements. I have 512 Meg
of memory but I still have problems. I'll try textpad.

Thanks for the suggestion.|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#ZNRubc$EHA.4004@.tk2msftngp13.phx.gbl...
> no-email wrote:
> > It's foreign language dictionary data that cannot be recreated. I
> > could manipulate the data on the file level but I am trying to
avoid
> > potential problems with Unicode. Once the data gets into SQL
Server I
> > don't have any problems, as long as the table order exactly
matches
> > the file order.
> I'm not sure what problems you would have as long as the tool you
are
> using to edit the data is unicode aware. I use TextEdit for editing
> (www.textpad.com) and it has simple replacement expressions.

I tried it but it doesn't properly handle Unicode.

Thanks anyway.|||no-email wrote:
> I tried it but it doesn't properly handle Unicode.
> Thanks anyway.

Textpad does handle unicode. I use it with unicode data all the time.
What problems are you having with it? Just because it doesn't look right
in the editor doesn't mean it's not saving the file properly.

Could you elaborate on the issue you are seeing?

--
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eBFSCDe$EHA.1408@.TK2MSFTNGP10.phx.gbl...
> no-email wrote:
> > I tried it but it doesn't properly handle Unicode.
> > Thanks anyway.
> Textpad does handle unicode. I use it with unicode data all the
time.
> What problems are you having with it? Just because it doesn't look
right
> in the editor doesn't mean it's not saving the file properly.
> Could you elaborate on the issue you are seeing?

I open the original Unicode file and save it using UTF-8 encoding.

I then open the file with Textpad using File->Open, with UTF-8
encoding, and I get the following error message:

"WARNING: "filename" contains characters that do not exist in code
page 1253 (ANSI-Greek). They will be converted to the system default
character, if you click OK."

All the Unicode characters are converted to question marks.

--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||David Gugick (davidg-nospam@.imceda.com) writes:
> Textpad does handle unicode. I use it with unicode data all the time.
> What problems are you having with it? Just because it doesn't look right
> in the editor doesn't mean it's not saving the file properly.

Are you using any version 5 beta?

Textpad 4.7 can read Unicode files, but if there actually is data outside
you ANSI code pages, that data will be mutilated. I have had problems
with as simple things as BKS files (control files for NT backup). If
I edit them with Textpad, NT backup does not like the file after I've
been to it.

See http://www.abaris.se/abaperls/doc/textpad.html for a couple of
links to similar tools. I have not evaulated them with regards to
Unicode, but I have a vague recollection that UltraEdit may cut it.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> In general BCP loads the data in the same order as the file but not
> always. The ordering sometimes reverses for thousands of rows, or
> skips certain rows, but you need to check it carefully to find the
> misordering. You can create a table with an identity column and load
> the data, but again if the rows are not loaded in the same sequence as
> the file this won't matter. You will end up with an ordered table that
> is unfortunately not in the same order as the original file.

Have you tried loading to a table with an identity column with BCP with
batch size set to 1?

Craig

How to list tables with Primary keys

Hello,

We imported a bunch of tables from a database and realized that the
primary keys weren't copied to the destination db. In order to re-
create the keys, we need to know which tables have them. Is there a
command that I can use (on the source db) to find out which tables
contain primary keys? The db has hundreds of tables and I'd rather not
go through each one to see which has a primary key.

Also, for future reference, is there a way to include the primary key
on an import?

Thanks,
PepsDanny (dlapitan@.gmail.com) writes:

Quote:

Originally Posted by

We imported a bunch of tables from a database and realized that the
primary keys weren't copied to the destination db. In order to re-
create the keys, we need to know which tables have them. Is there a
command that I can use (on the source db) to find out which tables
contain primary keys? The db has hundreds of tables and I'd rather not
go through each one to see which has a primary key.

Hopefully all tables have primary keys!

It would have helped if you had said which version of SQL Server you are
using. The query below will run on both SQL 2000 and SQL 2005, but the
old system tables are deprecated on SQL 2005, so had I known you were
using SQL 2005, I would have used the new catalog views instead.

If you did not bring over the primary keys, I suspect that no indexes at
all were copied. This query lists all indexes in a database, and the
column ispk indicates that the index is a primary key. The column
isuniqueconst indicates whether the index is a UNIQUE constraint.

Note that the query as I've written it, will only include the first
five columns in the index. Neither does include information about
ascending/descening, and other less commonly used index properties.

SELECT o.name, i.name,
isclustered = Indexproperty(o.id, i.name, 'IsClustered'),
isunique = Indexproperty(o.id, i.name, 'IsUnique'),
ispk = CASE WHEN o2.xtype = 'PK' THEN 1 ELSE 0 END,
isuniqueconst = CASE WHEN o2.xtype = 'UQ' THEN 1 ELSE 0 END,
cols = ik.col1 + coalesce(', ' + ik.col2, '') +
coalesce(', ' + ik.col3, '') + coalesce(', ' + ik.col4, '') +
coalesce(', ' + ik.col5, '')
FROM sysobjects o
JOIN sysindexes i ON o.id = i.id
LEFT JOIN sysobjects o2 ON o2.name = i.name
AND o2.parent_obj = o.id
JOIN (SELECT ik.id, ik.indid,
col1 = MIN(CASE ik.keyno WHEN 1 THEN c.name END),
col2 = MIN(CASE ik.keyno WHEN 2 THEN c.name END),
col3 = MIN(CASE ik.keyno WHEN 3 THEN c.name END),
col4 = MIN(CASE ik.keyno WHEN 4 THEN c.name END),
col5 = MIN(CASE ik.keyno WHEN 5 THEN c.name END)
FROM sysindexkeys ik
JOIN syscolumns c ON ik.id = c.id
AND ik.colid = c.colid
GROUP BY ik.id, ik.indid) AS ik ON i.id = ik.id
AND i.indid = ik.indid
WHERE Indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND Indexproperty(i.id, i.name, 'IsStatistics') = 0
AND Indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
ORDER BY o.name, i.indid

Quote:

Originally Posted by

Also, for future reference, is there a way to include the primary key
on an import?

There is. But I don't know which tool you used, which version of SQL Server
you have etc. Personally, I prefer to build databases from scripts. When
I need to copy a database, I prefer to use BACKUP/RESTORE.

--
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.mspx|||On Jun 8, 7:59 pm, Danny <dlapi...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
We imported a bunch of tables from a database and realized that the
primary keys weren't copied to the destination db. In order to re-
create the keys, we need to know which tables have them. Is there a
command that I can use (on the source db) to find out which tables
contain primary keys? The db has hundreds of tables and I'd rather not
go through each one to see which has a primary key.
>
Also, for future reference, is there a way to include the primary key
on an import?
>
Thanks,
Peps

What are all the keys used in a database

http://www.sqlhacks.com/faqs/list_all_keys
USE AdventureWorksLT;
go

SELECT schm.name AS 'Schema', tbl.name AS 'Table'
, KEYS.name AS 'Constraint', KEYS.type_desc AS 'Type'
, cols.name AS 'Column'
FROM sys.key_constraints AS KEYS
JOIN sys.TABLES AS tbl
ON tbl.object_id = KEYS.parent_object_id
JOIN sys.schemas AS schm
ON schm.schema_id = tbl.schema_id
JOIN sys.index_columns AS idxcols
ON idxcols.object_id = tbl.object_id
AND idxcols.index_id = KEYS.unique_index_id
JOIN sys.COLUMNS AS cols
ON cols.object_id = tbl.object_id
AND cols.column_id = idxcols.column_id
ORDER BY 1,2,3,4;
go

AND

What are all the tables without a primary key?

http://www.sqlhacks.com/faqs/no_primary_key
USE sql911;
go

SELECT SCHEMA_NAME(schema_id) AS "Schema", name AS "Table"
FROM sys.TABLES
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY 1,2;
go

This includes samples and explanations on how to do it.

Also new this week:

SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server?
What's the structure of a table with MS SQL Server?

How to know what is the default sort order of a database

Hi all,

The server is sql server 2000, it has a database with collation SQL_Latin_General_CP1_CI_AS. How to know what is the default sort order of it? By the way, is it possible to use a query using query analyser to find the sort order of the db? Thanks in advance.

Look at the code page definition

CI = Case Insenstive
AS = Accent Sensitive

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

As far as a query you can find the sort order/sollation for individual databases with

SELECT name, collation_name
FROM master.sys.databases

For the entire server instance, you can find the installed collation with

SELECT SERVERPROPERTY ('collation')

How to know the order of entries in a table

For example some data has entered into a table in a random manner i.e the pk filed value is not in a serial fashion.Is there any table or index that holds the entries of rows into a particular table as entered .

i.e
'some_table' has data like this

3,entry3
2,entry2
4,entry4
1,entry1

I want some DB table or Index that holds data like this about above 'some_table'

row_id ... ... ...
1
2
3
4

here 1 refers to entry of the first column in 'some_table' i.e 3,entry3
and so on...Hi

Please reread my first post in here:
http://www.dbforums.com/showthread.php?t=1620041

There is no order. If you need to know the order that data was inserted then you need to set something up yourself - use an identity column or a timestamp with a GETDATE() default value. Note that the second option will result in "ties" if you insert sets of data.|||Thanks for your help...