Autonumber when inserting

Hi i have a problem that i can′t solve!
I have three tables last_id, customer, prospect. The "last_id" table have
one colum that stores the last used id number (int), customer tables stores
info about the customer and a "last_id" number and the prospect stores new
customers!
I want to make an insert into customer table from the prospect table and ad
one extra column with the last_id number +1
insert into customer(name,adress,l_id)
select p_name,p_adress,l_id
from prospect,last_id
i don′t want alter the table and use identity
i can make i work with a cursor, is there any other way to do it'Hi
CREATE TABLE #Test
(
col INT
)
INSERT INTO #Test VALUES (1)
GO
INSERT INTO #Test SELECT COALESCE(MAX(col),0)+1 FROM #Test
GO
SELECT * FROM #test
DROP TABLE #Test
"LeSurfer" <LeSurfer@.discussions.microsoft.com> wrote in message
news:8C74D3BF-12D2-4FED-B07D-5070867310B4@.microsoft.com...
> Hi i have a problem that i cant solve!
> I have three tables last_id, customer, prospect. The "last_id" table have
> one colum that stores the last used id number (int), customer tables
stores
> info about the customer and a "last_id" number and the prospect stores new
> customers!
> I want to make an insert into customer table from the prospect table and
ad
> one extra column with the last_id number +1
> insert into customer(name,adress,l_id)
> select p_name,p_adress,l_id
> from prospect,last_id
> i dont want alter the table and use identity
> i can make i work with a cursor, is there any other way to do it'
>|||I couldn′t make it work, i tried this
select coalesce(max(l_id),0)+1,name
from ct_contact cross join kundfil
this is the error message:
Server: Msg 8118, Level 16, State 1, Line 1
Column 'Kundfil.kund' is invalid in the select list because it is not
contained in an aggregate function and there is no GROUP BY clause.
any ideas on what i could try'
"Uri Dimant" wrote:

> Hi
> CREATE TABLE #Test
> (
> col INT
> )
> INSERT INTO #Test VALUES (1)
> GO
> INSERT INTO #Test SELECT COALESCE(MAX(col),0)+1 FROM #Test
> GO
> SELECT * FROM #test
> DROP TABLE #Test
>
> "LeSurfer" <LeSurfer@.discussions.microsoft.com> wrote in message
> news:8C74D3BF-12D2-4FED-B07D-5070867310B4@.microsoft.com...
> stores
> ad
>
>|||Hi
Add this column 'Kundfil.kund' into GROUP BY clause
"LeSurfer" <LeSurfer@.discussions.microsoft.com> wrote in message
news:D5A79FA4-6E93-4EB7-870D-F754B26DF287@.microsoft.com...
> I couldnt make it work, i tried this
> select coalesce(max(l_id),0)+1,name
> from ct_contact cross join kundfil
> this is the error message:
> Server: Msg 8118, Level 16, State 1, Line 1
> Column 'Kundfil.kund' is invalid in the select list because it is not
> contained in an aggregate function and there is no GROUP BY clause.
> any ideas on what i could try'
> "Uri Dimant" wrote:
>
have
new
and