Merging Tables w/o Unique Field

[Compkitty]

I was trying to use a Temp Table w/ all of my product IDs, to cycle thru two other tables and merge them. However, I'm getting there is more than one value, this statement is terminated..This is due to the other two tables have several (millions) of the same ID... IS there a way around this? So that I can merge information from both tables into another table??? I was using a cursor w/ the ID from the temp table...

 

[stevexff]

You might have to give us a bit more detail. It could be a number of things.

 

[Compkitty]

Ok, here is what I have so far

DECLARE  @Pkey as nvarchar (3)


DECLARE Merge_Pkey_Trial CURSOR FOR
SELECT LATA FROM TBl_LATA_Tmp
ORDER BY LATA
OPEN Merge_Pkey_Trial
FETCH NEXT FROM Merge_Pkey_Trial
INTO @Pkey
WHILE @@FETCH_STATUS = 0 
BEGIN

FETCH NEXT FROM Merge_Pkey_Trial
INTO @Pkey


Insert INTO Tbl_Merge
SELECT Tbl_One.LATA, Tbl_One.NPA, Tbl_One.NXX, Tbl_One.Range,
Tbl_One.ST, Tbl_One.Company, Tbl_Two.LATA As OrigLata, Tbl_Two.Rate + Tbl_One.Rate AS Rate
FROM Tbl_Two INNER JOIN  Tbl_One ON Tbl_One.LATA= Tbl_Two.LATA
WHERE  Tbl_One.LATA = @Pkey



END
close Merge_Pkey_Trial
deallocate Merge_Pkey_Trial

Another way I did it was to Place Columns from Tbl_One, Tbl_Two into variables and do an INSERT...Values...
However, Tbl_One & Tbl_Two have NO Primary Key and there are a million rows for each PK in the Tbl_Lata_Tmp Table..
THANKS!!!!!!!!

 

[stevexff]

If you have multiple rows with the same key on both tables, this will give you the cartesian product of the matching rows in the merged table. Except in exceptional circumstances, this is a bad thing, and usually not what you want.

It sounds like you have what amounts to two flat files stored as tables, and you want to read a row from each, one at a time, and create the new row. Unless you can find a common key to link them 1:1, you may have to do this using two cursors.

 

[Compkitty]

Ok, Can you show me the structure for 2 cursors??? I am not the person who built these tables... just told to make it work... LOL =(

 

[stevexff]

Sorry, I thought that was what you'd tried

Another way I did it was to Place Columns from Tbl_One, Tbl_Two into variables and do an INSERT...Values...

I assumed you had opened two cursors (one on each table) and were looping through fetch cursor1, fetch cursor2, insert combined row...fetch cursor1 etc.

 

[Compkitty]

Nope, I have done two cursors at once, but was afraid and not 100% sure how to set it up for this one...