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.
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 + 1follows 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.maxinvoiceidfrom tblsp a cross join ( select max(invoicenumber)+1 as maxinvoiceid from tblinv ) b[/code]CODO ERGO SUM |
|
|
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 + 1follows my Connectionstring; |
|
|
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 |
|
|
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 readThanks, |
|
|
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 |
|
|
|
|
|
|
|