Copy data from one table to another table order by

[vkk]

Hi,

I have the following data in a table called staging, and it is in the format. C1,C2 and C3 are column Headers.

C1 C2 C3
41 te cc
51 te cc
42 te cc
41 te cc
51 te cc
42 te cc
41 te cc
51 te cc
42 te cc

Now from this staging table i want to insert into another temp table in the same order but i want to insert row42 after row41. Can you help me with the query? I appreciate your help

The result should be

C1 C2 C3
41 te cc
42 te cc
51 te cc
41 te cc
42 te cc
51 te cc
41 te cc
42 te cc
51 te cc

[M_K_Higa]

It looks like you want to do some logical grouping.

Here's what I would do....

C1 C2 C3 Grp
41 te cc 1
42 te cc 1
51 te cc 1
41 te cc 2
42 te cc 2
51 te cc 2
41 te cc 3
42 te cc 3
51 te cc 3

With this structure, you can sort by grp, c1 to get your desired results. If you don't want the Grp column, in your temp table, then exclude it from your select statement.

[vkk]

Mike,

Can you tell me how do i add grp column to the table as you shown above. if possible can you help me with the query? appreciate your help
Thx

[M_K_Higa]

Here's how you get the grp column into your temp table.

select C1, C2, C3, 0 [grp]
into #t1 
from staging

This creates a temp table called #t1 with a column named grp which is all initialized with the value 0.

You will then have to update the grp column so that you can achieve your results.

[vkk]

Mike,

I know how to add group column to the table. Can you please tell me how do i update the group. Appreciate your help
Thx

[M_K_Higa]

I'm not so sure how your data columns relates to each other, but here's one way you can put data into the grp column....

This is a three step process since it looks like you have three unique values in C1. Also, I'm making the assumption that you don't care which 41, 42 and 51 gets grouped together.

Here's step 1. This increments the grp column where the C1 column has a value of 41. This process is repeated for C1=42 and C1=51.

declare @cnt as int 
set @cnt = 0
update #t1
set @cnt = grp = @cnt + 1 
where C1 = 41

C1=42

declare @cnt as int 
set @cnt = 0
update #t1
set @cnt = grp = @cnt + 1 
where C1 = 42

C1=51

declare @cnt as int 
set @cnt = 0
update #t1
set @cnt = grp = @cnt + 1 
where C1 = 51

Finally, you can run the following query...

select C1, C2, C3 from #t1
order by grp, C1

Hope this helps.

[vkk]

Can you tell me how to run this in Sql Server Query Analyzer ?
Appreciate your help
Thx

[M_K_Higa]

copy/paste the following code into query analyzer.

SELECT C1, C2, C3, 0 [grp]
INTO #t1 
FROM staging

declare @cnt as int 
set @cnt = 0
update #t1
set @cnt = grp = @cnt + 1 
where C1 = 41

set @cnt = 0
update #t1
set @cnt = grp = @cnt + 1 
where C1 = 42

set @cnt = 0
update #t1
set @cnt = grp = @cnt + 1 
where C1 = 51

SELECT C1, C2, C3
FROM #t1 ORDER BY grp, C1

[M_K_Higa]

Let me know how it worked. If you get any errors, let me know of those too.

(I assumed you are using MS SQL 7 or above.)

[vkk]

Hi Mike,

I tried but it is not working. the staging table has more than 2000 rows and some times it will be like this. 51 rows will be more some times and some times it will be less. I want to group the data between 41 row and 42row. i mean row 41,51,42 shoud be one group. the main reason i am doing is i want the row 42 to be inserted after row 41. but in the table i have in the following order. basically each group should be distinct. can you help me how to achieve this? appreicate your help
Thx

C1 C2 C3
41 te cc
51 te cc
51 te cc
51 te cc
42 te cc
41 te cc
51 te cc
51 te cc
42 te cc
41 te cc
51 te cc
51 te cc
51 te cc
51 te cc
51 te cc
51 te cc
42 te cc
41 te cc
51 te cc
42 te cc