467,144 Members | 1,173 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,144 developers. It's quick & easy.

Tricky insert query

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?

Expand|Select|Wrap|Line Numbers
  1. insert into activity 
  2. (seqn, id, activity_type, transaction_date)
  3. VALUES 
  4. coalesce(MAX(seqn), 0) + 1 FROM activity WITH (UPDLOCK),
  5. (select id FROM subscriptions WHERE (PRODUCT_CODE = 'Product1') AND (PAID_THRU = '2007-12-31')) ,
  6.  'DUES', '2008-12-31') 
  7.  
Jan 22 '08 #1
  • viewed: 2228
Share:
2 Replies
ck9663
Expert 2GB
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?

Expand|Select|Wrap|Line Numbers
  1. insert into activity 
  2. (seqn, id, activity_type, transaction_date)
  3. VALUES 
  4. coalesce(MAX(seqn), 0) + 1 FROM activity WITH (UPDLOCK),
  5. (select id FROM subscriptions WHERE (PRODUCT_CODE = 'Product1') AND (PAID_THRU = '2007-12-31')) ,
  6.  'DUES', '2008-12-31') 
  7.  
i don't think this (coalesce(MAX(seqn), 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:

Expand|Select|Wrap|Line Numbers
  1. insert into activity 
  2. (id, activity_type, transaction_date)
  3. VALUES 
  4. (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_date 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
Jan 22 '08 #2
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?

Expand|Select|Wrap|Line Numbers
  1. insert into activity 
  2. (seqn, id, activity_type, transaction_date)
  3. VALUES 
  4. coalesce(MAX(seqn), 0) + 1 FROM activity WITH (UPDLOCK),
  5. (select id FROM subscriptions WHERE (PRODUCT_CODE = 'Product1') AND (PAID_THRU = '2007-12-31')) ,
  6.  'DUES', '2008-12-31') 
  7.  
Did you resolve this issue on your insert?

You can try the Counter table for the LAST_VALUE for Activity.
Apr 7 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Fraser Hanson | last post: by
7 posts views Thread by Joel Thornton | last post: by
3 posts views Thread by Martin | last post: by
5 posts views Thread by scott@spacehug.com | last post: by
22 posts views Thread by graham.parsons@reflective.com | last post: by
5 posts views Thread by Johnny Ljunggren | last post: by
15 posts views Thread by edouard.spooner@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.