while loop, Insert into, and CASE

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

Transact-SQL (2000)

while loop, Insert into, and CASE

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-03-02 : 11:41:52

I am trying to insert records into a Table_A. Column2 in Table_A is defined as char (5).

In my insert statement, I need to do some validation: If the length of the value in column2 is greater than 5, then I want to concatenate 'Q00'+ the row number of the record. For example records 1-9 in Table_B will have the following values for column2:{Q0001,Q0002,Q0003,Q0004,Q0005,Q0006,Q0007,Q0008,Q0009}

Rcords 10-@maxrows will have the following records:
{Q0010,Q0011,Q0012,...Q0099}

However if the record number reaches 3 digit (i.e.100), I will need to reduce 1 '0' from the lead i.e.'Q0100'.

Now the complicated part, I need to feed corresponding values from Table_B into column9(code) and coumn10left(code,2).

When I run this query, the insert does complete, however instead of inserting 90 records into Table_A (because @maxrows equates to 90) I get 8100 records in Table_A.

There is a problem with my while loop. The system is inserting the 90 'code' and left(code,2) records for each Q0001, Q0002,Q0003....Q0090.

Anyhelp would be appreciated. Thanks

Declare @rowcount int
Declare @maxrows int
set @rowcount=1
Set @maxrows=(Select count(*) from Table_B)
While @rowcount<=@maxrows

begin
insert into Table_A
Select
'F',
case
when len('Q000'+right(@rowcount,4))>5 then 'Q00'+right(@rowcount,4) else 'Q000'+right(@rowcount,4)end,
'Q',
'QQQ',
'QQT001',
'NNNNN',
code,
left(code,2),
'F',
'0',
'0',
'0',
'0'
From Table_B
set @rowcount=@rowcount+1
end

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-02 : 11:50:09

instead of
case
when len('Q000'+right(@rowcount,4))>5 then 'Q00'+right(@rowcount,4) else 'Q000'+right(@rowcount,4)end,

'Q' + right('0000' + convert(varchar(4),@rowcount),4)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-03-02 : 13:40:00

That seems to have resolved my problem for the CASE issue, however, the problem with 8100 records still exists. I think the problem is in the while loop because it is inserting the all the 'Code' records (90) for each of the rows (90). Therefore 90 x 90 = 8100 records.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-02 : 13:56:21

You do know thatyou're inserting the entire tableb, over and over again for every

Set @maxrows=(Select count(*) from Table_B)

Look at you INSERT satatement...

Brett

8-)

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-03-02 : 13:59:57

Brett:

That is right, I need to insert the records over and over again until I have inserted all 90 records with the coresponding code.

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-03-02 : 14:19:27

Let me clarify the problem a little more
Lets use the following as an example of the data I want to insert into the CODE and Left(CODE,2) columns.

The following are records in Table_B(5 records in stead of 90):
CI002
CI003
DA001
DA002
DE001

Therefore, if I run my insert statement, I will have 25 records
The first record will have a coulmn2 column9 and Column10 entry of:
{Q0001,CI002,CI}
{Q0001,CI003,CI}
{Q0001,DE001,DE}
....
{Q0002,CI002,CI}
{Q0002,CI003,CI}
{Q0002,DE001,DE}
....
{Q0005,DE001,DE}

It should be as follows:
{Q0001,CI002,CI}
{Q0002,CI003,CI}
{Q0003,DA001,DA}
{Q0004,DA002,DA}
{Q0005,DE001,DE}

The insert statment I am using is:
Declare @rowcount int
Declare @maxrows int
set @rowcount=1
Set @maxrows=(Select count(*) from Table_B)
While @rowcount<=@maxrows
begin
insert into Table_A
Select
'F',
'Q' + right('0000' + convert(varchar(4),@rowcount),4),
'Q',
'QQQ',
'QQT001',
'NNNNN',
code,
left(code,2),
'F',
'0',
'0',
'0',
'0'
from Table_B
set @rowcount=@rowcount+1
end

ChrisFretwell
Starting Member

43 Posts

Posted - 2004-03-02 : 14:45:15

How about this (I used temp tables)

alter table #table_b
add tmp_ident int identity(1,1)

create table #table_A
(codeplus char(5),
code char(5),
subcode char(2))

insert into #table_A
Select
'Q' + right('0000' + convert(varchar(4),tmp_ident),4),
code,
left(code,2)
from #table_b

select * from #table_b returns

Q0001 CI002 CI
Q0002 CI003 CI
Q0003 DA001 DA
Q0004 DA002 DA
Q0005 DE001 DE

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-03-02 : 15:00:37

Chris,
Thanks for the brainpower. It works well. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources