Help - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Help

Author

Topic

reenz
Starting Member

29 Posts

Posted - 2006-04-19 : 07:29:24

Suppose i have 2 tables

Table A
col1 int
id bigint

Table B
col1 int
col2 varchar(100)

i want to match table A with table B based on col 1 and insert the id of table A with the cols in table B into table C as well as break up delimited data col of table B

Eg
Table A
123 ID1
234 ID2

Table B
123 a>b>c
234 d>e>f

Table C
123 ID1 a b c
234 ID2 d e f

How do i go about doing it? I currently using cursors but the performance sux.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-19 : 08:00:01

[code]
create function dbo.CSVStr(@str varchar(8000), @delimiter varchar(10), @word_no int)
returns varchar(100)
as
begin
declare @word varchar(100),
@s int,
@c int,
@i int

select @i = 1
select @s = 1
select @c = -1
while (@i <= @word_no and @c <> 0)
begin
select @s = @c + 1
select @c = charindex(@delimiter, @str + @delimiter, @s)
select @i = @i + 1
end
select @word = substring(@str, @s, @c - @s)
where @c <> 0
return @word
end
go

declare @TableA table
(
col1 int,
id varchar(10)
)
declare @TableB table
(
col1 int,
col2 varchar(100)
)

insert into @TableA
select 123, 'ID1' union all
select 234, 'ID2'

insert into @TableB
select 123, 'a>b>c' union all
select 234, 'd>e>f'

select a.col1, a.id, dbo.CSVStr(b.col2, '>', 1), dbo.CSVStr(b.col2, '>', 2), dbo.CSVStr(b.col2, '>', 3)
from @TableA a inner join @TableB b
on a.col1 = b.col1
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-19 : 08:56:52

You need to read about Normalisation
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

reenz
Starting Member

29 Posts

Posted - 2006-04-19 : 20:28:20

the databases are created donkey years ago and its not up to me to say normalize them and i can do it.. data are coming in at 5 million records per table per day and i'm told to work with at least 5 such tables... it had been proposed to changed the schema but it will affect like dozens of other applications so no one is willing to bite the bullet yet.

reenz
Starting Member

29 Posts

Posted - 2006-04-19 : 20:44:09

Hi KH,

suppose it gets a little messier...
Table B now have a gid col which affect how data is col2 is like

Table B
col1 int
col2 varchar(100)
gid int

There is like 20 different gids. And i am interested in data a b c d efrom a few gids...
Eg

gid = 40 OR 18
col2 =1234567d

gid = 42 and consist of d
col 2 = 1234567d890>…>a:b:c
else col 2 = 1234567d890>…>a>b>c

gid = 18
col2 = e123456..

and i would like to write data from table B with table A's id and data a b c d into table C
and table B with table A's id and data d e into another table D and data from the rest of Table A's id and table B (other gids) into table E so that my asp.net application can read from table C D and E rather then the chunky table A n B

is it possible/feasible?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-19 : 21:40:44

yes. you can use CASE WHEN .. THEN to do this.

reenz
Starting Member

29 Posts

Posted - 2006-04-20 : 04:08:16

i tried something in the line of

case when gid = '42'
then dbo.CSVStr(b.col2, '>', 1)
end as a

and it work..
im thinking of

case when gid = '42'
then dbo.CSVStr(b.col2, '>', 1) as a, dbo.CSVStr(b.col2, '>', 2) as b, dbo.CSVStr(b.col2, '>', 3) as c
end

but it gave an error...

is it possible to do something like that since i need 3 data from the same case condition instead of doing 3 cases that are the same?

case when gid = '42'
then dbo.CSVStr(b.col2, '>', 1)
end as a,
case when gid = '42'
then dbo.CSVStr(b.col2, '>', 2)
end as b,
case when gid = '42'
then dbo.CSVStr(b.col2, '>', 3)
end as c

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-20 : 06:51:57

can you post some sample data of TableB for various different format of col2 that you might have

reenz
Starting Member

29 Posts

Posted - 2006-04-20 : 22:11:23

table B
gid int
ckpt varchar(100)
gstn
gsub

Sample Data 1
42
HKGBKKDF 1>HKG>KWS2356>0103>H143144912 >HHP>
HKG
KCC

Sample Data 2
42
SINLHRDF 1>:BRU-HUB> BCS2411:200406:AAA5692:STN
BRU
HUB

Sample Data 3
18
STAMP SGN00010307
HKG
KCC

Sample Data 4
20
HKG OHrtn to shipper 0000
HKG
KCC

Sample Data 5
40
HKGHKGRTCNOR STAMP 0000
HKG
KCC

What i need is

gid = 42 & substring(7,2)= DF
Sample Data 1
DF
NULL
KWS2356
0103
H143144912
HKG
KCC

gid = 42 & substring(7,2)= DF & gstn NOT IN (BKK, HHP,SSC ) & gsub <> HUB
Sample Data 2
DF
NULL
BCS2411
200406
AAA5692
BRU
HUB

gid = 18
Sample Data 3
18
OK Data retrieved from select c1 from tablec1 where gid = 18
STAMP LEFT(12)
NULL
NULL
NULL
HKG
KCC

gid = 20
Sample Data 4
20
OH
NULL
NULL
NULL
NULL
HKG
KCC

Gid = 40
Sample Data 5
40
RT
NULL
NULL
NULL
NULL
HKG
KCC

What i hope to achieve is to insert data with gid 42 into a table42,
data with gid 18 or 40 into another table1840
and all the data(includiing gid 42,18,40) into tableALL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-21 : 01:15:58

looks like you have quite a complicated scenario.

Modify the CSVStr() function and use IF .. ELSE or CASE WHEN .. ELSE .. END to check for your various condition and process accordingly

reenz
Starting Member

29 Posts

Posted - 2006-04-21 : 05:55:00

but even if i were to edit the function to cater for various gid, it wouldnt be able to return multiple values right?

what came to mind is to pass in gid and col2 into the function and hoping to return like 7 values from it.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources