Sequences from Oracle to SQL server

[toldav]

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.

[Dalek Caan]

If you are using SQL Server 2008 or earlier, you may need to use IDENTITY columns. Starting in SQL Server 2012, though, SEQUENCES are available.

http://msdn.microsoft.com/en-us/library/ff878091.aspx

[toldav]

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

[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

[toldav]

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

[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.

[toldav]

// 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

[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

[BitBlt]

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.

[toldav]

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