RowNumber?

I want to add a record number to each record in my report.
I don't have any grouping on my report, so not sure if using
the rownumber() function works in that case'
C.The following should work:
=RowNumber(Nothing)sql

rownum alternate in MS-SQL

I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.
I want to get 100 rows onward to one particular data ... how can i ?This doesn't sound like a good idea... (using rownum)
Please enlighten me with the SQL you used in mySQL?
Also, what version of SQL Server are you using?|||SELECT TOP n

(Being deprecated for modification statements in SS 2008+)
SET ROWCOUNT = n

(SS 2005)
OVER () clause|||I want to get 100 rows from particular record and onward.

You need more than just TOP, don't you?
I don't like the use of rownum - I'm sure there's a better way to get what the OP wants!|||You need more than just TOP, don't you?yes, a WHERE condition, too

oh, and an ORDER BY clause, without which TOP is meaningless

:)|||I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.

I want to get 100 rows onward to one particular data ... how can i ?

ummmmmmmmmmmmmm

what particular row|||what particular rowthe one specified by the WHERE condition|||ummmmmmmmmmmmmm

what particular rowThat row, right there near the middle of my screen.

-PatP|||I'm feeling better now|||"Standard" for SQL 2005:

with cte
as
(select row_number () over (order by name) as num, object_id, name
from master.sys.tables)

select *
from cte
where num >= 4

Rownum

Hi folks,
SELECT * FROM mytable
100 rows returned.
Can i get a rownum column for each record; i.e. if 100 records returned; rownum order 1,2,3....100 along with the each record position.
is it possible without using cursor?
Howdy!see http://www.dbforums.com/t1058224.html|||Assuming that you have atleast one primary key or at least a unique constraint:

Select Count(RowTable.UniqueField) as RowNumber, Mytable.Fields
from Mytable
inner join Mytable RowTable on Mytable.UniqueField >= RowTable.UniqueField

Even I had the Same problem. Thanks To Blindman for his help regarding the query.|||Thanx to r937 and blindman! :)|||Assuming that you have atleast one primary key or at least a unique constraint
Also: NULL values won't be counted. In the other thread the values were used as columnnames, so NULL is quite unlikely. Not sure about myTable.sql

Rowid-insert a record

IN Oracle we can Use Rowid.. I need Sql Server equivalent and
IN Oracle when we insert a record and then select it is display in the last
records.
I Need same in sql server how to make same features.
Selva,
Possibly IDENTITY (CREATE TABLE) or NEWID(). Not sure what the Rowid does
in Oracle.
HTH
Jerry
"Selva" <Selva@.discussions.microsoft.com> wrote in message
news:ED62696B-B651-41FC-B1CC-AF08BFEE3F35@.microsoft.com...
> IN Oracle we can Use Rowid.. I need Sql Server equivalent and
> IN Oracle when we insert a record and then select it is display in the
> last
> records.
> I Need same in sql server how to make same features.
sql

Rowid-insert a record

IN Oracle we can Use Rowid.. I need Sql Server equivalent and
IN Oracle when we insert a record and then select it is display in the last
records.
I Need same in sql server how to make same features.Selva,
Possibly IDENTITY (CREATE TABLE) or NEWID(). Not sure what the Rowid does
in Oracle.
HTH
Jerry
"Selva" <Selva@.discussions.microsoft.com> wrote in message
news:ED62696B-B651-41FC-B1CC-AF08BFEE3F35@.microsoft.com...
> IN Oracle we can Use Rowid.. I need Sql Server equivalent and
> IN Oracle when we insert a record and then select it is display in the
> last
> records.
> I Need same in sql server how to make same features.

Rowid-insert a record

IN Oracle we can Use Rowid.. I need Sql Server equivalent and
IN Oracle when we insert a record and then select it is display in the last
records.
I Need same in sql server how to make same features.Selva,
Possibly IDENTITY (CREATE TABLE) or NEWID(). Not sure what the Rowid does
in Oracle.
HTH
Jerry
"Selva" <Selva@.discussions.microsoft.com> wrote in message
news:ED62696B-B651-41FC-B1CC-AF08BFEE3F35@.microsoft.com...
> IN Oracle we can Use Rowid.. I need Sql Server equivalent and
> IN Oracle when we insert a record and then select it is display in the
> last
> records.
> I Need same in sql server how to make same features.

Row to Column?

All:
Is there a function in MS SQL so that I can archieve the following in
SQL statement? Or do I need to loop through the record set and doing
some array element movement on client side?
Table
Item Color
1 red
1 blue
2 red
2 yellow
3 red
I want the result looks like:
Item Color_red Color_blue Color_yellow
1 red blue null
2 red null yellow
3 red null null
thanks a lotCheck out RAC @.
www.rac4sql.net
A very easy and powerful pivoting/xtab utility.
No sql coding required.|||here's a couple ways, e.g.
declare @.x table (item int, color varchar(6))
insert @.x
select 1, 'red' union all
select 1, 'blue' union all
select 2, 'red' union all
select 2, 'yellow' union all
select 3, 'red'
-- either sql 2000/2005
select item,
max(case when color='red' then 'red' end) as color_red,
max(case when color='blue' then 'blue' end) as color_blue,
max(case when color='yellow' then 'yellow' end) as color_yellow
from @.x
group by item
-- sql2005 only [new PIVOT clause]
-- note in the pivot clause, those are columns, not values (strings)
select item, [red] as color_red, [blue] as color_blue, [yellow] as
color_yellow
from
(select item, color from @.x) x
pivot
(
max(color)
for color in ([red],[blue],[yellow])) as pvt
order by item
rockdale.green@.gmail.com wrote:
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>|||If this has to be done in SQL, you could try outer joining to the table
multiple times, once for each column on your output. If you have the option
of using a tool to process the data outside of SQL, thaqt may be easier.
if tblColor is the name of your table...
select item, rcolor, bcolor, ycolor
from
(Select distinct item from tblColor) as Main
left outer join (select distinct item as ritem, color as rcolor from
tblColor where color = 'red') as red
on item = ritem
left outer join (select distinct item as bitem, color as bcolor from
tblColor where color = 'blue') as blue
on item = bitem
left outer join (select distinct item as yitem, color as ycolor from
tblColor where color = 'yellow') as yellow
on item = yitem
OR, if you dont like inline queries, this is slightly more readable:
select Main.item, red.color, blue.color, yellow.color
from
(Select distinct item from tblColor) as Main
left outer join tblColor as red
on Main.item = red.item and red.color = 'red'
left outer join tblColor as blue
on Main.item = blue.item and blue.color = 'blue'
left outer join tblColor as yellow
on Main.item = yellow.item and yellow.color = 'yellow'
I think you are stuck with the inline query to select the distinct items
regardless. I can't think of a way to avoid this, but you should be able to
make the rest work. The performance on something like this is surprisingly
good, even when you have thousands of rows in your table and 20 collumns.
As you add more columns and more filters on the data it can get a bit out of
hand.
Hope this helps.
<rockdale.green@.gmail.com> wrote in message
news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>|||<rockdale.green@.gmail.com> wrote in message
news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>
Ugly.
Of course, you need to know what possible colors can exist in advance.
set nocount on
create table #col (ident int, col varchar(10))
insert #col select 1, 'red'
insert #col select 1, 'blue'
insert #col select 2, 'red'
insert #col select 2, 'yellow'
insert #col select 3, 'red'
select ident,
case when exists (select C.col from #col C where C.col = 'red' and C.ident =
#col.ident) then 'red' end as color_red,
case when exists (select C.col from #col C where C.col = 'blue' and C.ident
= #col.ident) then 'blue' end as color_blue,
case when exists (select C.col from #col C where C.col = 'yellow' and
C.ident = #col.ident) then 'yellow' end as color_yellow
from #col
group by ident
drop table #col|||If you are using SQL2K5, you can use this:
CREATE TABLE Colors
(Item int not null
,Color varchar(50) not null
)
INSERT INTO COLORS VALUES (1,'red')
INSERT INTO COLORS VALUES (1,'blue')
INSERT INTO COLORS VALUES (2,'red')
INSERT INTO COLORS VALUES (2,'yellow')
INSERT INTO COLORS VALUES (3,'red')
GO
SELECT Item, "red" AS Color_red, "blue" AS Color_blue, "yellow" AS
Color_Yellow
FROM (
SELECT Item, Color
FROM Colors
) p PIVOT (
MIN(Color)
FOR Color IN ("red","blue","yellow")
) pvt
ORDER BY Item
GO
DROP TABLE Colors
GO
If you are using SQL2K or below, then google for SQL Server and PIVOT.
HTH,
Gert-Jan
rockdale.green@.gmail.com wrote:
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:%239zKmuVFGHA.516@.TK2MSFTNGP15.phx.gbl...
>.
> -- sql2005 only [new PIVOT clause]
> -- note in the pivot clause, those are columns, not values (strings)
> select item, [red] as color_red, [blue] as color_blue, [yellow] as
> color_yellow
> from
> (select item, color from @.x) x
> pivot
> (
> max(color)
> for color in ([red],[blue],[yellow])) as pvt
> order by item
And to think you only had to wait 5 years for this!
Are we both being factious? :)
MS is doing its best to keep RAC around.
If you can't top it......:)
www.rac4sql.net|||Some people are dragged to the funny farm,
others JOIN it :)
"Jim Underwood" <james.underwood@.fallonclinic.com> wrote in message
news:%23Y%23HV3VFGHA.984@.tk2msftngp13.phx.gbl...
> If this has to be done in SQL, you could try outer joining to the table
> multiple times, once for each column on your output. If you have the
option
> of using a tool to process the data outside of SQL, thaqt may be easier.
> if tblColor is the name of your table...
> select item, rcolor, bcolor, ycolor
> from
> (Select distinct item from tblColor) as Main
> left outer join (select distinct item as ritem, color as rcolor from
> tblColor where color = 'red') as red
> on item = ritem
> left outer join (select distinct item as bitem, color as bcolor from
> tblColor where color = 'blue') as blue
> on item = bitem
> left outer join (select distinct item as yitem, color as ycolor from
> tblColor where color = 'yellow') as yellow
> on item = yitem
> OR, if you dont like inline queries, this is slightly more readable:
> select Main.item, red.color, blue.color, yellow.color
> from
> (Select distinct item from tblColor) as Main
> left outer join tblColor as red
> on Main.item = red.item and red.color = 'red'
> left outer join tblColor as blue
> on Main.item = blue.item and blue.color = 'blue'
> left outer join tblColor as yellow
> on Main.item = yellow.item and yellow.color = 'yellow'
> I think you are stuck with the inline query to select the distinct items
> regardless. I can't think of a way to avoid this, but you should be able
to
> make the rest work. The performance on something like this is
surprisingly
> good, even when you have thousands of rows in your table and 20 collumns.
> As you add more columns and more filters on the data it can get a bit out
of
> hand.
> Hope this helps.
>
> <rockdale.green@.gmail.com> wrote in message
> news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
>|||Guys, Thanks for all your reply. The pivot table is interesting. I
didnot know that SQL2k5 has this functionality.
But I decided to do this convertion in client side. Because how many
colour we have is stored in another table. I can not hard code say
color_red.. etc. I know that I can dynamic generate the sql statement
in store procedure. But that is kind of overkill.
Anyway, thanks a lot.|||Hi, all
I am back to this problem since now I have more time to test it out.
I guess Raymond's solution is a neat one but it does not solve a more
complex problem like following,
based on cid column then show content in col column.
Notice that I have to add col in my group by clause, but that cause the
problem. THe result is
1 red red NULL
1 blue blue NULL
2 red NULL red
2 yellow NULL yellow
3 red NULL NULL
Which not what I want. Any Idea?
---
set nocount on
create table #col (ident int,cid int, col varchar(10))
insert #col select 1,1, 'red'
insert #col select 1,2, 'blue'
insert #col select 2,1, 'red'
insert #col select 2,3, 'yellow'
insert #col select 3,1, 'red'
select ident,
case when exists (select C.col from #col C where C.cid = 1 and C.ident
=
#col.ident) then col end as color_red,
case when exists (select C.col from #col C where C.cid = 2 and C.ident
= #col.ident) then col end as color_blue,
case when exists (select C.col from #col C where C.cid = 3 and
C.ident = #col.ident) then col end as color_yellow
from #col
group by ident,cid, col
----
drop table #col

row number

how or what function can we use in sql server 2005 or 2000 to return the
corresponding row number of a record in select statement
for example
SELECT name, xxxxx as Number
FROM TableX
xxxxx - is the function or keyword that returns the corresponding row number
of the select statement.
the result set could be..
Name Number
John Doe 0
Jane Doe 1
0, 1 are the corresponding row number..
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787http://www.aspfaq.com/2427

> John Doe 0
> Jane Doe 1|||Jose,
In SQL Server 2000, you can derive row numbers using sub query like
use pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
SQL Server 2005 has an inbuilt ROW_NUMBER function.
SELECT ROW_NUMBER() OVER (ORDER BY job_desc ASC) as rownum, * FROM jobs
For more info see
http://toponewithties.blogspot.com/...es.blogspot.com
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||Using a identity field you obtain it. If you haven't I think exists a
internal rowid or something like that which provide us the row number.
see you,
"Jose G. de Jesus Jr MCP, MCDBA" wrote:

> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row numb
er
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||In SQL Server 2005, look up the ROW_NUMBER() function.
<Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||> If you haven't I think exists a
> internal rowid or something like that
There are internal row identifiers but these are not exposed to you, so you
can't use them in display.
Typically, the best way to present row numbers is to tack them on in the
presentation layer, since that's the only place where you *have to* loop
through and handle every single row anyway. Forcing the row numbers to be
generated in the database puts unnecessary strain there and turns a simple
query into either a subquery that is evaluated per row, or a mess with
pre-population into a temp table or table variable.|||>Using a identity field you obtain it
That behavior is not guaranteed.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30C1DECC-AF8C-4662-8EB3-3A1F0E3CBE26@.microsoft.com...
> Using a identity field you obtain it. If you haven't I think exists a
> internal rowid or something like that which provide us the row number.
> see you,
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>|||http://support.microsoft.com/defaul...b;EN-US;q186133
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||If one of the columns in query is unique, the following calculates a
sequence number for each row in a resultset:
SELECT
name,
(select count(*) from TableX as x where x.name > TableX.name) as Number
FROM
TableX
ORDER BY
name
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||I disagree. While it is true that using the IDENTITY function in a SELECT
INTO with an ORDER BY clause doesn't guarantee that the order of the
IDENTITY values match the order specified in the ORDER BY clause, using an
INSERT...SELECT...ORDER BY to insert into a temporary table or table
variable with an IDENTITY column will always work correctly. See KB273586.
An obvious improvement, however, is the ROW_NUMBER() function in SQL Server
2005, which eliminates the need for the self-join or the intermediate temp
table or table variable.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:OEtZ0dKqFHA.3192@.TK2MSFTNGP10.phx.gbl...
> That behavior is not guaranteed.
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:30C1DECC-AF8C-4662-8EB3-3A1F0E3CBE26@.microsoft.com...
the
>

ROW ID

Can any one guid me like I have a billions of records and I want to see only
10th record and I want to fetch the 10th record through rowid not the Unique
primary key.
Let's say Table primary key id start from
6380,
7066,
7067,
7131,
7896,
8042
and so on ...
If I wanna fetch third record which ID = 7067 so I have to give 7067 but I
want to give input parameter = 3 then it will show me the third record, I am
talking about row id... is there any thing in SQL Server or any alternate
way through which I can solve my problem.
Thanks
Hi,
There is no row id concept in SQL Server.
Thanks
Hari
SQL Server Mvp
"Roy" <roy@.hotmail.com> wrote in message
news:OKMKgoqdFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Can any one guid me like I have a billions of records and I want to see
> only
> 10th record and I want to fetch the 10th record through rowid not the
> Unique
> primary key.
> Let's say Table primary key id start from
> 6380,
> 7066,
> 7067,
> 7131,
> 7896,
> 8042
> and so on ...
> If I wanna fetch third record which ID = 7067 so I have to give 7067 but I
> want to give input parameter = 3 then it will show me the third record, I
> am
> talking about row id... is there any thing in SQL Server or any alternate
> way through which I can solve my problem.
> Thanks
>
>
|||Just to add there will be one in SQL2005
Andrew J. Kelly SQL MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:efaBvgudFHA.640@.tk2msftngp13.phx.gbl...
> Hi,
> There is no row id concept in SQL Server.
> Thanks
> Hari
> SQL Server Mvp
> "Roy" <roy@.hotmail.com> wrote in message
> news:OKMKgoqdFHA.1384@.TK2MSFTNGP09.phx.gbl...
>

Row by Row Copy

I'm writing a stored procedure that requires I duplicate records through a loop, one record at a time (required because I need to execute SCOPE_IDENTITY() logic on each insertion). But each row has 40 or so columns, making my stored procedure ridiculously full of long declaration lists. So I want to either:

1) Learn a way to auto-insert the column declarations into my code without having to type them all by hand, or...

2) Learn a way to represent the whole row for insertion, without having to specify each column specifically.

The latter solution would be the most elegant, but I'll take what I can get...

how about this?

--

IDENTITY (Function)

Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE.

Syntax

IDENTITY ( data_type [ , seed , increment ] ) AS column_name

Arguments

data_type

Is the data type of the identity column. Valid data types for an identity column are any data types of the integer data type category (except for the bit data type), or decimal data type.

seed

Is the value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value. If neither seed nor increment is specified, both default to 1.

increment

Is the increment to add to the seed value for successive rows in the table.

column_name

Is the name of the column that is to be inserted into the new table.

Return Types

Returns the same as data_type.

Remarks

Because this function creates a column in a table, a name for the column must be specified in the select list in one of these ways:

--(1)

SELECT IDENTITY(int, 1,1) AS ID_Num

INTO NewTable

FROM OldTable

--(2)

SELECT ID_Num = IDENTITY(int, 1, 1)

INTO NewTable

FROM OldTable

Examples

This example inserts all rows from the employee table from the pubs database into a new table called employees. The IDENTITY function is used to start identification numbers at 100 instead of 1 in the employees table.

USE pubs

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'employees')

DROP TABLE employees

GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'

SELECT emp_id AS emp_num,

fname AS first,

minit AS middle,

lname AS last,

IDENTITY(smallint, 100, 1) AS job_num,

job_lvl AS job_level,

pub_id,

hire_date

INTO employees

FROM employee

GO

USE pubs

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'

|||

What version of SQL Server are you using? In SQL Server 2005, you can use OUTPUT clause in INSERT statement to get the generated identity values for multiple rows easily. See the link below for a post that describes one example:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

You can do the same in older versions by dumping the key values into a temporary table created in the SP from the trigger and accessing it from outside. This will still be much more efficient than what you are doing and run many times faster. And you kinda lost me on why you ned the declare list etc. You can do the looping without cursors.

|||

I'm using 2005. I need more than just the newly generated identity values...I also need the original identity values from the records being copied. Like:

old_ID new_ID

4 227

63 228

65 229

I need this "pairing" of old and new, because I am copying both "parents" and "children" of 1-to-many relationships. To fetch and copy the children, I need to know the parent's "copy from" old id (to get the children) and the parent's "copy to" new id (to correlate the new copies of children to the new parent ids).

So I don't really care to explicitly "handle" each and every column of the parent - I don't care what the contents of those columns are - I just want to copy them. So it is really annoying to have an "INTO" clause where I've got a comma delimited list of 40 columns...which I only ever intend on copying without ever inspecting. The only thing I am "inspecting" is the values of the identity (primary key) of the parent.

Perhaps SQL Server 2005 has a "do what I mean" stored proc I can execute...

|||

Ok. This is kind of tricky with OUTPUT clause and INSERT because you can only reference INSERTED table columns / expressions. You can however do it easily using a technique like below:

create table T ( i int not null identity primary key, j int null references T(i));
insert into T (j) values(null) ;
insert into T (j) values(scope_identity());
select * from T;

declare @.t table(i int not null);
set transaction isolation level serializable;
begin tran;
insert into T (j)
output inserted.i into @.t( i)
select j from T order by i;

select t2.i as old_i, t1.i as new_i
from (select i, ROW_NUMBER() OVER(order by i) from @.t) as t1(i, seq)
join (select i, ROW_NUMBER() OVER(order by i) from T) as t2(i, seq)
on t1.seq = t2.seq;

commit;

select * from T;

drop table T;

The trick is to insert the rows into the table in a particular order (you can choose multiple columns if you want). In the code above, the ORDER BY in the SELECT statement of INSERT ensures that the generated identity values are in the same order. You can then sequence the old and newly generated values & join based on the sequence. The serializable transaction is however required since it is not easy to protect the identity values generation in case of concurrent inserts to the table. This technique will work for you if this operation is an expensive one (replicating portions of tree) and you are doing it infrequently. This set-based approach should be much simpler than what you have but there is nothing in the SQL language to simplify column lists. You have to specify those you want to SELECT or use. That is how the language is defined.

|||

Wow, that is quite the solution. I think I need just a wee more help...since I am so rusty/inexperienced with T/SQL.

Firstly, you have a "select * from T" statement near the top of your script that doesn't seem to do anything. Is it just a piece of debug/sanity check output - or is it necessary for the solution?

Secondly, it seems to me that when I turn my attention towards copying records in the child table - I still might need to resort to explicit looping while using your "old-to-new" correlation query. That is, after I copy the child's records, I will need to change the foreign key on each child row, to reflect the new foreign keys in the parent. There are perhaps two ways I can think of to avoid such explicit looping:

1) If the child table also has an identity column (primary key), then I can repeat the magical "old-to-new" correlation query for the child table's insert as well. Then, after the insert (copy) on the child is done, I would do an update on the child's newly inserted rows - predicated on a 3-way inner join between the parent's "old-to-new" recordset, the child's "old-to-new" recordset, and the child itself. Phew!!!

2) After duplicating the relevant child records in the child, I would need to use a facility in SQL Server 2005 that allows me to perform an update only on the newly added child records. Perhaps this just means using the "inserted" virtual table again. As such, I perceive a simpler variation of proposal (1) above. That is, I "save" the contents of the "inserted" rows into a temp table. Then I perform an update predicated on a 3-way inner join between the parent's "old-to-new," the "inserted" temp table, and the child itself. This still supposes that the child has its own identity (primary key) column.

Even if you concur with either of the above strategies, is there an alternative you prefer?

|||The main logic is the part between the declare table and the commit tran. The rest of the code was just to show the rows. I didn't quite understand the part about copying child rows. Please post a simple DDL and data like in my example. And also the expected results so it will be easier to suggest a modified solution or show how my previous example can be used.|||

I made the effort to implement the rest of what I needed, and discovered that producing copies of the child records was nothing as difficult as I anticipated. I did not need a unique primary key in the children, and I did not need looping. Just an insert statement predicated on a simple join. Thats all.

So, in short, you have shown me how to produce all the copies I needed, without using looping structures. Therefore I will mark your primary response as the answer.

Row by Row Copy

I'm writing a stored procedure that requires I duplicate records through a loop, one record at a time (required because I need to execute SCOPE_IDENTITY() logic on each insertion). But each row has 40 or so columns, making my stored procedure ridiculously full of long declaration lists. So I want to either:

1) Learn a way to auto-insert the column declarations into my code without having to type them all by hand, or...

2) Learn a way to represent the whole row for insertion, without having to specify each column specifically.

The latter solution would be the most elegant, but I'll take what I can get...

how about this?

--

IDENTITY (Function)

Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE.

Syntax

IDENTITY ( data_type [ , seed , increment ] ) AS column_name

Arguments

data_type

Is the data type of the identity column. Valid data types for an identity column are any data types of the integer data type category (except for the bit data type), or decimal data type.

seed

Is the value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value. If neither seed nor increment is specified, both default to 1.

increment

Is the increment to add to the seed value for successive rows in the table.

column_name

Is the name of the column that is to be inserted into the new table.

Return Types

Returns the same as data_type.

Remarks

Because this function creates a column in a table, a name for the column must be specified in the select list in one of these ways:

--(1)

SELECT IDENTITY(int, 1,1) AS ID_Num

INTO NewTable

FROM OldTable

--(2)

SELECT ID_Num = IDENTITY(int, 1, 1)

INTO NewTable

FROM OldTable

Examples

This example inserts all rows from the employee table from the pubs database into a new table called employees. The IDENTITY function is used to start identification numbers at 100 instead of 1 in the employees table.

USE pubs

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'employees')

DROP TABLE employees

GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'

SELECT emp_id AS emp_num,

fname AS first,

minit AS middle,

lname AS last,

IDENTITY(smallint, 100, 1) AS job_num,

job_lvl AS job_level,

pub_id,

hire_date

INTO employees

FROM employee

GO

USE pubs

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'

|||

What version of SQL Server are you using? In SQL Server 2005, you can use OUTPUT clause in INSERT statement to get the generated identity values for multiple rows easily. See the link below for a post that describes one example:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

You can do the same in older versions by dumping the key values into a temporary table created in the SP from the trigger and accessing it from outside. This will still be much more efficient than what you are doing and run many times faster. And you kinda lost me on why you ned the declare list etc. You can do the looping without cursors.

|||

I'm using 2005. I need more than just the newly generated identity values...I also need the original identity values from the records being copied. Like:

old_ID new_ID

4 227

63 228

65 229

I need this "pairing" of old and new, because I am copying both "parents" and "children" of 1-to-many relationships. To fetch and copy the children, I need to know the parent's "copy from" old id (to get the children) and the parent's "copy to" new id (to correlate the new copies of children to the new parent ids).

So I don't really care to explicitly "handle" each and every column of the parent - I don't care what the contents of those columns are - I just want to copy them. So it is really annoying to have an "INTO" clause where I've got a comma delimited list of 40 columns...which I only ever intend on copying without ever inspecting. The only thing I am "inspecting" is the values of the identity (primary key) of the parent.

Perhaps SQL Server 2005 has a "do what I mean" stored proc I can execute...

|||

Ok. This is kind of tricky with OUTPUT clause and INSERT because you can only reference INSERTED table columns / expressions. You can however do it easily using a technique like below:

create table T ( i int not null identity primary key, j int null references T(i));
insert into T (j) values(null) ;
insert into T (j) values(scope_identity());
select * from T;

declare @.t table(i int not null);
set transaction isolation level serializable;
begin tran;
insert into T (j)
output inserted.i into @.t( i)
select j from T order by i;

select t2.i as old_i, t1.i as new_i
from (select i, ROW_NUMBER() OVER(order by i) from @.t) as t1(i, seq)
join (select i, ROW_NUMBER() OVER(order by i) from T) as t2(i, seq)
on t1.seq = t2.seq;

commit;

select * from T;

drop table T;

The trick is to insert the rows into the table in a particular order (you can choose multiple columns if you want). In the code above, the ORDER BY in the SELECT statement of INSERT ensures that the generated identity values are in the same order. You can then sequence the old and newly generated values & join based on the sequence. The serializable transaction is however required since it is not easy to protect the identity values generation in case of concurrent inserts to the table. This technique will work for you if this operation is an expensive one (replicating portions of tree) and you are doing it infrequently. This set-based approach should be much simpler than what you have but there is nothing in the SQL language to simplify column lists. You have to specify those you want to SELECT or use. That is how the language is defined.

|||

Wow, that is quite the solution. I think I need just a wee more help...since I am so rusty/inexperienced with T/SQL.

Firstly, you have a "select * from T" statement near the top of your script that doesn't seem to do anything. Is it just a piece of debug/sanity check output - or is it necessary for the solution?

Secondly, it seems to me that when I turn my attention towards copying records in the child table - I still might need to resort to explicit looping while using your "old-to-new" correlation query. That is, after I copy the child's records, I will need to change the foreign key on each child row, to reflect the new foreign keys in the parent. There are perhaps two ways I can think of to avoid such explicit looping:

1) If the child table also has an identity column (primary key), then I can repeat the magical "old-to-new" correlation query for the child table's insert as well. Then, after the insert (copy) on the child is done, I would do an update on the child's newly inserted rows - predicated on a 3-way inner join between the parent's "old-to-new" recordset, the child's "old-to-new" recordset, and the child itself. Phew!!!

2) After duplicating the relevant child records in the child, I would need to use a facility in SQL Server 2005 that allows me to perform an update only on the newly added child records. Perhaps this just means using the "inserted" virtual table again. As such, I perceive a simpler variation of proposal (1) above. That is, I "save" the contents of the "inserted" rows into a temp table. Then I perform an update predicated on a 3-way inner join between the parent's "old-to-new," the "inserted" temp table, and the child itself. This still supposes that the child has its own identity (primary key) column.

Even if you concur with either of the above strategies, is there an alternative you prefer?

|||The main logic is the part between the declare table and the commit tran. The rest of the code was just to show the rows. I didn't quite understand the part about copying child rows. Please post a simple DDL and data like in my example. And also the expected results so it will be easier to suggest a modified solution or show how my previous example can be used.|||

I made the effort to implement the rest of what I needed, and discovered that producing copies of the child records was nothing as difficult as I anticipated. I did not need a unique primary key in the children, and I did not need looping. Just an insert statement predicated on a simple join. Thats all.

So, in short, you have shown me how to produce all the copies I needed, without using looping structures. Therefore I will mark your primary response as the answer.

Rounding real number

Dear All,
I've a problem when input real number. If I key in 6.5, on query
analyzer, the record show 6.499999999 and so.
Does anybody known the solution?
Thanks very much
MichaelDon't use float or real. These are "approximate" numerical datatype (when wo
rking with 10 base
systems as we humans tend to do). Use numeric or decimal instead.
http://www.aspfaq.com/show.asp?id=2477
http://www.aspfaq.com/show.asp?id=2503
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer,
the record show
> 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael|||Yap
What is your SQL Server's version
DECLARE @.d DECIMAL(18,1),@.w REAL
SET @.d=6.5
SET @.w=6.5
SELECT @.d,@.w
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer,
> the record show 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael

Rounding real number

Dear All,
I've a problem when input real number. If I key in 6.5, on query
analyzer, the record show 6.499999999 and so.
Does anybody known the solution?
Thanks very much
Michael
Don't use float or real. These are "approximate" numerical datatype (when working with 10 base
systems as we humans tend to do). Use numeric or decimal instead.
http://www.aspfaq.com/show.asp?id=2477
http://www.aspfaq.com/show.asp?id=2503
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer, the record show
> 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael
|||Yap
What is your SQL Server's version
DECLARE @.d DECIMAL(18,1),@.w REAL
SET @.d=6.5
SET @.w=6.5
SELECT @.d,@.w
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer,
> the record show 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael

Rounding real number

Dear All,
I've a problem when input real number. If I key in 6.5, on query
analyzer, the record show 6.499999999 and so.
Does anybody known the solution?
Thanks very much
MichaelDon't use float or real. These are "approximate" numerical datatype (when working with 10 base
systems as we humans tend to do). Use numeric or decimal instead.
http://www.aspfaq.com/show.asp?id=2477
http://www.aspfaq.com/show.asp?id=2503
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer, the record show
> 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael|||Yap
What is your SQL Server's version
DECLARE @.d DECIMAL(18,1),@.w REAL
SET @.d=6.5
SET @.w=6.5
SELECT @.d,@.w
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer,
> the record show 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael

Rounding problems

I have rounding problems when editing or inserting a new record in float type fields.
e.g. I have a cursor running an agrregate SQL statement. I have a calculated field Sum(DFactor*Cost). DFactor gets values -1,1 and values of Cost in the table have 2 digits. I get these values in a variable e.g. @.FCost. Then I round @.FCost=Round(@.FCost,2).
When I try to inert this value to a new record again I'using Round(@.FCost,2).
However in a lot of records a lot of digits are stored.
I have the same probelm when trying to insert values from MSAccess by ODBC. Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.
What is the best practise in order to solve this problem?
Regards,
ManolisIf you are using a FLOAT column to store data of type MONEY, that's a problem. If you are using a FLOAT column to store data of type DECIMAL (x, 2), that's also a problem. Is your underlying problem one of datatype, not actually rounding?

-PatP|||Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.The result will have decimals. You need this: CLng(@.FCost*100/100)