Professional Web Applications Themes

Insert Dummy Records - Microsoft SQL / MS SQL Server

David, Thanks for the reply. Maybe I should have elaborated. The reason I need these records is so I can produce a report that when these three fields have no corresponding numeric value the report will spit out the records displaying 0.00 for the corresponding numeric field. The client demanded that these blank records appear. It also adds uniformity to the report. Thanks again. Also I am just starting to deal with SQL programming and this report was throw'n at me to produce quickly. I was having a heck of a time trying to achieve this. Jon >-----Original Message----- >Something ...

  1. #1

    Default Re: Insert Dummy Records

    David,

    Thanks for the reply. Maybe I should have elaborated. The
    reason I need these records is so I can produce a report
    that when these three fields have no corresponding numeric
    value the report will spit out the records displaying 0.00
    for the corresponding numeric field. The client demanded
    that these blank records appear. It also adds uniformity
    to the report.

    Thanks again. Also I am just starting to deal with SQL
    programming and this report was throw'n at me to produce
    quickly. I was having a heck of a time trying to achieve this.

    Jon


    >-----Original Message-----
    >Something like:
    >
    >INSERT INTO Sometable (customer, service, region)
    > SELECT C.customername, S.servicename, R.regionname
    > FROM Customers AS C
    > CROSS JOIN Services AS S
    > CROSS JOIN Regions AS R
    >
    >But the phrase "dummy records" should indicate to you that
    something is
    >wrong here. Why are you generating this redundant data?
    Wouldn't a view
    >similar to the above query be a more efficient solution?
    >
    >--
    >David Portas
    >------------
    >Please reply only to the newsgroup
    >--
    >
    >"Jon" <jonscottsmartneighborhood.net> wrote in message
    >news:06ba01c3417a$c7f5b4d0$a001280aphx.gbl...
    >> Hi, I need to insert dummy records into a table every
    >> week. Each week I need to truncate a table and insert
    >> dummy records for customer name, Service and Region.
    >> Problem is the Customer names change each week but the
    >> Servicetype and Regions are static. I need to insert all
    >> the new customer names from Table A and with each
    >> customer name I need to add to the insert Service and
    >> Region. For example :
    >>
    >> Customer Service Region
    >> ABC New NorthEast
    >> ABC New South
    >> ABC New West
    >> ABC Disc NorthEast
    >> ABC Disc South
    >> ABC Disc West
    >> XYZ New Northeast etc etc etc....
    >>
    >> Can anyone help me with this insert statement? Again
    >> Customer name is always changing each week and the other
    >> two fields are just static values.
    >>
    >> Thank you very much for your time.
    >>
    >> Jon
    >
    >
    >.
    >
    jon Guest

  2. #2

    Default Re: Insert Dummy Records



    Stephen,

    Thanks for the help!!

    Jon
    >-----Original Message-----
    >create table #Services (
    > Service varchar(10)
    >)
    >
    >insert into #Services values ('New')
    >insert into #Services values ('Disc')
    >
    >create table #Regions (
    > Region varchar(10)
    >)
    >
    >insert into #Regions values ('NorthEast')
    >insert into #Regions values ('South')
    >insert into #Regions values ('West')
    >
    >insert into Results
    > (Customer, Service, Region)
    >select a.Customer, s.Service, r.Region
    >from Customers a
    > cross join #Services s
    > cross join #Regions r
    >
    >HTH
    >
    >*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    >Don't just participate in USENET...get rewarded for it!
    >.
    >
    jon Guest

  3. #3

    Default Re: Insert Dummy Records

    > reason I need these records is so I can produce a report
    > that when these three fields have no corresponding numeric
    > value the report will spit out the records displaying 0.00
    That's what I thought. So you shouldn't need to INSERT dummy rows into a
    table, you just need to include the CROSS JOIN in your report query:

    SELECT ....

    /* the rest of your report here */

    FROM Customers AS C
    CROSS JOIN Services AS S
    CROSS JOIN Regions AS R

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "jon" <jonscottsmartneighborhood.net> wrote in message
    news:029101c34186$72676e30$a101280aphx.gbl...
    > David,
    >
    > Thanks for the reply. Maybe I should have elaborated. The
    > reason I need these records is so I can produce a report
    > that when these three fields have no corresponding numeric
    > value the report will spit out the records displaying 0.00
    > for the corresponding numeric field. The client demanded
    > that these blank records appear. It also adds uniformity
    > to the report.
    >
    > Thanks again. Also I am just starting to deal with SQL
    > programming and this report was throw'n at me to produce
    > quickly. I was having a heck of a time trying to achieve this.
    >
    > Jon
    >
    >
    >
    > >-----Original Message-----
    > >Something like:
    > >
    > >INSERT INTO Sometable (customer, service, region)
    > > SELECT C.customername, S.servicename, R.regionname
    > > FROM Customers AS C
    > > CROSS JOIN Services AS S
    > > CROSS JOIN Regions AS R
    > >
    > >But the phrase "dummy records" should indicate to you that
    > something is
    > >wrong here. Why are you generating this redundant data?
    > Wouldn't a view
    > >similar to the above query be a more efficient solution?
    > >
    > >--
    > >David Portas
    > >------------
    > >Please reply only to the newsgroup
    > >--
    > >
    > >"Jon" <jonscottsmartneighborhood.net> wrote in message
    > >news:06ba01c3417a$c7f5b4d0$a001280aphx.gbl...
    > >> Hi, I need to insert dummy records into a table every
    > >> week. Each week I need to truncate a table and insert
    > >> dummy records for customer name, Service and Region.
    > >> Problem is the Customer names change each week but the
    > >> Servicetype and Regions are static. I need to insert all
    > >> the new customer names from Table A and with each
    > >> customer name I need to add to the insert Service and
    > >> Region. For example :
    > >>
    > >> Customer Service Region
    > >> ABC New NorthEast
    > >> ABC New South
    > >> ABC New West
    > >> ABC Disc NorthEast
    > >> ABC Disc South
    > >> ABC Disc West
    > >> XYZ New Northeast etc etc etc....
    > >>
    > >> Can anyone help me with this insert statement? Again
    > >> Customer name is always changing each week and the other
    > >> two fields are just static values.
    > >>
    > >> Thank you very much for your time.
    > >>
    > >> Jon
    > >
    > >
    > >.
    > >

    David Portas Guest

  4. #4

    Default Re: Insert Dummy Records

    Jon,

    David is right in saying that if all you want the data for is one
    report, it would be better to simply apply this same technique at report
    time.

    Hope things work out for you. Let us know how it turns out!



    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Stephen Hendricks Guest

Similar Threads

  1. 2 insert records and One page
    By Speegs in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 24th, 05:42 PM
  2. Insert Records from checkboxes
    By roshea in forum Macromedia ColdFusion
    Replies: 1
    Last Post: March 16th, 08:45 PM
  3. insert blank row between records
    By mark in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: May 26th, 11:59 AM
  4. insert records
    By Zvika Gur-Esh in forum Macromedia Dynamic HTML
    Replies: 1
    Last Post: August 7th, 09:55 AM

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