Important Notice from AspDotNetStorefront
It is with dismay that we report that we have been forced, through the action of hackers, to shut off write-access to this forum. We are keen to leave the wealth of material available to you for research. We have opened a new forum from which our community of users can seek help, support and advice from us and from each other. To post a new question to our community, please visit: http://forums.vortx.com
Results 1 to 10 of 10

Thread: Help exporting via SQL - Product ID, Name, and Manufacturer....etc

  1. #1
    joecalardo is offline Member
    Join Date
    May 2011
    Posts
    61

    Default Help exporting via SQL - Product ID, Name, and Manufacturer....etc

    Help exporting via SQL - Product ID, Name, Manufacturer, and all Categories (if possible). I need a query to do this, I'm going to start uploading my product to Ebay I would like to build the categories again same way on my site. Thank you !

  2. #2
    esedirect is offline Senior Member
    Join Date
    Feb 2010
    Location
    Norfolk, UK
    Posts
    343

    Default

    You're not being too clear. Here are some queries:

    SELECT * FROM Product
    SELECT * FROM ProductVariant
    SELECT * FROM Category
    SELECT * FROM Manufacturer

    They will certainly get all the information you have asked for, but they're probably not what you really want.
    http://www.esedirect.co.uk
    --------------------------------------------------------------------------
    Using MS 9.2.0.0 with the following customisations:

    Lightbox/Fancybox enlarged images;
    Auto-suggest searchbox;
    Extra product information shown only to our IP Address (such as supplier info, costs, etc.);
    Failed transactions emailed via trigger;
    Custom app to show basket contents when customer online;
    Orders pushed through to accounting systems.

    All the above without source!

  3. #3
    joecalardo is offline Member
    Join Date
    May 2011
    Posts
    61

    Default

    Ok, what I want, is a query to return me the Product ID, Variant ID, The Manufacturer (of the product), the Category (both parent and subs), and also the Product name and Variant Name, I want those columns all in 1 return

  4. #4
    esedirect is offline Senior Member
    Join Date
    Feb 2010
    Location
    Norfolk, UK
    Posts
    343

    Default

    Code:
    WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS
    (
    SELECT c.ParentCategoryID, c.CategoryID, c.Name, 0 AS ComputedLevel, CAST('\'+c.Name AS NVARCHAR(255))
    FROM Category AS c
    WHERE ParentCategoryID = 0
    AND Deleted=0
    AND Published=1
    UNION ALL
    SELECT c.ParentCategoryID, c.CategoryID, c.Name, ComputedLevel + 1, CAST(s.Sort + '\'+c.Name AS NVARCHAR(255))
    FROM Category AS c
    INNER JOIN Categories AS s ON c.ParentCategoryID = s.CategoryID
    WHERE Deleted=0
    AND Published=1
    )
    SELECT pv.ProductID, pv.VariantID, p.Name AS ProductName, pv.Name AS VariantName, c.Sort AS CategoryName, m.Name AS Manufacturer
    FROM ProductVariant AS pv WITH (NOLOCK)
    JOIN Product AS p WITH (NOLOCK) ON p.ProductID = pv.ProductID
    JOIN ProductCategory AS pc WITH (NOLOCK) ON pc.ProductID = p.ProductID
    JOIN ProductManufacturer AS pm WITH (NOLOCK) ON pm.ProductID = p.ProductID 
    JOIN Manufacturer AS m WITH (NOLOCK) ON pm.ManufacturerID = m.ManufacturerID
    JOIN Categories AS c WITH (NOLOCK) ON c.CategoryID = pc.CategoryID
    WHERE pv.Deleted=0 AND pv.Published=1
    AND p.Deleted=0 and pv.Published=1
    http://www.esedirect.co.uk
    --------------------------------------------------------------------------
    Using MS 9.2.0.0 with the following customisations:

    Lightbox/Fancybox enlarged images;
    Auto-suggest searchbox;
    Extra product information shown only to our IP Address (such as supplier info, costs, etc.);
    Failed transactions emailed via trigger;
    Custom app to show basket contents when customer online;
    Orders pushed through to accounting systems.

    All the above without source!

  5. #5
    btappan2 is offline Member
    Join Date
    Apr 2012
    Posts
    43

    Default

    this is great!, how would I add product type to it and get the product type name, not just the ID ?

  6. #6
    esedirect is offline Senior Member
    Join Date
    Feb 2010
    Location
    Norfolk, UK
    Posts
    343

    Default

    Code:
    WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS
    (
    SELECT c.ParentCategoryID, c.CategoryID, c.Name, 0 AS ComputedLevel, CAST('\'+c.Name AS NVARCHAR(255))
    FROM Category AS c
    WHERE ParentCategoryID = 0
    AND Deleted=0
    AND Published=1
    UNION ALL
    SELECT c.ParentCategoryID, c.CategoryID, c.Name, ComputedLevel + 1, CAST(s.Sort + '\'+c.Name AS NVARCHAR(255))
    FROM Category AS c
    INNER JOIN Categories AS s ON c.ParentCategoryID = s.CategoryID
    WHERE Deleted=0
    AND Published=1
    )
    SELECT pv.ProductID, pv.VariantID, p.Name AS ProductName, pv.Name AS VariantName, c.Sort AS CategoryName, m.Name AS Manufacturer, pt.Name
    FROM ProductVariant AS pv WITH (NOLOCK)
    JOIN Product AS p WITH (NOLOCK) ON p.ProductID = pv.ProductID
    JOIN ProductType AS pt ON pt.ProductTypeID=p.ProductTypeID
    JOIN ProductCategory AS pc WITH (NOLOCK) ON pc.ProductID = p.ProductID
    JOIN ProductManufacturer AS pm WITH (NOLOCK) ON pm.ProductID = p.ProductID 
    JOIN Manufacturer AS m WITH (NOLOCK) ON pm.ManufacturerID = m.ManufacturerID
    JOIN Categories AS c WITH (NOLOCK) ON c.CategoryID = pc.CategoryID
    WHERE pv.Deleted=0 AND pv.Published=1
    AND p.Deleted=0 and pv.Published=1
    http://www.esedirect.co.uk
    --------------------------------------------------------------------------
    Using MS 9.2.0.0 with the following customisations:

    Lightbox/Fancybox enlarged images;
    Auto-suggest searchbox;
    Extra product information shown only to our IP Address (such as supplier info, costs, etc.);
    Failed transactions emailed via trigger;
    Custom app to show basket contents when customer online;
    Orders pushed through to accounting systems.

    All the above without source!

  7. #7
    SWillis is offline Junior Member
    Join Date
    May 2012
    Location
    Charlotte, NC
    Posts
    9

    Default

    In many cases exports like you are making are going to be to an XML API of some kind. Every API has it's own structure of course, but here's an example I put together using esedirect's excellent CTE posted above to produce a basic XML file. This is just an example of one way to do it. The code will have to be modified as necessary to get the desired XML structure.

    Code:
         IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL 
            DROP TABLE #TempTable
         
         CREATE TABLE #TempTable
                (
                 [ID] INT IDENTITY(1,1) NOT NULL
                ,[ProductID] INT NULL
                ,[ProductName] VARCHAR(255) NULL
                ,[SKU] VARCHAR(255) NULL
                ,[VariantID] INT NULL
                ,[VariantName] VARCHAR(255) NULL
                ,[Colors] VARCHAR(255) NULL
                ,[Sizes] VARCHAR(255) NULL
                ,[Price] VARCHAR(255) NULL
                ,[CategoryID] INT NULL
                ,[CategoryName] VARCHAR(255) NULL
                ,[ManufacturerID] INT NULL
                ,[ManufacturerName] VARCHAR(255) NULL
                ,PRIMARY KEY (ID)
                ,UNIQUE (ID)
                )
     
    
        ;WITH Categories(ParentCategoryID,CategoryID,Name)
         AS (
            SELECT
                c.ParentCategoryID
               ,c.CategoryID
               ,c.Name
            FROM
                dbo.Category AS c
            WHERE
                ParentCategoryID = 0
                AND Deleted = 0
                AND Published = 1
            UNION ALL
            SELECT
                c.ParentCategoryID
               ,c.CategoryID
               ,c.Name
            FROM
                dbo.Category AS c
            INNER JOIN 
                Categories AS s
                ON c.ParentCategoryID = s.CategoryID
            WHERE
                Deleted = 0
                AND Published = 1
            )
        INSERT INTO #TempTable
        SELECT 
            p.ProductID
           ,p.Name AS ProductName
           ,p.SKU
           ,pv.VariantID
           ,pv.Name AS VariantName
           ,pv.Colors
           ,pv.Sizes
           ,pv.Price
           ,c.CategoryID
           ,c.Name AS CategoryName
           ,m.ManufacturerID
           ,m.Name AS ManufacturerName
        FROM
            dbo.ProductVariant AS pv WITH (NOLOCK)
        INNER JOIN 
            dbo.Product AS p WITH (NOLOCK)
            ON p.ProductID = pv.ProductID
        INNER JOIN 
            dbo.ProductCategory AS pc WITH (NOLOCK)
            ON pc.ProductID = p.ProductID
        INNER JOIN 
            dbo.ProductManufacturer AS pm WITH (NOLOCK)
            ON pm.ProductID = p.ProductID
        INNER JOIN 
            dbo.Manufacturer AS m WITH (NOLOCK)
            ON pm.ManufacturerID = m.ManufacturerID
        INNER JOIN 
            Categories AS c WITH (NOLOCK)
            ON c.CategoryID = pc.CategoryID
        WHERE
            pv.Deleted = 0
            AND pv.Published = 1
            AND p.Deleted = 0
            AND pv.Published = 1  
        ORDER BY
            ProductID
           ,VariantID
           ,CategoryID
           ,ManufacturerID     
      
    
        SELECT 
            (
             SELECT
                (
                 SELECT
                    ISNULL(CAST(ProductID AS VARCHAR(255)),'') AS 'ID'
                   ,(
                     SELECT
                        ISNULL(CAST(ProductName AS VARCHAR(255)),'') AS 'Name'
                        FOR XML PATH(''),TYPE
                    )   
                   ,(
                     SELECT
                        ISNULL(CAST(SKU AS VARCHAR(255)),'') AS 'SKU'
                        FOR XML PATH(''),TYPE
                    )
                   ,(
                     SELECT
                        ISNULL(CAST(VariantID AS VARCHAR(255)),'') AS 'VariantID'
                       ,ISNULL(CAST(VariantName AS VARCHAR(255)),'') AS 'VariantName'
                       ,ISNULL(CAST(Colors AS VARCHAR(255)),'') AS 'VariantColor'
                       ,ISNULL(CAST(Sizes AS VARCHAR(255)),'') AS 'VariantSize'
                       ,ISNULL(CAST(Price AS VARCHAR(255)),'') AS 'VariantPrice'
                        FOR XML PATH('Variant'),TYPE
                    )
                   ,(
                     SELECT
                        ISNULL(CAST(CategoryID AS VARCHAR(255)),'') AS 'CategoryID'
                       ,ISNULL(CAST(CategoryName AS VARCHAR(255)),'') AS 'CategoryName'
                        FOR XML PATH('Category'),TYPE
                    )
                   ,(
                     SELECT
                        ISNULL(CAST(ManufacturerID AS VARCHAR(255)),'') AS 'ManufacturerID'
                       ,ISNULL(CAST(ManufacturerName AS VARCHAR(255)),'') AS 'ManufacturerName'
                     FOR XML PATH('Manufacturer'),TYPE
                    )
                FOR XML PATH(''),TYPE
                )
            FOR XML PATH('ProductDetail'),TYPE)
        FROM
            #TempTable
        WHERE
            ID > 0
        FOR XML PATH(''), ROOT('Products')
    Steven C. Willis
    Director, Database Administration & Development
    The Moore Creative Company
    704.896.6057
    swillis@moorecreative.com

  8. #8
    btappan2 is offline Member
    Join Date
    Apr 2012
    Posts
    43

    Default

    How could I modify the query below to report inventory as an "In Stock" or "Out Of Stock" at the product level and not the variant level?

    For example the query below returns:

    SKU STOCK
    12345-RED 3
    12345-GREEN 1
    12345-BLUE 2
    54321-BLUE 0
    54321-RED 0


    I'd rather it just returned one line saying if there were any variants in stock like, without the variant suffixes:

    SKU STOCK
    12345 IN STOCK
    54321 OUT OF STOCK


    Quote Originally Posted by esedirect View Post
    Code:
    WITH Categories (ParentCategoryID, CategoryID, Name, ComputedLevel, Sort) AS
    (
    SELECT c.ParentCategoryID, c.CategoryID, c.Name, 0 AS ComputedLevel, CAST('\'+c.Name AS NVARCHAR(255))
    FROM Category AS c
    WHERE ParentCategoryID = 0
    AND Deleted=0
    AND Published=1
    UNION ALL
    SELECT c.ParentCategoryID, c.CategoryID, c.Name, ComputedLevel + 1, CAST(s.Sort + '\'+c.Name AS NVARCHAR(255))
    FROM Category AS c
    INNER JOIN Categories AS s ON c.ParentCategoryID = s.CategoryID
    WHERE Deleted=0
    AND Published=1
    )
    SELECT pv.ProductID, pv.VariantID, p.Name AS ProductName, pv.Name AS VariantName, c.Sort AS CategoryName, m.Name AS Manufacturer, pt.Name
    FROM ProductVariant AS pv WITH (NOLOCK)
    JOIN Product AS p WITH (NOLOCK) ON p.ProductID = pv.ProductID
    JOIN ProductType AS pt ON pt.ProductTypeID=p.ProductTypeID
    JOIN ProductCategory AS pc WITH (NOLOCK) ON pc.ProductID = p.ProductID
    JOIN ProductManufacturer AS pm WITH (NOLOCK) ON pm.ProductID = p.ProductID 
    JOIN Manufacturer AS m WITH (NOLOCK) ON pm.ManufacturerID = m.ManufacturerID
    JOIN Categories AS c WITH (NOLOCK) ON c.CategoryID = pc.CategoryID
    WHERE pv.Deleted=0 AND pv.Published=1
    AND p.Deleted=0 and pv.Published=1

  9. #9
    btappan2 is offline Member
    Join Date
    Apr 2012
    Posts
    43

    Default

    $25 paypal to the first working answer for my question above.

  10. #10
    esedirect is offline Senior Member
    Join Date
    Feb 2010
    Location
    Norfolk, UK
    Posts
    343

    Default

    Don't even get out of bed for that!
    http://www.esedirect.co.uk
    --------------------------------------------------------------------------
    Using MS 9.2.0.0 with the following customisations:

    Lightbox/Fancybox enlarged images;
    Auto-suggest searchbox;
    Extra product information shown only to our IP Address (such as supplier info, costs, etc.);
    Failed transactions emailed via trigger;
    Custom app to show basket contents when customer online;
    Orders pushed through to accounting systems.

    All the above without source!