Marc Cicero


Hello all,

I'm working on webshop database consisting of these tables:
customers, customers_groups, products_groups, products
For every customer (in customers) there is an entry in customers_groups.
The price lists that are used for every customer are defined in the table products_groups.
In products_groups the ids of customers_groups and products are used to assign a special price to every group.

Now I would like to insert data from another db on the sqlserver that holds the customer and product information.
So I have to insert price entries for all products for each customer.
I working on some t-sql procedures that should do that job.

In pseudo code:

Code Snippet
FOR EACH customer
FOR EACH product
INSERT INTO products_groups (, ...price)
END FOR
END FOR

Would that be a cross join

Thanks in advance for every hint!




Re: Updating webshop datatbase

oj


You can do something like this:

Code Snippet

insert products_groups(....)

select ...

from customers cross join products







Re: Updating webshop datatbase

Marc Cicero

Yes seems that the cross join was the right way to do that.

Now I'm thinking over the synchronization of this table.

I've also managet to fetch all customer groups that are not already used in a product group.

Code Snippet
SELECT customers_groups.customers_group_id
FROM customers_groups
LEFT JOIN products_groups
ON customers_groups.customers_group_id = products_groups.customers_group_id
WHERE products_groups.customers_group_id IS NULL;






Re: Updating webshop datatbase

Marc Cicero

Yes the CROSS JOIN did the job Smile

Thanks again!!!

There are some troubles left in the synchronization.

In one database there is a price list table with these cols: pricelist_id, product_id, price, customer_id

There could be one common price for a product in one pricelist and there could be a customer specific price too (then there is some customer id).

Now I would like to split up these table into simple layout like this:

products_groups_id, customers_group_id, price

So I would store all product prices for each customer (each customer has a group).

So every customer would have his own "pricelist" (the product group that holds all products with the prices).

I would like to create an insert / update statement to rearrange the data stored in the original table and insert it into the table with this new layout.

Any recommendations

Thanks in advance!





Re: Updating webshop datatbase

oj

Can you post DDL (create table) + sample data (insert statement) + expected output. This will make it easier to help.