Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Newbie question Query Help

Author  Topic 

vk18
Posting Yak Master

146 Posts

Posted - 2006-09-27 : 21:22:48
Hello,

what i am trying here is, i want to insert this invoicenumber into tblinv as increments like 0, 1, 2 and so on.
here is my query. some reason it is not working. i am doing wrong. or else do i need to use Data Reader..?
Thx,



sql = "select max(invoicenumber) as maxinvoiceid from tblinv"
Dim getMaxinvoiceid As Integer = dbfunctions.GetSingleIntData(CONNECTIONSTRING, sql, "maxinvoiceid")


sql = "INSERT INTO tblInv (extension,invoicenumber) SELECT ext," & getMaxinvoiceid & ",from tblsp"

getMaxinvoiceid = getMaxinvoiceid + 1

follows my Connectionstring;


Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-27 : 21:56:43
[code]

INSERT INTO tblInv
(extension,
invoicenumber)
SELECT
a.ext,
b.maxinvoiceid
from
tblsp a
cross join
(
select
max(invoicenumber)+1 as maxinvoiceid
from
tblinv
) b




[/code]

CODO ERGO SUM
Go to Top of Page

vk18
Posting Yak Master

146 Posts

Posted - 2006-09-28 : 12:24:45
Hi,
It doesn't seems like working. i am giving my full query here. Please take a look at this. Also i do have one Identity column in the table.
what i am trying here is, i want to insert this invoicenumber into tblinv as increments like 0, 1, 2 and so on.
here is my query. some reason it is not working. i am doing wrong. or else do i need to use Data Reader..?
Thx,



sql = "select max(invoicenumber) as maxinvoiceid from tblinv"
Dim getMaxinvoiceid As Integer = dbfunctions.GetSingleIntData(CONNECTIONSTRING, sql, "maxinvoiceid")


sql = INSERT INTO tblInv (extension,invoicenumber,fkAdvertiser,fkAgency,fkGroup,fksubgroup,generationDate,BroadcastMonth) SELECT 0," & getMaxinvoiceid & ",fkadvertiser,fkagency,'" & Me.txtgroup.Text & "','" & Me.txtsubgroup.Text & "','" & Today.Date.Now & "','" & Me.ddbmonth.SelectedValue & "' from tblsp WHERE fkInvoice IS NULL AND dateScheduled <= '" & Me.ddbmonth.SelectedValue & "' AND fkContractType = 'UNWIRED'"

getMaxinvoiceid = getMaxinvoiceid + 1

follows my Connectionstring;


Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-28 : 12:38:12
Why are you incrementing AFTER creating your insert statement?
getMaxinvoiceid = getMaxinvoiceid + 1


Why did you originally post a different query than the one you wanted help with?



CODO ERGO SUM
Go to Top of Page

vk18
Posting Yak Master

146 Posts

Posted - 2006-09-28 : 12:49:38
quote:
Originally posted by Michael Valentine Jones

Why are you incrementing AFTER creating your insert statement?
getMaxinvoiceid = getMaxinvoiceid + 1


Why did you originally post a different query than the one you wanted help with?



CODO ERGO SUM




Hi,

it is not incrementing the values after i insert so i thought i need to do it again to increment like 0,1,2, and so on.
Actually this is the original query i just cut short to make it simple for the people to read

Thanks,


Go to Top of Page

vk18
Posting Yak Master

146 Posts

Posted - 2006-09-28 : 22:38:32
Hi,

Is it possible to get the increment number in Sql Server2000 while doing the Bulk Insert at a time into the table. Other than the Identity Column.
Thanks
Go to Top of Page
   

- Advertisement -