April 9, 2002 at 8:18 am
I have two tables in my DB
Table one:
ID1 ID2 Refnum Date Address Phone Ect..
abc 123 178368 4/9/2002 NULL Null ...
ect......
Table two:
ID Address Phone Lname Fname
abc123 55 East st 5551212 Perdue Frank
ect….
I need to insert the data from table two into table one by matching up the ID in table 2 with the 2 id's in table one.
So where table2 ID = Both columns from table1 put together.
How the heck can I write this?? There are fixed data lengths in table one on both ID columns. The first ID in t1 is 9 and the second ID in t1 is 2. Not sure if this information can be used inorder to join the records together from each table.
April 9, 2002 at 9:21 am
quote:
I have two tables in my DB
Have you tried :-
INSERT INTO Table1 (
ID1
,ID2
,Refnum
,Date
,Address
,Phone
,etc.......
)
SELECT SUBSTRING(ID, 1, 3)
,SUBSTRING(ID, 4, 3)
,'A Refnum???'
,'A Date'
,Address
,Phone
,etc.....
FROM Table2
WHERE EXISTS (SELECT 1
FROM Table1
WHERE ID1 = SUBSTRING(Table2.ID, 1, 3)
AND ID2 = SUBSTRING(Table2.ID, 4, 3))
Please forgive me for the typing but I did this in the tiny TEXTAREA of the Reply page.
Basicall, do a where exists check on Table1 with the SUBSTRING'd key from Table2.
Hope this is what you were looking for.
Simple Simon.............
"To ask is human, to assume is dangerous"
Table one:
ID1 ID2 Refnum Date Address Phone Ect..
abc 123 178368 4/9/2002 NULL Null ...
ect......
Table two:
ID Address Phone Lname Fname
abc123 55 East st 5551212 Perdue Frank
ect….
I need to insert the data from table two into table one by matching up the ID in table 2 with the 2 id's in table one.
So where table2 ID = Both columns from table1 put together.
How the heck can I write this?? There are fixed data lengths in table one on both ID columns. The first ID in t1 is 9 and the second ID in t1 is 2. Not sure if this information can be used inorder to join the records together from each table.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply