Hello, everyone

I'm new into databases. I have an Oracle insert sequence that I need to convert to SQL server
if anyone could help me to get this to work. I'm not sure if nextval is compatible with SQL Server.

Here is the code:

// original
insert into invoice
select inv_no_seq.nextval, company_id, trunc(sysdate),
   trunc(sysdate) + 30, amount
from
(select co.company_id
       ,sum(a.amount_paid) amount
from company co, attendance a, student s
where s.student_id = a.student_id
and co.company_id = s.company_id
group by co.company_id);

// my changes
insert into invoice
select inv_no_seq.nextval, company_id, convert(datetime, convert(date,getdate())),
   convert(datetime, convert(date,getdate())) + 30, amount
from
(select co.company_id
       ,sum(a.amount_paid) amount
from company co, attendance a, student s
where s.student_id = a.student_id
and co.company_id = s.company_id
group by co.company_id);

Thank you in advance.

I have been trying that syntax and it just the first 3 lines of code that is not working. I was able to insert to the tables from oracle to sql server and only this insert I have issues with.
Thank you for yor input

Member Avatar

Dalek Caan

This link explains how you need to write the sequence usage in your SELECT statement which feeds the insert: http://msdn.microsoft.com/en-us/library/hh272694(v=vs.103).aspx

The syntax in SQL Server is SELECT NEXT VALUE FOR inv_no_seq as opposed to NextVal

Thank you! that helps a lot, but when I execute the query it comes with an error:
Incorrect syntax near ';'.
Thanks again for your help

// my changes
insert into invoice
select next value inv_no_seq, company_id, convert(datetime, convert(date,getdate())),
convert(datetime, convert(date,getdate())) + 30, amount
from
(select co.company_id
,sum(a.amount_paid) amount
from company co, attendance a, student s
where s.student_id = a.student_id
and co.company_id = s.company_id
group by co.company_id);
go

Member Avatar

Dalek Caan

According to the link, you need the word 'FOR' in your SELECT NEXT VALUE... I am not sure if that keyword may be excluded, though.

The syntax looks ok other than that. I do not have 2012 installed, so the Google references are the best I can offer. If someone has 2012 they might be able to format and check the syntax further.

// This is my update code
insert into invoice
select next value for inv_no_seq, company_id, convert(datetime, convert(date,getdate())),
convert(datetime, convert(date,getdate())) + 30, amount
from
(select co.company_id
,sum(a.amount_paid) amount
from company co, attendance a, student s
where s.student_id = a.student_id
and co.company_id = s.company_id
group by co.company_id);  // here is where the syntax show up red ";"
go

everything else clean.
Thank you

Member Avatar

Dalek Caan

Try giving your inline view an alias...

...group by co.company_id) IV1;

and change your first select to use it:

select next value for inv_no_seq, IV1.company_id, convert(datetime, convert(date,getdate())),
convert(datetime, convert(date,getdate())) + 30, IV1.amount

In order to use a derived table in MS SQL Server, you have to give the derived table an alias like so:

-- // This is my update code
insert into invoice
select next value for inv_no_seq, company_id, convert(datetime, convert(date,getdate())),
convert(datetime, convert(date,getdate())) + 30, amount
from
(select co.company_id
,sum(a.amount_paid) amount
from company co, attendance a, student s
where s.student_id = a.student_id
and co.company_id = s.company_id
group by co.company_id) AS myTbl;  -- // here is where the syntax show up red ";"
go

Also, please note that the double-forward slash is not a valid comment... SQL Server uses a double-dash.

It Works! Thanks a lot for everyone that put a little bit of their knowledge.