Need help on Transfer row into another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help on Transfer row into another table

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-20 : 00:53:29

My table and row as follow,

declare @col_X as tinyint
declare @row_Y as tinyint
set @col_X=4
set @row_Y=11

declare @tBusSeat table
(
TrnxID int identity(1,1),
BusCd char(10) not null,
col_X tinyint not null,
row_Y tinyint not null,
seatDesc char(4) null
)
/*alter table @tBusSeat add constraint tBusType_BusCdcolXrowY_UQ unique (BusCd,col_X,row_Y)*/
/*alter table @tBusSeat add constraint tBusType_BusCdseatDesc_UQ unique (BusCd,seatDesc)*/

insert into @tBusSeat(BusCd,col_X,row_Y,seatDesc) values('STD30',1,1,'1A')
insert into @tBusSeat(BusCd,col_X,row_Y,seatDesc) values('STD30',2,1,'1B')
insert into @tBusSeat(BusCd,col_X,row_Y,seatDesc) values('STD30',3,1,'1C')
insert into @tBusSeat(BusCd,col_X,row_Y,seatDesc) values('STD30',4,1,'1D')
insert into @tBusSeat(BusCd,col_X,row_Y,seatDesc) values('STD30',1,2,'2A')
insert into @tBusSeat(BusCd,col_X,row_Y,seatDesc) values('STD30',3,2,'2C')
insert into @tBusSeat(BusCd,col_X,row_Y,seatDesc) values('STD30',4,2,'2D')
insert into @tBusSeat(BusCd,col_X,row_Y,seatDesc) values('STD30',2,3,'3B')
insert into @tBusSeat(BusCd,col_X,row_Y,seatDesc) values('STD30',3,9,'9C')
insert into @tBusSeat(BusCd,col_X,row_Y,seatDesc) values('STD30',4,9,'9D')

declare @tBusSeatV3 table
(
TrnxID int identity(1,1),
BusCd char(10) not null,
col_X tinyint null,
row_Y tinyint null,
seatDesc char(4) null
)

declare @i tinyint;
declare @j tinyint;
set @i = 1;
while @i <= @row_Y
begin

set @j = 1;
while @j <=@col_X
begin
insert into @tBusSeatV3(BusCd,col_X,row_Y) values('STD30',@j,@i)
set @j = @j + 1;
end

set @i = @i + 1;
end

select * from @tBusSeat
TrnxID| BusCd | col_X | row_Y | seatDesc
------------------------------------------------------
1 STD30 1 1 1A
2 STD30 2 1 1B
3 STD30 3 1 1C
4 STD30 4 1 1D
5 STD30 1 2 2A
6 STD30 3 2 2C
7 STD30 4 2 2D
8 STD30 2 3 3B
9 STD30 3 9 9C
10 STD30 4 9 9D

select * from @tBusSeatV3
TrnxID| BusCd | col_X | row_Y | seatDesc
------------------------------------------------------
1 STD30 1 1 NULL
2 STD30 2 1 NULL
3 STD30 3 1 NULL
4 STD30 4 1 NULL
5 STD30 1 2 NULL
6 STD30 2 2 NULL
7 STD30 3 2 NULL
8 STD30 4 2 NULL
9 STD30 1 3 NULL
10 STD30 2 3 NULL
11 STD30 3 3 NULL
12 STD30 4 3 NULL
13 STD30 1 4 NULL
14 STD30 2 4 NULL
15 STD30 3 4 NULL
16 STD30 4 4 NULL
17 STD30 1 5 NULL
18 STD30 2 5 NULL
19 STD30 3 5 NULL
20 STD30 4 5 NULL
21 STD30 1 6 NULL
22 STD30 2 6 NULL
23 STD30 3 6 NULL
24 STD30 4 6 NULL
25 STD30 1 7 NULL
26 STD30 2 7 NULL
27 STD30 3 7 NULL
28 STD30 4 7 NULL
29 STD30 1 8 NULL
30 STD30 2 8 NULL
31 STD30 3 8 NULL
32 STD30 4 8 NULL
33 STD30 1 9 NULL
34 STD30 2 9 NULL
35 STD30 3 9 NULL
36 STD30 4 9 NULL
37 STD30 1 10 NULL
38 STD30 2 10 NULL
39 STD30 3 10 NULL
40 STD30 4 10 NULL
41 STD30 1 11 NULL
42 STD30 2 11 NULL
43 STD30 3 11 NULL
44 STD30 4 11 NULL

I want fillup seatDesc field in @tBusSeatV3 with seatDesc field in @tBusSeat based on col_X and row_Y

As a result, my @tBusSeatV3 filled as follow,
TrnxID| BusCd | col_X | row_Y | seatDesc
-------------------------------------------------------
1 STD30 1 1 1A
2 STD30 2 1 1B
3 STD30 3 1 1C
4 STD30 4 1 1D
5 STD30 1 2 2A
6 STD30 2 2 NULL
7 STD30 3 2 2C
8 STD30 4 2 2D
9 STD30 1 3 NULL
10 STD30 2 3 3B
11 STD30 3 3 NULL
12 STD30 4 3 NULL
13 STD30 1 4 NULL
14 STD30 2 4 NULL
15 STD30 3 4 NULL
16 STD30 4 4 NULL
17 STD30 1 5 NULL
18 STD30 2 5 NULL
19 STD30 3 5 NULL
20 STD30 4 5 NULL
21 STD30 1 6 NULL
22 STD30 2 6 NULL
23 STD30 3 6 NULL
24 STD30 4 6 NULL
25 STD30 1 7 NULL
26 STD30 2 7 NULL
27 STD30 3 7 NULL
28 STD30 4 7 NULL
29 STD30 1 8 NULL
30 STD30 2 8 NULL
31 STD30 3 8 NULL
32 STD30 4 8 NULL
33 STD30 1 9 NULL
34 STD30 2 9 NULL
35 STD30 3 9 9C
36 STD30 4 9 9D
37 STD30 1 10 NULL
38 STD30 2 10 NULL
39 STD30 3 10 NULL
40 STD30 4 10 NULL
41 STD30 1 11 NULL
42 STD30 2 11 NULL
43 STD30 3 11 NULL
44 STD30 4 11 NULL

How my update statement look's like?

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-01-20 : 04:05:16

Use the script,

update @tBusSeatV3 set T2.seatDesc=T1.seatDesc from @tBusSeat as T1 inner join @tBusSeatV3 as T2
on T1.col_X=T2.col_X and T1.row_Y=T2.row_Y

This will give you the result.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-20 : 04:49:41

tq very much sir

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources