Data Migration Scenario - CTE solution
Posted by decipherinfosys on June 12, 2008
A few days ago, we had posted the problem and the solution pertaining to a data migration issue. We had given some solutions and explained one of those in detail along with the code. A reader asked us to do the same thing using the CTE which was one of the solutions that we had proposed. Using a CTE, there are a couple of ways to get it done. Refer to the tables and the data in that post before you use this code. Here is the CTE code and the explanation follows after that:
with
N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y),
N5 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n%10) FROM N4 AS X, N4 AS Y)
select p.ID as ItemID,
row_number() over (partition by p.id order by n) as LineItem,
substring(p.profession, case when n = 1 then 1 else n + 1 end, 10) as Profession
from N5
join dbo.profession as p
on N5.n <= Len(p.Profession)
where n <= 100
and (n = 1 or n%10 = 0)
order by p.id, n
ItemID LineItem Profession ----------- -------------------- ---------- 1 1 I am a Pro 1 2 grammer an 1 3 d a baseba 1 4 ll Champio 1 5 n and a fo 1 6 otball Pla 1 7 yer 2 1 I am a Doc 2 2 tor 3 1 I am an Ar 3 2 tist
The above piece of code first constructs a number table using the CTE (this was introduced by Itzik in his SQL column in the sqlmag) and then it joins with the source table Profession and slices the string up and shows up the data in the format that we needed for the destination table.
July 18, 2008 at 7:34 pm
[...] Posted by decipherinfosys on July 18, 2008 We were planning a get together with some of our friends and other community members in our sub-division and were thinking about some games to keep everyone interested and occupied and one of the suggestions was to do a lottery at the end of it and award prizes - so, tickets were issued and we wrote our own random number generator for the lottery system. If you look into our blog posts, we have posted similar solutions in the past by using a CTE and generating a table of sequential numbers as well as random numbers and have covered the generation of random numbers quite extensively in some of the posts. This solution was very much similar to those with a combination from both those posts. Here is how the SQL portion of that code looked like (used a SQL Server Solution for this one). In this post, we use the same type of CTE solution as we had used in another post here. [...]