Re: Can it be done in SQL
Toby Denbow wrote:
>
> I want to insert some rows into a detail table. The question is how can
> I, in SQL, create line numbers for the rows being inserted without
> hardcoding them?
> I could probalby do it with a stored procedure but would prefer using SQL.
>
> For example I want the_line_number to be incremented for each row os that
> line_no is a valid line_no.
>
> EXAMPLE:
> insert into line_no, col1, col2
> select the_line_number, table.col1, table.col2 from table where col2 = "a">
> Please reply via email if you can.
>
> Thanks,
> Toby Denbow
> tdenbow@mathworks.com
>
> == Toby Denbow ========================= tdenbow@mathworks.com ==
> The MathWorks, Inc. info@mathworks.com
> 24 Prime Park Way http://www.mathworks.com
> Natick, MA 01760-1500 ftp.mathworks.com
> ==== Tel: 508-647-7243 ==========================================
Hi Toby !
If I understand your problem right, the easiest
solution is to implement the line number field as a
SERIAL. The values are assigned automatically by
the database server when a row is inserted. By
default, the starting number is 1, but you are free
to choose any other integer. The highest possible
number is 2147483647. I hope your table won't have
that much rows. If you insert a new record the next
number for the SERIAL field is computed using the
formula MAX(SERIAL_column)+1. This is still valid,
if you change the initial value later (by using
alter table).
I hope this helps.
---------------------------------------------------
Dirk Schmidtpott S I E M E N S
EMail: schmd@dbm212.pdb.sni.de =============
Phone: 0049-5251-8-14922 N I X D O R F
Fax : 0049-5251-8-17905
The above statements and opinions are my own and do
not necessarily represent those of my employer.
---------------------------------------------------