0

I have two tables Control1 (BatchID ,TableName) , Control2(BatchID, MemID) and there are additional tables that will be added to the database one by one. The format for the tablename is ABC_date for example ABC_01032014 (dynamic)

Now this Table ABC_Date has one caolumn Called MemID.

control1 and control2 are empty tables and abc_date is populated with let's say 10 MemIds.

So, I want to populate control1 and control2. Control1 would have a unique ID and tableName = ABC_date DONE

control2 would have the same BatchID as control1 and all 10 memIds from ABC_Date. Please help with control2.

Now it should have all 10 memIDs from ABC_Date and 1 same batchID from control1

Thanks

Raidri
  • 17,258
  • 9
  • 62
  • 65
Bhupinder Singh
  • 1,061
  • 1
  • 11
  • 21

1 Answers1

-1

I'm not sure if you want 10 rows in control 2 or just one with the MemIDs separated by a comma but I'm assuming you want 10 registers

you could use a cursor to do the job for you

Declare @BatchID
Declare @MemIdFetch

Set @BatchID = Select distinct BatchID from control1

--Declaration of the cursor
Declare CursorABC cursor for select MemID from ABC_date where BatchID = @BatchID

--Fetching the cursor
Fetch CursorABC into @MemIdFetch 

--Loop for the cursor 
WHILE(@@FETCH_STATUS = 0)
BEGIN
insert into control2 select @BatchID,@MemIdFetch

Fetch CursorABC into @MemIdFetch
END

Hope it helps

user3275265
  • 1