Professional Web Applications Themes

How to select and then loop while insert - Microsoft SQL / MS SQL Server

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

  1. #1

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


    ..

    >-----Original Message-----
    >You can use Cursors (see books online)
    >or
    >Use WHILE .. BEGIN .. END (see books online)
    >
    >Ivan
    >
    >"Mansoor Aleem" <aleemmansoorhotmail.com> wrote in
    message
    >news:0dcb01c34588$3145c270$a001280aphx.gbl...
    >> How can I select some records and then loop thru them
    one
    >> by one and then insert a new record in another table.
    >> This is all within a sp. Thanks.
    >
    >
    >.
    >
    Mansoor Aleem Guest

  2. #2

    Default 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

  3. #3

    Default 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" <aleemmansoorhotmail.com> wrote in message
    news:043f01c3458a$d220bc70$a301280aphx.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.
    >
    >
    > .
    >
    >
    > >-----Original Message-----
    > >You can use Cursors (see books online)
    > >or
    > >Use WHILE .. BEGIN .. END (see books online)
    > >
    > >Ivan
    > >
    > >"Mansoor Aleem" <aleemmansoorhotmail.com> wrote in
    > message
    > >news:0dcb01c34588$3145c270$a001280aphx.gbl...
    > >> How can I select some records and then loop thru them
    > one
    > >> by one and then insert a new record in another table.
    > >> This is all within a sp. Thanks.
    > >
    > >
    > >.
    > >

    Ivan Demkovitch Guest

  4. #4

    Default 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
    >-----Original Message-----
    >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" <aleemmansoorhotmail.com> wrote in
    message
    >news:043f01c3458a$d220bc70$a301280aphx.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.
    >>
    >>
    >> .
    >>
    >>
    >> >-----Original Message-----
    >> >You can use Cursors (see books online)
    >> >or
    >> >Use WHILE .. BEGIN .. END (see books online)
    >> >
    >> >Ivan
    >> >
    >> >"Mansoor Aleem" <aleemmansoorhotmail.com> wrote in
    >> message
    >> >news:0dcb01c34588$3145c270$a001280aphx.gbl...
    >> >> How can I select some records and then loop thru
    them
    >> one
    >> >> by one and then insert a new record in another
    table.
    >> >> This is all within a sp. Thanks.
    >> >
    >> >
    >> >.
    >> >
    >
    >
    >.
    >
    Mansoor Aleem Guest

  5. #5

    Default 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

Similar Threads

  1. xml - how to select next item in loop?
    By Tony Barnhill in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: January 6th, 07:42 PM
  2. What's faster - loop for insert or insert...select.
    By mr. modus in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 01:57 AM
  3. Insert Loop values into DB Problem
    By DJ5MD in forum Coldfusion - Advanced Techniques
    Replies: 7
    Last Post: July 18th, 04:11 PM
  4. Can you put an Insert Command object in a loop?
    By Tim Pollard in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 12th, 09:47 PM
  5. insert record loop
    By ericMorcombe in forum Dreamweaver AppDev
    Replies: 0
    Last Post: February 24th, 05:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139