nvarchar and output to flat file.

Hello,
Im outputting a sql table to a text file. One of the columns in this table
is an nvarchar(1200) but the result in the flat file is truncated to 254
chars....how do ensure that it outputs all chars up to 1200?
thxJohn,
which approach / utility are you using to outputting the sql table to a text
file?
AMB
"John Smith" wrote:

> Hello,
> Im outputting a sql table to a text file. One of the columns in this table
> is an nvarchar(1200) but the result in the flat file is truncated to 254
> chars....how do ensure that it outputs all chars up to 1200?
> thx
>
>|||I think I gave him a suitable answer on a separate post.
Barrysql

nvarchar & varchar

Hi,

I am new to MS SQL. When I create a column in a table, when shall I
use nvarchar or varchar? Please help.

Thanks,
MikeThe nvarchar data type provides support for Unicode characters. This is
needed if you are building an international system that must store different
languages. However, if you have no need to store Unicode characters then you
are better using varchar. The nvarchar data type occupies twice the space of
varchar as it uses 2 bytes to encode each character.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On 26 Feb, 15:13, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

The nvarchar data type provides support for Unicode characters. This is
needed if you are building an international system that must store different
languages. However, if you have no need to store Unicode characters then you
are better using varchar. The nvarchar data type occupies twice the space of
varchar as it uses 2 bytes to encode each character.
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

varchar will support a lot of characters from different languages
though (depending on the collation codepage) so no need to rush into
doubling your storage if you dont "really" need to|||On Feb 26, 10:26 am, "oliver" <oraus...@.hotmail.comwrote:

Quote:

Originally Posted by

On 26 Feb, 15:13, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:
>

Quote:

Originally Posted by

The nvarchar data type provides support for Unicode characters. This is
needed if you are building an international system that must store different
languages. However, if you have no need to store Unicode characters then you
are better using varchar. The nvarchar data type occupies twice the space of
varchar as it uses 2 bytes to encode each character.

>

Quote:

Originally Posted by

HTH,

>

Quote:

Originally Posted by

Plamen Ratchevhttp://www.SQLStudio.com

>
varchar will support a lot of characters from different languages
though (depending on the collation codepage) so no need to rush into
doubling your storage if you dont "really" need to

Plamen, Oliver Thanks a lot!

Mike|||On Feb 26, 9:47 am, haid...@.gmail.com wrote:

Quote:

Originally Posted by

Hi,
>
I am new to MS SQL. When I create a column in a table, when shall I
use nvarchar or varchar? Please help.
>
Thanks,
Mike

Mike,

Clearly you need to go back to reading the manual or get a Dummy's
book if you don't know the difference between unicode and ascii
strings.

HTH,

Carl Tegeder
Master MS-SQL Administrator|||Carl Tegeder wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I am new to MS SQL. When I create a column in a table, when shall I
>use nvarchar or varchar? Please help.

Quote:

Originally Posted by

Clearly you need to go back to reading the manual or get a Dummy's
book if you don't know the difference between unicode and ascii
strings.

Not the most tactful of responses, but hey.

To the original poster: Google and Wikipedia are your friends.

Numerous Numeric Fields to 1 Numeric Field in New Table

Are there any routines out there that will automatically convert a table (A)
with numerous numeric fields to a new table (B) with just one numeric field.
Thus the number of records in the table (B) would be the number or records
in A mutliplied by the number of numeric fields.
Thanks in advance
Please include DDL with your questions so that we don't have to guess at what
your tables might look like.
Here's an example. Suppose you have a denormalized structure like this:
CREATE TABLE monthly_accounts (account_no INTEGER PRIMARY KEY, jan INTEGER
NULL, feb INTEGER NULL, mar INTEGER NULL, ...)
You can convert this to a more usable form as follows:
CREATE TABLE accounts (account_no INTEGER NOT NULL, dt DATETIME NOT NULL
CHECK (DAY(dt)=1), amount INTEGER NOT NULL, PRIMARY KEY (account_no, dt))
INSERT INTO accounts (account_no, dt, amount)
SELECT account_no, '20040101', jan
FROM monthly_accounts
WHERE jan IS NOT NULL
UNION ALL
SELECT account_no, '20040201', feb
FROM monthly_accounts
WHERE feb IS NOT NULL
UNION ALL
SELECT account_no, '20040301', mar
FROM monthly_accounts
WHERE mar IS NOT NULL
...
Notice that you will usually add at least one column to the key when you do
this.
David Portas
SQL Server MVP
|||Thanks David
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:E5E28C97-6FF6-4EBA-8152-33063A6C813C@.microsoft.com...
> Please include DDL with your questions so that we don't have to guess at
what
> your tables might look like.
> Here's an example. Suppose you have a denormalized structure like this:
> CREATE TABLE monthly_accounts (account_no INTEGER PRIMARY KEY, jan INTEGER
> NULL, feb INTEGER NULL, mar INTEGER NULL, ...)
> You can convert this to a more usable form as follows:
> CREATE TABLE accounts (account_no INTEGER NOT NULL, dt DATETIME NOT NULL
> CHECK (DAY(dt)=1), amount INTEGER NOT NULL, PRIMARY KEY (account_no, dt))
> INSERT INTO accounts (account_no, dt, amount)
> SELECT account_no, '20040101', jan
> FROM monthly_accounts
> WHERE jan IS NOT NULL
> UNION ALL
> SELECT account_no, '20040201', feb
> FROM monthly_accounts
> WHERE feb IS NOT NULL
> UNION ALL
> SELECT account_no, '20040301', mar
> FROM monthly_accounts
> WHERE mar IS NOT NULL
> ...
> Notice that you will usually add at least one column to the key when you
do
> this.
> --
> David Portas
> SQL Server MVP
> --
|||Hi
I occasionally concatenate field names from sysColumn rows (by object) in
order to save myself the typing (in sprocs, etc.). Don't see any reason why
you couldn't add the values in your case (if this is what you're trying to
do).
example:
CREATE PROCEDURE dbo.sp_tablecolumns
@.object_id varchar(100)
AS
DECLARE @.FldCat1 VARCHAR(8000)
SET @.FldCat1=''
SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
FROM sysColumns with (NOLOCK)
WHERE id = object_id(@.object_id)
ORDER BY sysColumns.colorder
PRINT @.FldCat1
GO
usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
rob
"Joe" wrote:

> Are there any routines out there that will automatically convert a table (A)
> with numerous numeric fields to a new table (B) with just one numeric field.
> Thus the number of records in the table (B) would be the number or records
> in A mutliplied by the number of numeric fields.
> Thanks in advance
>
>
|||sorry, I get what you're saying now.
u can do the same kind of thing I mentioned in my 1st post. u'd have to find
the numeric columns from table A first, loop thru A (by row and then columns)
and do the insert (into B) in the loops.
u could write a generic routine starting with the code I posted.
rob
"RobKaratzas" wrote:
[vbcol=seagreen]
> Hi
> I occasionally concatenate field names from sysColumn rows (by object) in
> order to save myself the typing (in sprocs, etc.). Don't see any reason why
> you couldn't add the values in your case (if this is what you're trying to
> do).
> example:
> CREATE PROCEDURE dbo.sp_tablecolumns
> @.object_id varchar(100)
> AS
> DECLARE @.FldCat1 VARCHAR(8000)
> SET @.FldCat1=''
> SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
> FROM sysColumns with (NOLOCK)
> WHERE id = object_id(@.object_id)
> ORDER BY sysColumns.colorder
> PRINT @.FldCat1
> GO
> usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
> rob
> "Joe" wrote:
|||> I occasionally concatenate field names from sysColumn rows (by
object) in
> order to save myself the typing (in sprocs, etc.).
In SQL2000 Query Analyzer can do that automatically for you. Just drag
the Columns node from the Object Browser into the editing window. In
7.0 and earlier you don't have Object Browser so the method you
described may be useful. Not sure what it has to do with Joe's question
though :-)
David Portas
SQL Server MVP
|||What I usually do to copy the column names is
Setting the Result to Text option and then do a
SELECT * FROM table WHERe 1 = 0
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
> object) in
> In SQL2000 Query Analyzer can do that automatically for you. Just drag
> the Columns node from the Object Browser into the editing window. In
> 7.0 and earlier you don't have Object Browser so the method you
> described may be useful. Not sure what it has to do with Joe's question
> though :-)
> --
> David Portas
> SQL Server MVP
> --
>
|||thanks Roji
I did misread the original post.
But in order to handle this problem with a generic solution, you're going to
require some means to programatically gather what these numerous columns are
for whatever Table A has.
Rob
"Roji. P. Thomas" wrote:

> What I usually do to copy the column names is
> Setting the Result to Text option and then do a
> SELECT * FROM table WHERe 1 = 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
>
>
sql

Numerous Numeric Fields to 1 Numeric Field in New Table

Are there any routines out there that will automatically convert a table (A)
with numerous numeric fields to a new table (B) with just one numeric field.
Thus the number of records in the table (B) would be the number or records
in A mutliplied by the number of numeric fields.
Thanks in advance
Please include DDL with your questions so that we don't have to guess at what
your tables might look like.
Here's an example. Suppose you have a denormalized structure like this:
CREATE TABLE monthly_accounts (account_no INTEGER PRIMARY KEY, jan INTEGER
NULL, feb INTEGER NULL, mar INTEGER NULL, ...)
You can convert this to a more usable form as follows:
CREATE TABLE accounts (account_no INTEGER NOT NULL, dt DATETIME NOT NULL
CHECK (DAY(dt)=1), amount INTEGER NOT NULL, PRIMARY KEY (account_no, dt))
INSERT INTO accounts (account_no, dt, amount)
SELECT account_no, '20040101', jan
FROM monthly_accounts
WHERE jan IS NOT NULL
UNION ALL
SELECT account_no, '20040201', feb
FROM monthly_accounts
WHERE feb IS NOT NULL
UNION ALL
SELECT account_no, '20040301', mar
FROM monthly_accounts
WHERE mar IS NOT NULL
...
Notice that you will usually add at least one column to the key when you do
this.
David Portas
SQL Server MVP
|||Thanks David
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:E5E28C97-6FF6-4EBA-8152-33063A6C813C@.microsoft.com...
> Please include DDL with your questions so that we don't have to guess at
what
> your tables might look like.
> Here's an example. Suppose you have a denormalized structure like this:
> CREATE TABLE monthly_accounts (account_no INTEGER PRIMARY KEY, jan INTEGER
> NULL, feb INTEGER NULL, mar INTEGER NULL, ...)
> You can convert this to a more usable form as follows:
> CREATE TABLE accounts (account_no INTEGER NOT NULL, dt DATETIME NOT NULL
> CHECK (DAY(dt)=1), amount INTEGER NOT NULL, PRIMARY KEY (account_no, dt))
> INSERT INTO accounts (account_no, dt, amount)
> SELECT account_no, '20040101', jan
> FROM monthly_accounts
> WHERE jan IS NOT NULL
> UNION ALL
> SELECT account_no, '20040201', feb
> FROM monthly_accounts
> WHERE feb IS NOT NULL
> UNION ALL
> SELECT account_no, '20040301', mar
> FROM monthly_accounts
> WHERE mar IS NOT NULL
> ...
> Notice that you will usually add at least one column to the key when you
do
> this.
> --
> David Portas
> SQL Server MVP
> --
|||Hi
I occasionally concatenate field names from sysColumn rows (by object) in
order to save myself the typing (in sprocs, etc.). Don't see any reason why
you couldn't add the values in your case (if this is what you're trying to
do).
example:
CREATE PROCEDURE dbo.sp_tablecolumns
@.object_id varchar(100)
AS
DECLARE @.FldCat1 VARCHAR(8000)
SET @.FldCat1=''
SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
FROM sysColumns with (NOLOCK)
WHERE id = object_id(@.object_id)
ORDER BY sysColumns.colorder
PRINT @.FldCat1
GO
usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
rob
"Joe" wrote:

> Are there any routines out there that will automatically convert a table (A)
> with numerous numeric fields to a new table (B) with just one numeric field.
> Thus the number of records in the table (B) would be the number or records
> in A mutliplied by the number of numeric fields.
> Thanks in advance
>
>
|||sorry, I get what you're saying now.
u can do the same kind of thing I mentioned in my 1st post. u'd have to find
the numeric columns from table A first, loop thru A (by row and then columns)
and do the insert (into B) in the loops.
u could write a generic routine starting with the code I posted.
rob
"RobKaratzas" wrote:
[vbcol=seagreen]
> Hi
> I occasionally concatenate field names from sysColumn rows (by object) in
> order to save myself the typing (in sprocs, etc.). Don't see any reason why
> you couldn't add the values in your case (if this is what you're trying to
> do).
> example:
> CREATE PROCEDURE dbo.sp_tablecolumns
> @.object_id varchar(100)
> AS
> DECLARE @.FldCat1 VARCHAR(8000)
> SET @.FldCat1=''
> SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
> FROM sysColumns with (NOLOCK)
> WHERE id = object_id(@.object_id)
> ORDER BY sysColumns.colorder
> PRINT @.FldCat1
> GO
> usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
> rob
> "Joe" wrote:
|||> I occasionally concatenate field names from sysColumn rows (by
object) in
> order to save myself the typing (in sprocs, etc.).
In SQL2000 Query Analyzer can do that automatically for you. Just drag
the Columns node from the Object Browser into the editing window. In
7.0 and earlier you don't have Object Browser so the method you
described may be useful. Not sure what it has to do with Joe's question
though :-)
David Portas
SQL Server MVP
|||What I usually do to copy the column names is
Setting the Result to Text option and then do a
SELECT * FROM table WHERe 1 = 0
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
> object) in
> In SQL2000 Query Analyzer can do that automatically for you. Just drag
> the Columns node from the Object Browser into the editing window. In
> 7.0 and earlier you don't have Object Browser so the method you
> described may be useful. Not sure what it has to do with Joe's question
> though :-)
> --
> David Portas
> SQL Server MVP
> --
>
|||thanks Roji
I did misread the original post.
But in order to handle this problem with a generic solution, you're going to
require some means to programatically gather what these numerous columns are
for whatever Table A has.
Rob
"Roji. P. Thomas" wrote:

> What I usually do to copy the column names is
> Setting the Result to Text option and then do a
> SELECT * FROM table WHERe 1 = 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
>
>

Numerous Numeric Fields to 1 Numeric Field in New Table

Are there any routines out there that will automatically convert a table (A)
with numerous numeric fields to a new table (B) with just one numeric field.
Thus the number of records in the table (B) would be the number or records
in A mutliplied by the number of numeric fields.
Thanks in advanceHi
I occasionally concatenate field names from sysColumn rows (by object) in
order to save myself the typing (in sprocs, etc.). Don't see any reason why
you couldn't add the values in your case (if this is what you're trying to
do).
example:
CREATE PROCEDURE dbo.sp_tablecolumns
@.object_id varchar(100)
AS
DECLARE @.FldCat1 VARCHAR(8000)
SET @.FldCat1=''
SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
FROM sysColumns with (NOLOCK)
WHERE id = object_id(@.object_id)
ORDER BY sysColumns.colorder
PRINT @.FldCat1
GO
usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
rob
"Joe" wrote:

> Are there any routines out there that will automatically convert a table (
A)
> with numerous numeric fields to a new table (B) with just one numeric fiel
d.
> Thus the number of records in the table (B) would be the number or record
s
> in A mutliplied by the number of numeric fields.
> Thanks in advance
>
>|||sorry, I get what you're saying now.
u can do the same kind of thing I mentioned in my 1st post. u'd have to find
the numeric columns from table A first, loop thru A (by row and then columns
)
and do the insert (into B) in the loops.
u could write a generic routine starting with the code I posted.
rob
"RobKaratzas" wrote:
> Hi
> I occasionally concatenate field names from sysColumn rows (by object) in
> order to save myself the typing (in sprocs, etc.). Don't see any reason wh
y
> you couldn't add the values in your case (if this is what you're trying to
> do).
> example:
> CREATE PROCEDURE dbo.sp_tablecolumns
> @.object_id varchar(100)
> AS
> DECLARE @.FldCat1 VARCHAR(8000)
> SET @.FldCat1=''
> SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
> FROM sysColumns with (NOLOCK)
> WHERE id = object_id(@.object_id)
> ORDER BY sysColumns.colorder
> PRINT @.FldCat1
> GO
> usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
> rob
> "Joe" wrote:
>|||> I occasionally concatenate field names from sysColumn rows (by
object) in
> order to save myself the typing (in sprocs, etc.).
In SQL2000 Query Analyzer can do that automatically for you. Just drag
the Columns node from the Object Browser into the editing window. In
7.0 and earlier you don't have Object Browser so the method you
described may be useful. Not sure what it has to do with Joe's question
though :-)
David Portas
SQL Server MVP
--|||What I usually do to copy the column names is
Setting the Result to Text option and then do a
SELECT * FROM table WHERe 1 = 0
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103200824.858097.15460@.z14g2000cwz.googlegroups.com...
> object) in
> In SQL2000 Query Analyzer can do that automatically for you. Just drag
> the Columns node from the Object Browser into the editing window. In
> 7.0 and earlier you don't have Object Browser so the method you
> described may be useful. Not sure what it has to do with Joe's question
> though :-)
> --
> David Portas
> SQL Server MVP
> --
>|||thanks Roji
I did misread the original post.
But in order to handle this problem with a generic solution, you're going to
require some means to programatically gather what these numerous columns are
for whatever Table A has.
Rob
"Roji. P. Thomas" wrote:

> What I usually do to copy the column names is
> Setting the Result to Text option and then do a
> SELECT * FROM table WHERe 1 = 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1103200824.858097.15460@.z14g2000cwz.googlegroups.com...
>
>

Numerous Numeric Fields to 1 Numeric Field in New Table

Are there any routines out there that will automatically convert a table (A)
with numerous numeric fields to a new table (B) with just one numeric field.
Thus the number of records in the table (B) would be the number or records
in A mutliplied by the number of numeric fields.
Thanks in advance
Hi
I occasionally concatenate field names from sysColumn rows (by object) in
order to save myself the typing (in sprocs, etc.). Don't see any reason why
you couldn't add the values in your case (if this is what you're trying to
do).
example:
CREATE PROCEDURE dbo.sp_tablecolumns
@.object_id varchar(100)
AS
DECLARE @.FldCat1 VARCHAR(8000)
SET @.FldCat1=''
SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
FROM sysColumns with (NOLOCK)
WHERE id = object_id(@.object_id)
ORDER BY sysColumns.colorder
PRINT @.FldCat1
GO
usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
rob
"Joe" wrote:

> Are there any routines out there that will automatically convert a table (A)
> with numerous numeric fields to a new table (B) with just one numeric field.
> Thus the number of records in the table (B) would be the number or records
> in A mutliplied by the number of numeric fields.
> Thanks in advance
>
>
|||sorry, I get what you're saying now.
u can do the same kind of thing I mentioned in my 1st post. u'd have to find
the numeric columns from table A first, loop thru A (by row and then columns)
and do the insert (into B) in the loops.
u could write a generic routine starting with the code I posted.
rob
"RobKaratzas" wrote:
[vbcol=seagreen]
> Hi
> I occasionally concatenate field names from sysColumn rows (by object) in
> order to save myself the typing (in sprocs, etc.). Don't see any reason why
> you couldn't add the values in your case (if this is what you're trying to
> do).
> example:
> CREATE PROCEDURE dbo.sp_tablecolumns
> @.object_id varchar(100)
> AS
> DECLARE @.FldCat1 VARCHAR(8000)
> SET @.FldCat1=''
> SELECT @.FldCat1=@.FldCat1+(sysColumns.name + char(44))
> FROM sysColumns with (NOLOCK)
> WHERE id = object_id(@.object_id)
> ORDER BY sysColumns.colorder
> PRINT @.FldCat1
> GO
> usage: dbo.sp_tablecolumns 'MyTableNamethatIwantfieldsfor'
> rob
> "Joe" wrote:
|||> I occasionally concatenate field names from sysColumn rows (by
object) in
> order to save myself the typing (in sprocs, etc.).
In SQL2000 Query Analyzer can do that automatically for you. Just drag
the Columns node from the Object Browser into the editing window. In
7.0 and earlier you don't have Object Browser so the method you
described may be useful. Not sure what it has to do with Joe's question
though :-)
David Portas
SQL Server MVP
|||What I usually do to copy the column names is
Setting the Result to Text option and then do a
SELECT * FROM table WHERe 1 = 0
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
> object) in
> In SQL2000 Query Analyzer can do that automatically for you. Just drag
> the Columns node from the Object Browser into the editing window. In
> 7.0 and earlier you don't have Object Browser so the method you
> described may be useful. Not sure what it has to do with Joe's question
> though :-)
> --
> David Portas
> SQL Server MVP
> --
>
|||thanks Roji
I did misread the original post.
But in order to handle this problem with a generic solution, you're going to
require some means to programatically gather what these numerous columns are
for whatever Table A has.
Rob
"Roji. P. Thomas" wrote:

> What I usually do to copy the column names is
> Setting the Result to Text option and then do a
> SELECT * FROM table WHERe 1 = 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1103200824.858097.15460@.z14g2000cwz.googlegro ups.com...
>
>

Numeric to real type

Hi Everyone,

I hope this is not a silly one. Well, I have two tables - table 1 destination, table 2 source. Data is being imported from table 1 to table 2. For ages this has been working fine but now we get an error which just says can not insert value 1 (.i.e. user defined error message from error handling). I have been trying to run this whole thing bit by bit and detect the actual error but for some reason it doesn't show any error but only on live.

I could detect just one potential issue that value 1 in table1 is numeric defined with size 13 precision (20,10) and in table 2 it is defined as real. But I am not sure if conversion from numeric to real can be any problem as the SP didn't show me when I tried to run manually.

Can anyone double on my assumption that this might be a potential problem which doesn't seem to occur on test server as it might be because the data are different in test and live.

Thanks a ton in advance,
Sree

Quote:

Originally Posted by sreemati

Hi Everyone,

I hope this is not a silly one. Well, I have two tables - table 1 destination, table 2 source. Data is being imported from table 1 to table 2. For ages this has been working fine but now we get an error which just says can not insert value 1 (.i.e. user defined error message from error handling). I have been trying to run this whole thing bit by bit and detect the actual error but for some reason it doesn't show any error but only on live.

I could detect just one potential issue that value 1 in table1 is numeric defined with size 13 precision (20,10) and in table 2 it is defined as real. But I am not sure if conversion from numeric to real can be any problem as the SP didn't show me when I tried to run manually.

Can anyone double on my assumption that this might be a potential problem which doesn't seem to occur on test server as it might be because the data are different in test and live.

Thanks a ton in advance,
Sree

Well that should not be a problem because 1 is just a single digit number.
We would not be able to find the real reason with the help of user defined message.
Is it possible to find the original error message and POST it for my reference?|||

Quote:

Originally Posted by amitpatel66

Well that should not be a problem because 1 is just a single digit number.
We would not be able to find the real reason with the help of user defined message.
Is it possible to find the original error message and POST it for my reference?

Hi

I did not mean the Number 1 by value1, I just meant it in reference to a field value1. Yes, I know that I am trying to re-create the error and get the error message, hopefully I get lucky soon and than I will post it.

Thanks,
Sree|||

Quote:

Originally Posted by sreemati

Hi

I did not mean the Number 1 by value1, I just meant it in reference to a field value1. Yes, I know that I am trying to re-create the error and get the error message, hopefully I get lucky soon and than I will post it.

Thanks,
Sree

Then I assume there should be some problem with the data.
Ok, find out the exact error message and POST here for my reference!!

numeric overflow

Hi,
I'm trying to load a table from a dts job that selects all the records from
sybase table and imports it into an equivalent MS SQL 2000 table.
I have done this successfully for 16 tables.
However for one table I get the following error on insert:
Error at source for row number 38014.
Errors encountered for this task: 1
[DataDirect][ODBC Sybase Wire Protocol Driver]Numeric Overflow. Erro
r in
column 16.
[DataDirect][ODBC Sybase Wire Protocol Driver]Error in row.
I have checked the column definitions for the table and as far as I can
determine both the Sybase and MS table match. Column 16 is of money data
type. Any ideas causing the error?
Thanks!!gracie wrote:
> Hi,
> I'm trying to load a table from a dts job that selects all the
> records from sybase table and imports it into an equivalent MS SQL
> 2000 table.
> I have done this successfully for 16 tables.
> However for one table I get the following error on insert:
> Error at source for row number 38014.
> Errors encountered for this task: 1
> [DataDirect][ODBC Sybase Wire Protocol Driver]Numeric Overflow. Er
ror
> in column 16.
> [DataDirect][ODBC Sybase Wire Protocol Driver]Error in row.
> I have checked the column definitions for the table and as far as I
> can determine both the Sybase and MS table match. Column 16 is of
> money data type. Any ideas causing the error?
> Thanks!!
Have you looked at the data in row 38014 to see what is in the table?
David Gugick
Imceda Software
www.imceda.comsql

numeric overflow

Hi,
I'm trying to load a table from a dts job that selects all the records from
sybase table and imports it into an equivalent MS SQL 2000 table.
I have done this successfully for 16 tables.
However for one table I get the following error on insert:
Error at source for row number 38014.
Errors encountered for this task: 1
[DataDirect][ODBC Sybase Wire Protocol Driver]Numeric Overflow. Error in
column 16.
[DataDirect][ODBC Sybase Wire Protocol Driver]Error in row.
I have checked the column definitions for the table and as far as I can
determine both the Sybase and MS table match. Column 16 is of money data
type. Any ideas causing the error?
Thanks!!gracie wrote:
> Hi,
> I'm trying to load a table from a dts job that selects all the
> records from sybase table and imports it into an equivalent MS SQL
> 2000 table.
> I have done this successfully for 16 tables.
> However for one table I get the following error on insert:
> Error at source for row number 38014.
> Errors encountered for this task: 1
> [DataDirect][ODBC Sybase Wire Protocol Driver]Numeric Overflow. Error
> in column 16.
> [DataDirect][ODBC Sybase Wire Protocol Driver]Error in row.
> I have checked the column definitions for the table and as far as I
> can determine both the Sybase and MS table match. Column 16 is of
> money data type. Any ideas causing the error?
> Thanks!!
Have you looked at the data in row 38014 to see what is in the table?
--
David Gugick
Imceda Software
www.imceda.com

numeric overflow

Hi,
I'm trying to load a table from a dts job that selects all the records from
sybase table and imports it into an equivalent MS SQL 2000 table.
I have done this successfully for 16 tables.
However for one table I get the following error on insert:
Error at source for row number 38014.
Errors encountered for this task: 1
[DataDirect][ODBC Sybase Wire Protocol Driver]Numeric Overflow. Error in
column 16.
[DataDirect][ODBC Sybase Wire Protocol Driver]Error in row.
I have checked the column definitions for the table and as far as I can
determine both the Sybase and MS table match. Column 16 is of money data
type. Any ideas causing the error?
Thanks!!
gracie wrote:
> Hi,
> I'm trying to load a table from a dts job that selects all the
> records from sybase table and imports it into an equivalent MS SQL
> 2000 table.
> I have done this successfully for 16 tables.
> However for one table I get the following error on insert:
> Error at source for row number 38014.
> Errors encountered for this task: 1
> [DataDirect][ODBC Sybase Wire Protocol Driver]Numeric Overflow. Error
> in column 16.
> [DataDirect][ODBC Sybase Wire Protocol Driver]Error in row.
> I have checked the column definitions for the table and as far as I
> can determine both the Sybase and MS table match. Column 16 is of
> money data type. Any ideas causing the error?
> Thanks!!
Have you looked at the data in row 38014 to see what is in the table?
David Gugick
Imceda Software
www.imceda.com

Numeric data column and insert from ASP.NET

I am trying to insert some values into a table where the column is of the data type "numeric". The insert works fine.Update does not work.

Update BUT_BREAKDOWN_PCT SET BDP_EFFORT_BREAKDOWN_PCT=0.15 WHERE BDP_BREAKDOWN_ID =1 AND BDP_PHASE_ID = 3 AND BDP_START_EFF_DT = '12/31/2004'

BDP_EFFORT_BREAKDOWN_PCT is a numeric column with a size 5 (4,3)

When I do the updatedirectly from QA, it works fine.

I was googling it and read a KB article saying it's a problem with Service Pack of SQL Server 2000. If it is, then the query should not work even from QA...isn't it?

Anyone had this problem before? Please help.

In SQL Server 2000 you can change the data type to Decimal and your problem will go away but in SQL Server 7.0 Numeric was more stable than Decimal from my experience. Hope this helps.|||If it works OK in Query Analyzer (works fine for me, too), and it isnot working in your application, then your application is not sendingthe exact same information.

What results are you seeing? An error message? Unexpected data in theBDP_EFFORT_BREAKDOWN_PCTcolumn? No update at all?

I am suspecting that you are not seeing any update at all, which meansthat your WHERE condition is not being met. And since you seem tohave a datetime column in your WHERE condition, I would guess furtherthat that is where your problem lies. (Datetime columnstrip up a lot of people.)

If you are having difficulty troubleshooting this further, let us see your code and maybe we can point out the problem.

numeric conversion

I have a source table with a varchar field like 0000005467.
My target table has a numeric 18,2 column which I am trying to populate with 54.67 but it keeps rounding the last 2 digits to ZERO's.
Any ideas?
-KTry something likes that:

declare @.str varchar(25)
select @.str='00005667'
select @.str=left(@.str,len(@.str)-2)+'.'+right(@.str,2)
select convert(decimal(10,2),@.str),@.str|||The data field numeric(18,2) will support 2 decimal places but it doesn't assume that a value stored or converted has a 2 decimal value. So you need to tell the system that the converted value has a 2 decimal value, divide by 100.

declare @.x varchar(15)

set @.x = '0000005467'

select convert(numeric(18,2),@.x)/100

Numer comparison

Hi,

I have table with four fields ID, Date1, Date2,Date3,Date4. Dates are in numeric farmat like 20070927. How can i get largest date (from date1 to date4) on each row. Please suggest query.

Thanks

One posible solution is to unpivot those columns and then use "group by".

Code Block

declare @.t table (

[id] int not null identity unique,

date1 int,

date2 int,

date3 int,

date4 int

)

insert into @.t(date1, date2, date3, date4) values(20070305, 20070401, 20070928, 20070704)

insert into @.t(date1, date2, date3, date4) values(20070315, 20070411, 20070228, 20070714)

select

[id],

max(dt) max_dt

from

@.t as t

unpivot

(

dt

for date_col in ([date1], [date2], [date3], [date4])

) as unpvt

group by

[id]

go

AMB|||

Bukhari wrote:

Hi,

I have table with four fields ID, Date1, Date2,Date3,Date4. Dates are in numeric farmat like 20070927. How can i get largest date (from date1 to date4) on each row. Please suggest query.

Thanks

A function like Oracle's GREATEST would come in handy here

What about

Code Block

CREATE TABLE tableA
( id INT IDENTITY PRIMARY KEY
, date1 INT
, date2 INT
, date3 INT
, date4 INT)
INSERT INTO tableA SELECT 20070927, 20070928, 20070926, 20080927
INSERT INTO tableA SELECT 20070927, 20071028, 20070926, 20060927

SELECT id, MAX(iDate) AS maxdate
FROM
(SELECT id,
CASE i
WHEN 1 THEN date1
WHEN 2 THEN date2
WHEN 3 THEN date3
WHEN 4 THEN date4
END AS iDate
FROM tableA
CROSS JOIN
(SELECT 1 AS i
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4) T) T
GROUP BY id
SELECT id,
CASE
WHEN date1 > date2 AND date1 > date3 AND date1 > date4 THEN date1
WHEN date2 > date3 AND date2 > date4 THEN date2
WHEN date3 > date4 THEN date3
ELSE date4 END AS maxdate
FROM tableA
DROP TABLE tableA

id maxdate
-- --
1 20080927
2 20071028

(2 row(s) affected)

id maxdate
-- --
1 20080927
2 20071028

(2 row(s) affected)

|||

First off, I just have to note that this is not really a relational design. Each of these dates should be stored in it's own row, and this is an easy SQL problem.

Second, the best relational solution I have found uses a subquery with a UNION ALL operator to "normalize" the date values for an aggregate:

create table dates
(
dateKey int primary key ,
date1 int,
date2 int,
date3 int,
date4 int
)

insert into dates
select 1, 20070101,20070102,20070103,20070104
union
select 2, 20070201,20070202,20070203,20070104
go

select dateKey,
(select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) as minDate
from dates

It is really quite fast, but if you need more performance, consider using a CLR function to do this. I have found that this technique is about the same speed with you have a few dates, or less than millions of rows to deal with (and it is certainly easier to code and deploy...)

|||

Excilent solution. Thanks very much hunchback and frank.

I will go with CASE--WHEN solution.

|||I was really struggling with it. Thanks a lot Louis.|||

I just want to mention that what the solution using "case" is doing is unpivoting. Well, all posted solutions are unpivoting some how.

AMB

|||

Can i specify criteria in Where clause? Where maxdate less than last 60 days.

Where maxdate < CONVERT(VARCHAR, DATEADD(day, - 60, GETDATE()), 112)

but maxdate is not column so it generate error, how can i use CASE WHEN statement in WHERE clause?

thanks

|||

Yes, but you need to use a derived table:

select *
from ( --your original query goes here
select dateKey,
(select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) as minDate
from dates) as dateQuery
where minDate > 20070101

or CTE if you are using 2005:

with dateQuery as (

select dateKey,
(select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) as minDate
from dates )

select *
from dateQuery
where minDate > 20070101

Or you can just repeat the subquery:

select dateKey,
(select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) as minDate
from dates
where (select min(date)
from (select date1 as date union all select date2 union all
select date3 union all select date4) as dates) > 20070101

But that is the least astetically pleasing...

|||

Add a "having" clause to the solution I suggested.

Code Block

declare @.t table (

[id] int not null identity unique,

date1 int,

date2 int,

date3 int,

date4 int

)

insert into @.t(date1, date2, date3, date4) values(20070305, 20070401, 20070928, 20070704)

insert into @.t(date1, date2, date3, date4) values(20070315, 20070411, 20070228, 20070114)

select

[id],

max(dt) max_dt

from

@.t as t

unpivot

(

dt

for date_col in ([date1], [date2], [date3], [date4])

) as unpvt

group by

[id]

having

max(dt) < CONVERT(CHAR(8), DATEADD(day, - 60, GETDATE()), 112)

go

AMB|||

Great Stuff, Thanks

|||

Hunchback, nice stuff. I would say that both yours and mine have merits, mine is a bit more clean in one way (I am doing a rowwise operation, so I could include most any kind of operation in mine, whereas yours includes an aggregate that would be limiting, but definitely does what was called for, and if you were doing multiple operations only on the date data, might be a lot faster.

|||

Hi Louis,

Well, I would prefer to follow what you mentioned (normalizing) in your first post to this thread.

Thanks,

Alejandro Mesa

P.S. I am the guy who asked you to sign my book (I was staying at the "Red Lion") during this year summit. I hope to see you again, to ask you to sign also the one about DMVs & DMFs.

|||

>>Well, I would prefer to follow what you mentioned (normalizing) in your first post to this thread.<<

Amen to that, but I have had to do this sort of operation for other reasons. In our etl, we join a bunch of tables together and then get the earliest update date from all of the joined rows so we can include/exclude rows for ETL.

>>also the one about DMVs & DMFs.<<

I definitely remember. I just hope I ever finish that book

|||
Just to throw another approach into the mix.

create function Util.fn_max( @.p1 int, @.p2 int)
returns int
as
begin
return (select case when @.p1>=@.p2 then @.p1 else @.p2 end as int)
end

Select Util.fn_max(Util.fn_max(Util.fn_max(date1, date2), date3), date4)

Change util to the schema that you want, aka dbo. I keep things like this in a Util schema.

numbering rows of unordered table?

create table t (colA int, colB char(1), colC int)
insert into t(colB, colC) Values('C', 3)
insert into t(colB, colC) Values('C', 1)
insert into t(colB, colC) Values('C', 4)
insert into t(colB, colC) Values('C', 2)
insert into t(colB, colC) Values('A', 4)
insert into t(colB, colC) Values('A', 1)
insert into t(colB, colC) Values('A', 3)
insert into t(colB, colC) Values('A', 2)
insert into t(colB, colC) Values('B', 2)
insert into t(colB, colC) Values('B', 3)
insert into t(colB, colC) Values('B', 4)
insert into t(colB, colC) Values('B', 1)
so colA of table t contains all nulls right now.
Select * from t
NULL C 3
NULL C 1
NULL C 4
NULL C 2
NULL A 4
NULL A 1
NULL A 3
NULL A 2
NULL B 2
NULL B 3
NULL B 4
NULL B 1
I need to number each row of table t so it looks like this
Select * from t Order By colB, colC
1 A 1
2 A 2
3 A 3
4 A 4
5 B 1
6 B 2
7 B 3
8 B 4
9 C 1
10 C 2
11 C 3
12 C 4
In my actual app Table t already exists with colA = null
and colB and colC as above and thousands of rows. Thus,
to say
Update t set colA = 1 Where colB = 'A' And colC = '1'
Update t set colA = 2 Where colB = 'A' And colC = '2'
Update t set colA = 3 Where colB = 'A' And colC = '3'
...
Update t set colA = 9 Where colB = 'C' And colC = '1'
Update t set colA = 10 Where colB = 'C' And colC = '2'
...
is clearly is not the way to go. I humbly request if
someone could show me how to number the rows with T-sql
the correct way. My problem is that I don't know how to
increment the seed number and how to apply it to the
desired order. I am thinking a while loop, but what flag
to use to stop the loop? How to order the rows?
Thanks,
RonTry,
select
count(*) as colA,
a.colB,
a.colC
from
t as a
inner join
t as b
on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
group by
a.colB,
a.colC
order by
1
go
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Ron" wrote:

> create table t (colA int, colB char(1), colC int)
> insert into t(colB, colC) Values('C', 3)
> insert into t(colB, colC) Values('C', 1)
> insert into t(colB, colC) Values('C', 4)
> insert into t(colB, colC) Values('C', 2)
> insert into t(colB, colC) Values('A', 4)
> insert into t(colB, colC) Values('A', 1)
> insert into t(colB, colC) Values('A', 3)
> insert into t(colB, colC) Values('A', 2)
> insert into t(colB, colC) Values('B', 2)
> insert into t(colB, colC) Values('B', 3)
> insert into t(colB, colC) Values('B', 4)
> insert into t(colB, colC) Values('B', 1)
> so colA of table t contains all nulls right now.
> Select * from t
> NULL C 3
> NULL C 1
> NULL C 4
> NULL C 2
> NULL A 4
> NULL A 1
> NULL A 3
> NULL A 2
> NULL B 2
> NULL B 3
> NULL B 4
> NULL B 1
> I need to number each row of table t so it looks like this
> Select * from t Order By colB, colC
> 1 A 1
> 2 A 2
> 3 A 3
> 4 A 4
> 5 B 1
> 6 B 2
> 7 B 3
> 8 B 4
> 9 C 1
> 10 C 2
> 11 C 3
> 12 C 4
> In my actual app Table t already exists with colA = null
> and colB and colC as above and thousands of rows. Thus,
> to say
> Update t set colA = 1 Where colB = 'A' And colC = '1'
> Update t set colA = 2 Where colB = 'A' And colC = '2'
> Update t set colA = 3 Where colB = 'A' And colC = '3'
> ...
> Update t set colA = 9 Where colB = 'C' And colC = '1'
> Update t set colA = 10 Where colB = 'C' And colC = '2'
> ...
> is clearly is not the way to go. I humbly request if
> someone could show me how to number the rows with T-sql
> the correct way. My problem is that I don't know how to
> increment the seed number and how to apply it to the
> desired order. I am thinking a while loop, but what flag
> to use to stop the loop? How to order the rows?
> Thanks,
> Ron
>|||here is the update.
update
t
set
colA = (select count(*) from t as a where t.colB + ltrim(t.colC) >= a.colB
+ ltrim(a.colC))
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> count(*) as colA,
> a.colB,
> a.colC
> from
> t as a
> inner join
> t as b
> on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
> group by
> a.colB,
> a.colC
> order by
> 1
> go
> How to dynamically number rows in a SELECT Statement
> http://support.microsoft.com/defaul...kb;en-us;186133
>
> AMB
>
> "Ron" wrote:
>|||Thanks very much for your reply. I guess the trick was in
the self join. I took this one step further and performed
an update (as I need to hardcode these numbers):
update t set t.colA = t2.colA
From t Join
(select count(*) as colA, a.colB, a.colC from t as a inner
join t as b on a.colB + ltrim(a.colC) >= b.colB + ltrim
(b.colC) group by a.colB, a.colC) t2
on t.colB = t2.colB and t.colC = t2.colC
Question: someone advised me that using joins in an
update statement is not correct. But this Update
statement accomplished what I needed. Any comments
appreciated.
Thanks again for your help.
Ron

>--Original Message--
>Try,
>select
> count(*) as colA,
> a.colB,
> a.colC
>from
> t as a
> inner join
> t as b
> on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
>group by
> a.colB,
> a.colC
>order by
> 1
>go
>How to dynamically number rows in a SELECT Statement
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;186133
>
>AMB|||Read my last post.
AMB
"Ron" wrote:

> Thanks very much for your reply. I guess the trick was in
> the self join. I took this one step further and performed
> an update (as I need to hardcode these numbers):
> update t set t.colA = t2.colA
> From t Join
> (select count(*) as colA, a.colB, a.colC from t as a inner
> join t as b on a.colB + ltrim(a.colC) >= b.colB + ltrim
> (b.colC) group by a.colB, a.colC) t2
> on t.colB = t2.colB and t.colC = t2.colC
> Question: someone advised me that using joins in an
> update statement is not correct. But this Update
> statement accomplished what I needed. Any comments
> appreciated.
> Thanks again for your help.
> Ron
>
> us;186133
>|||Thanks again for this correction.

>--Original Message--
>here is the update.
>update
> t
>set
> colA = (select count(*) from t as a where t.colB +
ltrim(t.colC) >= a.colB
>+ ltrim(a.colC))
>go
>
>AMB
>"Alejandro Mesa" wrote:
>
us;186133
this
null
Thus,
sql
to
flag
>.
>

numbering rows

How do i number the rows in the table? My data does not have any unique
values if that helpsThere is a RowNumber(<scope>) function, where <scope> is a string that
identifies a data region, dataset or grouping if you need that context. For
just a simple running row total, use RowNumber(Nothing). You can also use
it for visual effects, and the most frequent example of this is to create
"green bar" reports by setting the background colour of a table row with the
expression:
=iif(RowNumber(Nothing) Mod 2, "Green", "White")
Cheers, Mark
"Marvin" <Marvin@.discussions.microsoft.com> wrote in message
news:25E67CAB-76C0-450A-B4DE-87788DD19E80@.microsoft.com...
> How do i number the rows in the table? My data does not have any unique
> values if that helps

Numbering in SQL

I have table with 10-20 rows with field P6 which is empty. I want to
update numbers to P6 starting 1 and increasing by 1. I suppose it is
done by triggers but I don't know how to do that. Help :-)is this as 1 off or every time you INSERT a record?

--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

<lemes_m@.yahoo.com> wrote in message
news:1148037597.406952.273140@.j33g2000cwa.googlegr oups.com...
> I have table with 10-20 rows with field P6 which is empty. I want to
> update numbers to P6 starting 1 and increasing by 1. I suppose it is
> done by triggers but I don't know how to do that. Help :-)|||Make the column a identity value, this way it is incremented with every
insert:

int IDENTITY (1, 1)|||lemes_m@.yahoo.com (lemes_m@.yahoo.com) writes:
> I have table with 10-20 rows with field P6 which is empty. I want to
> update numbers to P6 starting 1 and increasing by 1. I suppose it is
> done by triggers but I don't know how to do that. Help :-)

Without further knowledge about the table it is difficult to give
advice. And if the rest of the data is not unique, it's getting sort
of ugly.

For a one-off you could do:

DECLARE @.i int
SELECT @.i = 1
-- SET ROWCOUNT 1 Use this on SQL 2000.
WHILE EXISTS (SELECT * FROM tbl WHERE P6 IS NULL)
BEGIN
UPDATE /* TOP(1) */ tbl -- Remove comment for SQL 2005.
SET P6 = @.i

SELECT @.i = @.i + 1
END
-- SET ROWCOUNT 0 again, for SQL 2000.

But I would not like to see this code in a trigger.

--
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|||>> have table with 10-20 rows with field [sic] P6 which is empty [sic]. <<

You have NEVER written SQL before, have you? Columsn are not fields
and we have NULLs, not the empty spreadsheet cells you assume. TOTALLY
WRONG MINDSET!

>> I want to update numbers to P6 starting 1 and increasing by 1. <<

That is a SEQUENTAL MAGNETIC TAPE FILE and you are tryignto write
1950's code in SQL! It has northing whatsoever to do with RDBMS.
Tables have no ordering by definition. This is soooooo wrong ...|||Geeesh!!! You can insert a set at a time, so adding one to a previous
value makes no sense. Doesn't anyone go to RDBMS classes any more?|||>> Make the column a identity value, this way it is incremented with every insert: <<

Always go for the proprietary and most non-relational kludge? LET'S
FINBD OUT WHAT HE IS REALLY TRYIGN TO DO BEFORE WE POST ANYTHING ELSE.

Okay, lemes_m@.yahoo.com , why do you want to destroy the relational
model? What is your business goal?|||Hi Lemes,

Check out http://blogs.msdn.com/sqlcat/archiv.../10/572848.aspx it has
good examples of how to do this.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

<lemes_m@.yahoo.com> wrote in message
news:1148037597.406952.273140@.j33g2000cwa.googlegr oups.com...
>I have table with 10-20 rows with field P6 which is empty. I want to
> update numbers to P6 starting 1 and increasing by 1. I suppose it is
> done by triggers but I don't know how to do that. Help :-)

Numbering groups of rows?

create table t (colA int, colB char(1), colC int)
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
update t set colc =
(select count(*) from t as a where t.colb >= a.colb)
yields
NULL C 12
NULL C 12
NULL C 12
NULL C 12
NULL A 4
NULL A 4
NULL A 4
NULL A 4
NULL B 8
NULL B 8
NULL B 8
NULL B 8
how can I make it yield
NULL C 3
NULL C 3
NULL C 3
NULL C 3
NULL A 1
NULL A 1
NULL A 1
NULL A 1
NULL B 2
NULL B 2
NULL B 2
NULL B 2
Thanks,
Ronupdate t set colc =
(select count(DISTINCT a.colb) from t as a where t.colb >= a.colb)
Jacco Schalkwijk
SQL Server MVP
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:01b401c50fb9$5a806ee0$a501280a@.phx.gbl...
> create table t (colA int, colB char(1), colC int)
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> update t set colc =
> (select count(*) from t as a where t.colb >= a.colb)
> yields
> NULL C 12
> NULL C 12
> NULL C 12
> NULL C 12
> NULL A 4
> NULL A 4
> NULL A 4
> NULL A 4
> NULL B 8
> NULL B 8
> NULL B 8
> NULL B 8
> how can I make it yield
> NULL C 3
> NULL C 3
> NULL C 3
> NULL C 3
> NULL A 1
> NULL A 1
> NULL A 1
> NULL A 1
> NULL B 2
> NULL B 2
> NULL B 2
> NULL B 2
> Thanks,
> Ron
>|||On Thu, 10 Feb 2005 13:42:01 -0800, Ron wrote:

>update t set colc =
>(select count(*) from t as a where t.colb >= a.colb)
>yields
(snip)
>how can I make it yield
(snip)
Hi Ron,
Better not to store this information at all - you'll find yourself
constantly fighting to keep the rankingf column current after each
modification to the underlying data. It's better to drop the column from
the table and create a view to calculate it.
If you MUST do it in an update, try
update t set colc =
(select count(distinct a.colb) from t as a where t.colb >= a.colb)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks very much. That worked well. But I also tried
using this in a select statement as follows for ranking
the values A, B, C
select a.colB, count(distinct a.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
yielded this ranking
A 1
B 1
C 1
without the Distinct keyword I get this ranking
A 16
B 32
C 48
But I would like to get a ranking as follows
A 1
B 2
C 3
I ask this because I am trying to understand the sql logic
to achieve these results. Hopefully, after I do enough of
these kinds of queries I will get the idea how they work.
May I ask how I could achieve the ranking from result3?
Thanks again,
Ron

>--Original Message--
> update t set colc =
>(select count(DISTINCT a.colb) from t as a where t.colb
>= a.colb)
>
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01b401c50fb9$5a806ee0$a501280a@.phx.gbl...
>
>.
>|||Ron,
Try count(distinct b.colb) instead of count(distinct a.colb). I suspect
that's what you had in mind.
Steve Kass
Drew University
Ron wrote:
>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
>yielded this ranking
>A 1
>B 1
>C 1
>without the Distinct keyword I get this ranking
>A 16
>B 32
>C 48
>But I would like to get a ranking as follows
>A 1
>B 2
>C 3
>I ask this because I am trying to understand the sql logic
>to achieve these results. Hopefully, after I do enough of
>these kinds of queries I will get the idea how they work.
>May I ask how I could achieve the ranking from result3?
>Thanks again,
>Ron
>
>
>message
>|||On Thu, 10 Feb 2005 14:21:58 -0800, Ron wrote:

>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
Hi Ron,
Try this one instead:
select a.colB, count(distinct b.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
(Note: only one letter weas changed!!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks very much. I am also trying to figure out how to
rank A, B, C
select a.colB, count(distinct a.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
yielded this ranking
A 1
B 1
C 1
without the Distinct keyword I get this ranking
A 16
B 32
C 48
But I would like to get a ranking as follows
A 1
B 2
C 3
May I ask how I could achieve the ranking from result3?
This way, as you say, I don't really store the ranks, just
retrieve them dynamically.
Thanks again,
Ron

>--Original Message--
>On Thu, 10 Feb 2005 13:42:01 -0800, Ron wrote:
>
>(snip)
>(snip)
>Hi Ron,
>Better not to store this information at all - you'll find
yourself
>constantly fighting to keep the rankingf column current
after each
>modification to the underlying data. It's better to drop
the column from
>the table and create a view to calculate it.
>If you MUST do it in an update, try
>update t set colc =
>(select count(distinct a.colb) from t as a where t.colb
>= a.colb)
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>|||Thanks all. I am sort of starting to get the idea. But I
wonder if I could belabor this thing one more notch:
Instead of using distinct is it possible to plant a group
by query in there? Pseudocode here:
select a.colB, count(select a.colb from a group by a.colb)
as colC from t as a inner join t as b
on a.colB >= b.colB group by a.colB
Again, I just ask because I don't really know all the
rules for t-sql, let alone the tricks. I am guessing that
t-sql does not allow Selects inside of Count(..)
Thanks again,
Ron

>--Original Message--
>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
>yielded this ranking
>A 1
>B 1
>C 1
>without the Distinct keyword I get this ranking
>A 16
>B 32
>C 48
>But I would like to get a ranking as follows
>A 1
>B 2
>C 3
>I ask this because I am trying to understand the sql
logic
>to achieve these results. Hopefully, after I do enough
of
>these kinds of queries I will get the idea how they
work.
>May I ask how I could achieve the ranking from result3?
>Thanks again,
>Ron
>
>message
>.
>|||On Thu, 10 Feb 2005 14:45:11 -0800, Ron wrote:

>Thanks all. I am sort of starting to get the idea. But I
>wonder if I could belabor this thing one more notch:
>Instead of using distinct is it possible to plant a group
>by query in there? Pseudocode here:
>select a.colB, count(select a.colb from a group by a.colb)
>as colC from t as a inner join t as b
>on a.colB >= b.colB group by a.colB
Hi Ron,
This code won't work. I'm sure there is some way to do this with a group
by in the subquery, but it's not trivial and it'll be more complex than
the version with DISTINCT that I suggested.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Instead of using distinct is it possible to plant a group by query
in there? <<
This will use a GROUP BY and get you a bit more information in the VIEW
aggregate functions. I am not sure that there is any advantage. .
CREATE TABLE Foobar (letter CHAR(1) NOT NULL);
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
CREATE VIEW FoobarReport (letter, occurs, place)
AS
SELECT F1.letter, COUNT(*),
(SELECT COUNT (DISTINCT F2.letter)
FROM Foobar AS F2
WHERE F2.letter <= F1.letter)
FROM Foobar AS F1
GROUP BY F1.letter;

Numbering each row in my resultset

In my query I want to succesively number each record I retrieve from a table.
Example: select <...>, state from tbl_states (<...> is what I am asking for)
Col1 Col2
1 Ohio
2 New York
3 California
4 Texas
I have seen some solutions using count(*), group by and joins to achieve
this. However I have images that I retrieve in columns 2 (instead of the
states) and group by is not compliant with image datatype.
I think Oracle has something called rownum that does what I want, but I
don't know how to do this with SQL Server.
Anybody knows?
Why not add the row number client-side? That can work out much more
efficient than any of the potential SQL solutions. Here's one SQL
alternative:
SELECT
(SELECT COUNT(*)
FROM YourTable
WHERE key_col <= T.key_col) AS col1,
col2
FROM YourTable AS T
David Portas
SQL Server MVP
|||Why make the query treat each row individually? The client has to do that
anyway, so it is a much more appropriate place to keep a running count.
http://www.aspfaq.com/
(Reverse address to reply.)
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:D2BEB8E4-C710-4027-A1EF-1D8166A6DB10@.microsoft.com...
> In my query I want to succesively number each record I retrieve from a
table.
> Example: select <...>, state from tbl_states (<...> is what I am asking
for)
> Col1 Col2
> --
> 1 Ohio
> 2 New York
> 3 California
> 4 Texas
> I have seen some solutions using count(*), group by and joins to achieve
> this. However I have images that I retrieve in columns 2 (instead of the
> states) and group by is not compliant with image datatype.
> I think Oracle has something called rownum that does what I want, but I
> don't know how to do this with SQL Server.
> Anybody knows?
>

Numbering each row in my resultset

In my query I want to succesively number each record I retrieve from a table.
Example: select <...>, state from tbl_states (<...> is what I am asking for)
Col1 Col2
--
1 Ohio
2 New York
3 California
4 Texas
I have seen some solutions using count(*), group by and joins to achieve
this. However I have images that I retrieve in columns 2 (instead of the
states) and group by is not compliant with image datatype.
I think Oracle has something called rownum that does what I want, but I
don't know how to do this with SQL Server.
Anybody knows?Why not add the row number client-side? That can work out much more
efficient than any of the potential SQL solutions. Here's one SQL
alternative:
SELECT
(SELECT COUNT(*)
FROM YourTable
WHERE key_col <= T.key_col) AS col1,
col2
FROM YourTable AS T
--
David Portas
SQL Server MVP
--|||Why make the query treat each row individually? The client has to do that
anyway, so it is a much more appropriate place to keep a running count.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:D2BEB8E4-C710-4027-A1EF-1D8166A6DB10@.microsoft.com...
> In my query I want to succesively number each record I retrieve from a
table.
> Example: select <...>, state from tbl_states (<...> is what I am asking
for)
> Col1 Col2
> --
> 1 Ohio
> 2 New York
> 3 California
> 4 Texas
> I have seen some solutions using count(*), group by and joins to achieve
> this. However I have images that I retrieve in columns 2 (instead of the
> states) and group by is not compliant with image datatype.
> I think Oracle has something called rownum that does what I want, but I
> don't know how to do this with SQL Server.
> Anybody knows?
>

Numbering each row in my resultset

In my query I want to succesively number each record I retrieve from a table
.
Example: select <...>, state from tbl_states (<...> is what I am asking for
)
Col1 Col2
--
1 Ohio
2 New York
3 California
4 Texas
I have seen some solutions using count(*), group by and joins to achieve
this. However I have images that I retrieve in columns 2 (instead of the
states) and group by is not compliant with image datatype.
I think Oracle has something called rownum that does what I want, but I
don't know how to do this with SQL Server.
Anybody knows?Why not add the row number client-side? That can work out much more
efficient than any of the potential SQL solutions. Here's one SQL
alternative:
SELECT
(SELECT COUNT(*)
FROM YourTable
WHERE key_col <= T.key_col) AS col1,
col2
FROM YourTable AS T
David Portas
SQL Server MVP
--|||Why make the query treat each row individually? The client has to do that
anyway, so it is a much more appropriate place to keep a running count.
http://www.aspfaq.com/
(Reverse address to reply.)
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:D2BEB8E4-C710-4027-A1EF-1D8166A6DB10@.microsoft.com...
> In my query I want to succesively number each record I retrieve from a
table.
> Example: select <...>, state from tbl_states (<...> is what I am asking
for)
> Col1 Col2
> --
> 1 Ohio
> 2 New York
> 3 California
> 4 Texas
> I have seen some solutions using count(*), group by and joins to achieve
> this. However I have images that I retrieve in columns 2 (instead of the
> states) and group by is not compliant with image datatype.
> I think Oracle has something called rownum that does what I want, but I
> don't know how to do this with SQL Server.
> Anybody knows?
>