current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Someone else designed this table and I am not allowed to modify it so bear with me.

I am trying to insert multiple rows from one table into another. The table where I am inserting the rows has an ID but it does not auto-increment. I cannot figure out how to manually increment the id as I insert rows. The current code throws an error:

Error running query. Page15.CaseSerial is invalid in the select list becauseit is not contained in either an aggregate function or the GROUP BY clause.

I've tried adding a GROUP BY clause with no success.

Here's the code:

insert into page4 (serial, caseserial, linkserial, type, add1, add2, city, state, orgname, prefername, email, firstname, lastname, salutation, contactstatus, workphone, notes, cellphone, nametype, homephone, fax, zip, payments) 
   select id = max(serial), caseserial, linkserial, type, add1, add2, city, state, 
          orgname, prefername, email, firstname, lastname, salutation, contactstatus, 
          workphone, notes, cellphone, nametype, homephone, fax, zip, payments 
   from page16

It would be nice if I could write something to get the highest id from page4 and insert the next highest.

Thanks!

share|improve this question
2  
Which flavour of SQL? The solution will be different depending on what RDBMs you are using –  Andrew Nov 14 '11 at 21:36
    
What kind of server or environment are you using for your database? I am asking because MS Access, for example, supports DMin() and DMax() look-up functions in SQL queries. If your server support user-defined scalar functions you might want to implement those for your convenience and use them in your SQL query. –  Alexander Galkin Nov 14 '11 at 21:38
    
This is an article on microsofts approach to this if an MSFT db. support.microsoft.com/kb/273586 –  xQbert Nov 14 '11 at 21:48

1 Answer 1

up vote 0 down vote 
declare @maxId int

select @maxId = max(yourIdColumn)
from YourTable

set @maxId = @maxId + 1

insert into YourTable (yourIdColumn, ....)
values (@maxId, ....)

Disclaimer: not sure how this would transpose over to other RDBMS's, but this is with SQL Server in mind. Also, this handles inserting only one value. If you need to insert a set of values, then please let me know.

share|improve this answer
    
"I am trying to insert multiple rows from one table into another." He let us know :D –  xQbert Nov 14 '11 at 21:46
    
@xQbert That is true! Thanks for pointing out (the obvious). ;) I'll update my answer shortly. –  user596075 Nov 14 '11 at 21:47
    
I'm glad you took that in the spirit in which it was meant. Not snide, crude or rude. I was looking how to do this myself and your approach seemed reasonable. (I was hoping to avoid a cursor but I just can't figure it out without one.) thus I think that was the approach this (your response) would go. and didn't spend more time on it. + I wanted to know what RDBMS they were using. –  xQbert Nov 14 '11 at 22:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.