1

As part of an redesign, there's need for an different schema in one of the main tables in our database. The data in one of the existing tables will be kept as legacy data.

To make the changeover easier, I'm trying to create a view on the existing data, which can be union'ed with new data, to allow both to be used until the final change.

The scenario is that there's a table which stores the following:

  • WorkID
  • User
  • HoursWorked
  • Project
  • Number of reports written

It's being modified to become two tables, the first being

  • WorkID
  • User
  • HoursWorked
  • Project
  • Flag Legacy

And the second, with one row for every report as specified in the number of reports field in the original table.

  • ReportID,
  • WorkID [fk to first table]
  • .. (extra data about the report)

My issue is deriving the second table as a view, that can then be union'ed with data from the new version's table.

So, my question is, how could I go about selecting a row multiple times as specified by a field within that row as a query that can be created as a view - infact is it possible?

The DB is in Sql Server 2008r2

Thanks for your help!

Josh
  • 11
  • 1
  • 2

1 Answers1

2

If you have a numbers table, you can use that. If you don't have a numbers table, just create one:

CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY CLUSTERED);

INSERT dbo.Numbers WITH (TABLOCKX) (Number) 
SELECT TOP (1000000) Number = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_objects AS s1
  CROSS JOIN sys.all_objects AS s2;

(You probably don't need 1 million rows in your numbers table, but it's a decent ballpark that handles most scenarios. You can also reduce space by applying compression if you have an appropriate edition.)

Then you can do this:

SELECT t.columns
FROM dbo.[a table] AS t
CROSS JOIN dbo.Numbers AS n
WHERE n.Number >= 1 -- in case you have a 0-based numbers table
  AND t.[Number of reports written] >= n.Number;

More info on the power of Numbers tables:

Community
  • 1
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624