How to transfer values from one table to another

[johds]

Hi! i have a little problem on inserting my my data, i clean up all the values from from my previous table, only left are the fieldnames. now i want to insert new value on it that came from another table. but im having trouble in my query the error says i have wrong number of values for insert.
can somebody help me in this problem?
here is my query:

begin
declare a integer;
declare i integer;
set a = (select count(customercode) from tmp_a);
set i=0;
while i<=a loop
insert into customer (customercode,companyname,building,street,area,cou ntry,attention,email,tel,fax)
values ('select customercode,company_na,building,street,area,count ry,attn,mail_addre,tel_no_,fax_no_ from tmp_a');
set i=i+1;
end loop
end
please help me i really need your help.
thanks and happy new year!!!

johds

[teixeira]

Hi,

I've 2 things to tell:

1st - Chech if the field "Country" is well written in your code, ypu have "cou ntry" and maybe you wanted "country" all together.

2nd - if 1rst wasn't right, try this piece of code:

DECLARE @customercode <type>,@companyname <type>,@building <type>,@street <type>,
@area <type>,@country <type>,@attention <type>,@email <type>,@tel <type>,@fax <type>
DECLARE myCursor CURSOR FOR
select customercode,company_na,building,street,area,count ry,attn,mail_addre,tel_no_,fax_no_ from tmp_a

--Open cursor
OPEN myCursor

FETCH NEXT FROM myCursor
INTO --Get the first record
@customercode,@companyname,@building ,@street,@area,@countr,@attention,@email,@tel,@fax

WHILE @@FETCH_STATUS = 0--While there are records to go through....
BEGIN
/*Record insert*/
insert into customer (customercode,companyname,building,street,area,cou ntry,attention,email,tel,fax)
values(@customercode,@companyname,@building ,@street,@area,@countr,@attention,@email,@tel,@fax )

-- Get the next record
FETCH NEXT FROM myCursor
INTO @customercode,@companyname,@building ,@street,@area,@countr,@attention,@email,@tel,@fax
END

--Clean and close the cursor from memory
CLOSE myCursor
DEALLOCATE myCursor
GO

OR just try this at last,

begin
declare a integer;
declare i integer;
set a = (select count(customercode) from tmp_a);
set i=0;
while i<=a loop
insert into customer (customercode,companyname,building,street,area,cou ntry,attention,email,tel,fax)
values SELECT customercode,company_na,building,street,area,count ry,attn,mail_addre,tel_no_,fax_no_ FROM tmp_a;
/*You were inserting just one value because ' ' delimiters, make the expression to be executed as one field, this without the round brackets and without the ' ' */
/*YOUR OLD CODE -> ('select customercode,company_na,building,street,area,count ry,attn,mail_addre,tel_no_,fax_no_ from tmp_a');*/
set i=i+1;
end loop
end

Regards and happy new year too,
Tiago Teixeira

[jezemine]

what RDBMS are you using?

In most of them it would be done like this, and there is no need for your loop.

insert into customer
select customercode, company_na, building,street, area,country, attn, mail_addre, tel_no_, fax_no_
from tmp_a

[johds]

thank you guys, i made it