Tricky insert query

[aaron1234nz]

I am trying to insert new rows into a table but there are a few tricky things I have not been able to get my head around.

1. I need to insert a unique ID in each row.
2. I need to insert rows based on information from another table.

I am using sql server 2005.

I have come up with some code, but it does not work. I get an "Error in list of function arguments: 'FROM' not recognized." message.

Can someone tell me if I am on the right track with this or if I need to take a different approach?

insert into activity 
(seqn, id, activity_type, transaction_date)
VALUES 
( 
coalesce(MAX(seqn), 0) + 1 FROM activity WITH (UPDLOCK),
(select id FROM subscriptions WHERE (PRODUCT_CODE = 'Product1') AND (PAID_THRU = '2007-12-31')) ,
 'DUES', '2008-12-31')

[ck9663]

I am trying to insert new rows into a table but there are a few tricky things I have not been able to get my head around.

1. I need to insert a unique ID in each row.
2. I need to insert rows based on information from another table.

I am using sql server 2005.

I have come up with some code, but it does not work. I get an "Error in list of function arguments: 'FROM' not recognized." message.

Can someone tell me if I am on the right track with this or if I need to take a different approach?

insert into activity 
(seqn, id, activity_type, transaction_date)
VALUES 
( 
coalesce(MAX(seqn), 0) + 1 FROM activity WITH (UPDLOCK),
(select id FROM subscriptions WHERE (PRODUCT_CODE = 'Product1') AND (PAID_THRU = '2007-12-31')) ,
 'DUES', '2008-12-31')

i don't think this (coalesce(MAX(s eqn), 0) + 1) will result in a unique number. if you have control over activity table, might as well add an IDENTITY column. it might not ensure sequential-lity, but it ensures uniqueness. if it's really necessary to do this, get the max(seqn) from activity first then add rownumber of your subscription using row number. for faster processing, i'd recommend the IDENTITY column technique. then you just have to do the insert. something like:

insert into activity 
(id, activity_type, transaction_date)
VALUES 
(select id, 'DUES',   '2008-12-31' FROM subscriptions WHERE (PRODUCT_CODE = 'Product1') AND (PAID_THRU = '2007-12-31'))

i just notice, your activity_type is always hardcoded to "DUES" and transaction_dat e is always hardcoded as well? this not necessarily wrong, but you might want to take a second look....

this is a pseudo-code. but i hope you got the idea

-- CK

[TiBoT]

I am trying to insert new rows into a table but there are a few tricky things I have not been able to get my head around.

1. I need to insert a unique ID in each row.
2. I need to insert rows based on information from another table.

I am using sql server 2005.

I have come up with some code, but it does not work. I get an "Error in list of function arguments: 'FROM' not recognized." message.

Can someone tell me if I am on the right track with this or if I need to take a different approach?

insert into activity 
(seqn, id, activity_type, transaction_date)
VALUES 
( 
coalesce(MAX(seqn), 0) + 1 FROM activity WITH (UPDLOCK),
(select id FROM subscriptions WHERE (PRODUCT_CODE = 'Product1') AND (PAID_THRU = '2007-12-31')) ,
 'DUES', '2008-12-31')

Did you resolve this issue on your insert?

You can try the Counter table for the LAST_VALUE for Activity.