Re: Insert Into with a row number.

Nice, Thanks for the info.

A couple of things though

row_number()'s help says it starts at 1. I take it I would have to add max(LineNumber) from the OrderDetails some how. I'd work on it but the next ones a killer.

I unfortunately have no control over the SQL Server version. This is an add on to an existing application that supports SQL Server 2000 and 2005 and is currently working on 2008 compatibility.

So. the 2005 requirement is a problem. Any other ideas?


Erland Sommarskog wrote:
Guy Dreger (isgdre@xxxxxxxxxxx) writes:
Hi, I have a table that contains order information, each row contains information about a detail item that's been order but the key is OrderID and LineNumber.

I'm trying to insert information into the table from another table but other table does not have any type of line number information. Also there is now hard value for the number of rows already in the order detail table.

Is there a way to use “Insert into....” for this problem? I.e

Insert into OrderDetails od (OrderID, Item, Description, LineNumber)
select OrderID, '!NOTE!', Description, od.@ROWID() from OrderNotes
If you are on SQL 2005 or later (hint! it is always a good idea to say which version of SQL Server you are using!), you can use the
row_number function:

row_number() OVER (PARTITION BY OrderID ORDER BY somecolumn)



.

Relevant Pages

  • Re: group by help
    ... Columnist, SQL Server Professional ... "Alejandro Mesa" wrote in message ... > OrderID ... > SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Unable to insert record in table
    ... INSERT INTO ORDERS (OrderID) VALUES ... (error code -2147217887). ... So I opened SQL Server Managment studio and opened the table. ... insert a new record using the same OrderId, I first got a timeout error and ...
    (microsoft.public.sqlserver.programming)
  • RE: ISNULL function does not work as expected on calculated field
    ... SQL Server Might Return Incorrect Results When You Use an Outer ... SELECT OrderID, (select HasStatus from constantvalue) AS HasStatus ... > handling of the ISNULL function. ...
    (microsoft.public.sqlserver.programming)
  • Re: Problem in Select Query
    ... Wayne Snyder, MCDBA, SQL Server MVP ... > OrderId OrderItems ... > Actually I have written a query that will convert OrderItems to select ... > Using this query i got the sql statemens like this ...
    (microsoft.public.sqlserver.programming)
  • Re: Insert Into with a row number.
    ... information about a detail item that's been order but the key is OrderID ... Insert into OrderDetails od (OrderID, Item, Description, LineNumber) ... If you are on SQL 2005 or later (hint! ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
Loading