Ask a Question related to Microsoft SQL / MS SQL Server, Design and Development.
-
Mansoor Aleem #1
Re: How to select and then loop while insert
I need help in writing a stored procedure on SQL Server
2000. Basically the stored procedure's primary task is to
generate invoice records and insert the records in a
invoice table. In order to generate the invoice records,
I have an initial table which are basically Day Records.
The task that I would like to complete is to select all
records from that initial table and I guess put them into
a temp table. Now that i have my temp table, I would like
to loop thru the table record by record, and do inserts
in the invoice table. I cant seem to figure out the
syntax since I am somewhat weak in TSQL Programming. I
would appreciate any assistance on this forum or to my
email. Also If you need some pseudocode for the process
or the DDL for the initial table and the invoice table, I
can definitely post that in the forum possibly in the
next thread. Please advise, I would need a full written
syntax to get me started since i have some other
processes that I would need to build using the template.
Thanks again.
..
message>-----Original Message-----
>You can use Cursors (see books online)
>or
>Use WHILE .. BEGIN .. END (see books online)
>
>Ivan
>
>"Mansoor Aleem" <aleemmansoor@hotmail.com> wrote inone>news:0dcb01c34588$3145c270$a001280a@phx.gbl...>> How can I select some records and then loop thru them>>> by one and then insert a new record in another table.
>> This is all within a sp. Thanks.
>
>.
>Mansoor Aleem Guest
-
xml - how to select next item in loop?
Hello, Sorry to crosspost, just didn't know which group was more relevant. I am importing an xml file into my flash and assigning the value... -
What's faster - loop for insert or insert...select.
What is faster if I'm moving large numbers of records (anywhere from 10,000 to 300,000 records per archive) from one query to another table? 1)... -
Insert Loop values into DB Problem
:confused; How do I call the loop when inserting the values into my DB? I am getting an error message. Here is the Insert Statement: ... -
Can you put an Insert Command object in a loop?
Hi I am trying to figure out how to insert a variable number of records into one table in Access, therefore without stored procedures, in ASP... -
insert record loop
Can anyone help me to create an insert record loop using asp vbs? -
Mansoor Aleem #2 Re: How to select and then loop while insert
I need help in writing a stored procedure on SQL Server 2000. Basically
the stored procedure's primary task is to generate invoice records and
insert the records in a invoice table. In order to generate the invoice
records, I have an initial table which are basically Day Records. The
task that I would like to complete is to select all records from that
initial table and I guess put them into a temp table. Now that i have my
temp table, I would like to loop thru the table record by record, and do
inserts in the invoice table. I cant seem to figure out the syntax since
I am somewhat weak in TSQL Programming. I would appreciate any
assistance on this forum or to my email. Also If you need some
pseudocode for the process or the DDL for the initial table and the
invoice table, I can definitely post that in the forum possibly in the
next thread. Please advise, I would need a full written syntax to get me
started since i have some other processes that I would need to build
using the template. Thanks again.
*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!
Mansoor Aleem Guest
-
Ivan Demkovitch #3 Re: How to select and then loop while insert
Mansoor!
Post your DDL here. From what you described you don't need cursor and it
probably could be done using plain SQL.
Give details on how data gets inserted from where and to where and what
logic used.
"Mansoor Aleem" <aleemmansoor@hotmail.com> wrote in message
news:043f01c3458a$d220bc70$a301280a@phx.gbl...> I need help in writing a stored procedure on SQL Server
> 2000. Basically the stored procedure's primary task is to
> generate invoice records and insert the records in a
> invoice table. In order to generate the invoice records,
> I have an initial table which are basically Day Records.
> The task that I would like to complete is to select all
> records from that initial table and I guess put them into
> a temp table. Now that i have my temp table, I would like
> to loop thru the table record by record, and do inserts
> in the invoice table. I cant seem to figure out the
> syntax since I am somewhat weak in TSQL Programming. I
> would appreciate any assistance on this forum or to my
> email. Also If you need some pseudocode for the process
> or the DDL for the initial table and the invoice table, I
> can definitely post that in the forum possibly in the
> next thread. Please advise, I would need a full written
> syntax to get me started since i have some other
> processes that I would need to build using the template.
> Thanks again.
>
>
> .
>
>> message> >-----Original Message-----
> >You can use Cursors (see books online)
> >or
> >Use WHILE .. BEGIN .. END (see books online)
> >
> >Ivan
> >
> >"Mansoor Aleem" <aleemmansoor@hotmail.com> wrote in> one> >news:0dcb01c34588$3145c270$a001280a@phx.gbl...> >> How can I select some records and then loop thru them> >> >> by one and then insert a new record in another table.
> >> This is all within a sp. Thanks.
> >
> >.
> >
Ivan Demkovitch Guest
-
Mansoor Aleem #4 Re: How to select and then loop while insert
Thanks for offering me some help:
Here is the pseudocode for what i would like to do
Select tblprassignmentDays where BillingStatus = 'billnow'
--process though each selected record
if tblprassignmentDays.ClientRegHours or
tblprassignmentDays.ClientOTHours not = 0 then
insert into
tblARInvoiceDetailTemp
[Invoice_Line] Initially null until Invoice_ID
is Generated Concatenated Inv# + Line # (Unique)
[Invoice_ID] Initially null until Invoice_ID
is Generated
[Date] AssignDate from
tblprassignmentdays
[Units] see below
[Description] see below
[Quantity] if invoice is credit '-1'
otherwise '01'
[BillType] see below
[Amount] see below
[GLAccount] see below
[SWKMonth] concatenated field Assignid
(6),year(2),week(2),month(2) leading zeros on assignid
[Job#] tblprassignment.AssignID
[DEBatchID] Chips batch import#
here is the DDL for tblPRassignmentdays
CREATE TABLE [dbo].[tblPRAssignmentDays] (
[TimesheetDayID] [int] IDENTITY (1, 1) NOT NULL ,
[TimeSheetWeekID] [bigint] NULL ,
[AssignID] [int] NULL ,
[SlotID] [int] NULL ,
[AssignDate] [smalldatetime] NULL ,
[Status] [varchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[BillingStatus] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ProvRegHours] [numeric](6, 2) NULL ,
[ProvOTHours] [numeric](6, 2) NULL ,
[ProvNightCall] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ProvWeekendCall] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ProvMiscelAmount] [numeric](8, 2) NULL ,
[ProvMiscelBillClient] [bit] NULL ,
[ProvMemo] [varchar] (200) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ProvTotalAmount] [numeric](8, 2) NULL ,
[InsuranceHours] [numeric](6, 2) NULL ,
[InsuranceHoursOR] [bit] NULL ,
[InsuranceClassOR] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[InsuranceTotalAmount] [numeric](8, 2) NULL ,
[InsuranceTotalAmountQ] [numeric](18, 0) NULL ,
[ClientRegHours] [numeric](6, 2) NULL ,
[ClientOTHours] [numeric](6, 2) NULL ,
[ClientNightCharge] [bit] NULL ,
[ClientWeekendCharge] [bit] NULL ,
[ClientMemo] [varchar] (200) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ClientMiscelAmount] [numeric](8, 2) NULL ,
[ClientRegHoursOR] [bit] NULL ,
[ClientOTHoursOR] [bit] NULL ,
[ClientTotalAmount] [numeric](8, 2) NULL ,
[InvoiceCreateDate] [smalldatetime] NULL ,
[PayStatus] [char] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Creator] [varchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Created] [smalldatetime] NULL ,
[Updater] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Updated] [smalldatetime] NULL ,
[Archive] [bit] NULL
) ON [PRIMARY]
GO
here is the ddl for the table i would like to insert:
CREATE TABLE [dbo].[tblARInvoiceDetailTemp] (
[Invoice_Line] [varchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Invoice_ID] [varchar] (6) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [datetime] NULL ,
[Units] [varchar] (6) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (200) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [int] NULL ,
[BillType] [int] NULL ,
[Amount] [money] NULL ,
[GLAccount] [varchar] (11) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[SWKMonth] [varchar] (12) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Job#] [varchar] (6) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[DEBatchID] [int] NULL ,
[BookedDays] [real] NULL ,
[AssignIDMonth] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GOneed cursor and it>-----Original Message-----
>Mansoor!
>
>Post your DDL here. From what you described you don'twhere and what>probably could be done using plain SQL.
>
>Give details on how data gets inserted from where and tomessage>logic used.
>
>
>
>"Mansoor Aleem" <aleemmansoor@hotmail.com> wrote into>news:043f01c3458a$d220bc70$a301280a@phx.gbl...>> I need help in writing a stored procedure on SQL Server
>> 2000. Basically the stored procedure's primary task isrecords,>> generate invoice records and insert the records in a
>> invoice table. In order to generate the invoiceRecords.>> I have an initial table which are basically Dayinto>> The task that I would like to complete is to select all
>> records from that initial table and I guess put themlike>> a temp table. Now that i have my temp table, I wouldtable, I>> to loop thru the table record by record, and do inserts
>> in the invoice table. I cant seem to figure out the
>> syntax since I am somewhat weak in TSQL Programming. I
>> would appreciate any assistance on this forum or to my
>> email. Also If you need some pseudocode for the process
>> or the DDL for the initial table and the invoicetemplate.>> can definitely post that in the forum possibly in the
>> next thread. Please advise, I would need a full written
>> syntax to get me started since i have some other
>> processes that I would need to build using thethem>> Thanks again.
>>
>>
>> .
>>
>>>> message>> >-----Original Message-----
>> >You can use Cursors (see books online)
>> >or
>> >Use WHILE .. BEGIN .. END (see books online)
>> >
>> >Ivan
>> >
>> >"Mansoor Aleem" <aleemmansoor@hotmail.com> wrote in>> >news:0dcb01c34588$3145c270$a001280a@phx.gbl...
>> >> How can I select some records and then loop thrutable.>> one>> >> by one and then insert a new record in another>>> >> This is all within a sp. Thanks.
>> >
>> >
>> >.
>> >
>
>.
>Mansoor Aleem Guest
-
Mansoor Aleem #5 Re: How to select and then loop while insert
Thanks for offering me some help:
Here is the pseudocode for what i would like to do
Select tblprassignmentDays where BillingStatus = 'billnow'
--process though each selected record
if tblprassignmentDays.ClientRegHours or
tblprassignmentDays.ClientOTHours not = 0 then
insert into
tblARInvoiceDetailTemp
[Invoice_Line] Initially null until Invoice_ID is Generated
Concatenated Inv# + Line # (Unique)
[Invoice_ID] Initially null until Invoice_ID is Generated
[Date] AssignDate from tblprassignmentdays
[Units] see below
[Description] see below
[Quantity] if invoice is credit '-1' otherwise '01'
[BillType] see below
[Amount] see below
[GLAccount] see below
[SWKMonth] concatenated field
Assignid(6),year(2),week(2),month(2) leading zeros on assignid
[Job#] tblprassignment.AssignID
[DEBatchID] Chips batch import#
here is the DDL for tblPRassignmentdays
CREATE TABLE [dbo].[tblPRAssignmentDays] (
[TimesheetDayID] [int] IDENTITY (1, 1) NOT NULL ,
[TimeSheetWeekID] [bigint] NULL ,
[AssignID] [int] NULL ,
[SlotID] [int] NULL ,
[AssignDate] [smalldatetime] NULL ,
[Status] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BillingStatus] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ProvRegHours] [numeric](6, 2) NULL ,
[ProvOTHours] [numeric](6, 2) NULL ,
[ProvNightCall] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ProvWeekendCall] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ProvMiscelAmount] [numeric](8, 2) NULL ,
[ProvMiscelBillClient] [bit] NULL ,
[ProvMemo] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProvTotalAmount] [numeric](8, 2) NULL ,
[InsuranceHours] [numeric](6, 2) NULL ,
[InsuranceHoursOR] [bit] NULL ,
[InsuranceClassOR] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[InsuranceTotalAmount] [numeric](8, 2) NULL ,
[InsuranceTotalAmountQ] [numeric](18, 0) NULL ,
[ClientRegHours] [numeric](6, 2) NULL ,
[ClientOTHours] [numeric](6, 2) NULL ,
[ClientNightCharge] [bit] NULL ,
[ClientWeekendCharge] [bit] NULL ,
[ClientMemo] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ClientMiscelAmount] [numeric](8, 2) NULL ,
[ClientRegHoursOR] [bit] NULL ,
[ClientOTHoursOR] [bit] NULL ,
[ClientTotalAmount] [numeric](8, 2) NULL ,
[InvoiceCreateDate] [smalldatetime] NULL ,
[PayStatus] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Creator] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Created] [smalldatetime] NULL ,
[Updater] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Updated] [smalldatetime] NULL ,
[Archive] [bit] NULL
) ON [PRIMARY]
GO
here is the ddl for the table i would like to insert:
CREATE TABLE [dbo].[tblARInvoiceDetailTemp] (
[Invoice_Line] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Invoice_ID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [datetime] NULL ,
[Units] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Quantity] [int] NULL ,
[BillType] [int] NULL ,
[Amount] [money] NULL ,
[GLAccount] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SWKMonth] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Job#] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEBatchID] [int] NULL ,
[BookedDays] [real] NULL ,
[AssignIDMonth] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!
Mansoor Aleem Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
