Insert Dummy Records

[jon]

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" <jonscott@smartneighborhood.net> wrote in message
>news:06ba01c3417a$c7f5b4d0$a001280a@phx.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]

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

[David Portas]

> 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" <jonscott@smartneighborhood.net> wrote in message
news:029101c34186$72676e30$a101280a@phx.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" <jonscott@smartneighborhood.net> wrote in message
> >news:06ba01c3417a$c7f5b4d0$a001280a@phx.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

> >
> >
> >.
> >

[Stephen Hendricks]

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!