SQL Stored Procedure Question/Looping Records

[smilbuta]

I have Table A with 3 cloumns x,y,z Varchars.(data is a first middle lastname stuff)
I have Table b that is supposed to Store the Concatenated result of (xyz)

I have a stored procedure that is supposed to move through Table A and read the 3 columns then insert the result to table b. It works for 1 record only then stops, I am unsure how to get it to do more than one record.

CREATE  PROCEDURE namecat @newname nvarchar(50)
AS

	select  @newname = (X + Y + Z) from TableA
	Insert into  TableB (xyz) Values (@newname)
GO

Sorry for the Newish question but im thiking a Cursor is needed?

[brucevde]

Cursors should be a last resort and in this case they are not needed.

Insert Into TableB (xyz) Select x + ', ' + y + ' ' + z From TableA

What is the purpose of the @NewName parameter?

[smilbuta]

The new name parameter was how i was going to pass the cont cat variables to the insert statment.

[szlamany]

The new name parameter was how i was going to pass the cont cat variables to the insert statment.

You are thinking like a programmer - and T-SQL needs you to lose that old iterative/logic processing mindset.

You need to try to see how an entire "grid" of data can be prepared - as in the SELECT statement that BRUCEVDE showed you - and that grid will effortlessly pass into an INSERT statement.

All in one shot - that's the beauty of set-based logic and SQL...

[oceanebelle]

insert into b values
     b.xyz = a.xyz
from 
     (select x + y + z xyz
      from a)  a

edit:
first idea
basically you use a subquery for it but i have not tested it wait...

[szlamany]

oceane...

BRUCEVDE's syntax is fine - INSERT ... SELECT ... will work in MS SQL server. Probably in ACCESS as well.

I do not believe that you need to so carefully wrap it in ()'s to indicate it's sub-query nature or that you are required to alias it.

[oceanebelle]

hehhe yep there's a bug in it .. i tested this new one

DECLARE @a TABLE(x char(1), y char(2), z char(3))
DECLARE @b TABLE(xyz varchar(5))

insert into @a values('a', 'b', 'c')
insert into @a values('d', 'e', 'f')


select x + y + z xyz from
   @a

-- this is the only statement needed
insert into @b
  select x + y + z xyz from
   @a

his problem was because he only gets the first record, this one gets all in one statement.. though not sure in access. but here is the t-sql

[oceanebelle]

LOLz did not see that one sorry bruce