Databases
StackOverflow2013
Postsdbo
POExport data from a non-normalized database
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExport data from a non-normalized database
    primarykey
    data
    text
    <p>I need to export data from a <strong>non-normalized database</strong> where there are <strong>multiple columns</strong> to a new normalized database.<br> One example is the <em>Products</em> table, which has 30 boolean columns (ValidSize1, ValidSize2 ecc...) and every record has a foreign key which points to a <em>Sizes</em> table where there are 30 columns with the size codes (XS, S, M etc...). In order to take the valid sizes for a product I have to scan both tables and take the value SizeCodeX from the Sizes table only if ValidSizeX on the product is true. Something like this: </p> <pre><code>Products Table -------------- ProductCode &lt;PK&gt; Description SizesTableCode &lt;FK&gt; ValidSize1 ValidSize2 [...] ValidSize30 Sizes Table ----------- SizesTableCode &lt;PK&gt; SizeCode1 SizeCode2 [...] SizeCode30 </code></pre> <p>For now I am using a "template" query which I repeat for 30 times:</p> <pre><code>SELECT Products.Code, Sizes.SizesTableCode, -- I need this code because different codes can have same size codes Sizes.Size_1 FROM Products INNER JOIN Sizes ON Sizes.SizesTableCode = Products.SizesTableCode WHERE Sizes.Size_1 IS NOT NULL AND Products.ValidSize_1 = 1 </code></pre> <p>I am just putting this query inside a loop and I replace the "_1" with the loop index:</p> <pre><code>SET @counter = 1; SET @max = 30; SET @sql = ''; WHILE (@counter &lt;= @max) BEGIN SET @sql = @sql + ('[...]'); -- Here goes my query with dynamic indexes IF @counter &lt; @max SET @sql = @sql + ' UNION '; SET @counter = @counter + 1; END INSERT INTO DestDb.ProductsSizes EXEC(@sql); -- Insert statement GO </code></pre> <p>Is there a better, cleaner or faster method to do this? I am using <strong>SQL Server</strong> and I can only use <strong>SQL/TSQL</strong>.</p> <p>Thanks!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USStockBreak
    1. USStockBreak
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COThere's a rogue comma in your template query (end of line 4).
      singulars
      1. POExport data from a non-normalized database
      1. USNeville Kuyt
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload