Systems Engineering and RDBMS

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.

2 Responses to “Data Migration Scenario – CTE solution”

  1. […] 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. […]

  2. […] was introduced by Microsoft MVP Itzik and that we have also covered in our posts before – here and here.   So, here goes: with N1 (n) AS (SELECT 1 UNION ALL SELECT 1), N2 (n) AS (SELECT 1 […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: