Insert
-
I have 2 tables table1 and table2 which is in use and has some data in it and has no relationship between them.
Now I need relationship between these 2 tables, how can I do tht without losing any data?
-
can you send the columns definition so we can try to figure it out?
-
it would help if you could show the schema of the tables in question; then you'd get a solid answer from one of the readers here.
In theory, you simply add the relationship.
if there is no data that is contradicts the relationship, it will work:
ie:
test if the foreign key would be valid:
SELECT * FROM TBL2 WHERE SOMEFIELD NOT IN(SELECT PRIMKEY FROM TBL1)
if no records were returned, you could potentially add a foreign key relationship to it:
ALTER TABLE TBL2 ADD FOREIGN KEY SOMEFIELD REFERENCES TBL1(PRIMKEY)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Is it possible to add a column in table2 as FK which is a PK column in table1 and make a relationship. The no of rows in both tables are not equal.
-
That's the way to do it, but without knowing the data, we can't help you. You'll have to set the values so that they mean something.
-
OK I'll give the structure of my tables and let me know how to update the table2 where I have added a FK column.
Lets say I have 2 tables in this way.
Table1
(PK)DocumentHeadersID int 4 0
FileHeadersID int 4 1
DocNum char 16 0
TrBatchPoNo char 50 0
RecordType char 2 0
TCode char 6 0
HeaderAmount money 8 0
LineItemCount int 4 0
RefDocNum char 16 0
HeaderLiquidationAmount money 8 0
PayDateBaseLineDate datetime 8 1
DocDateInvoiceDate datetime 8 1
PartnerNum char 10 0
PartnerPayee char 10 0
PaymentMethod char 2 0
BankPartner char 4 0
PaymentMethodSuppl char 2 0
PaymentBlock char 1 0
PaymentTerms char 4 0
HeaderText char 50 0
Notes char 79 0
Park char 1 0
VendorValdnSkip char 1 0
DocHeaderAddlData char 65 0
WarrNo char 14 0
BankNo char 2 0
ChkNo char 8 0
PayDate datetime 8 1
PurDocNum char 10 0
Table2
(PK)DocumentTransactionsID int 4 0
(FK)DocumentHeadersID int 4 1
RecordType char 2 0
LineItemNum smallint 2 0
Fund char 10 0
GLAccount char 10 0
CostCenter char 10 0
IntOrder char 12 0
WBSElement char 24 0
Amount money 8 0
DrCr char 1 0
Vendor char 10 0
DueOn datetime 8 1
ItemText char 50 0
RefDocEMFund char 10 0
EMFundLineItem smallint 2 0
LiquidationAmount money 8 0
GAAPCode char 1 0
ServicePeriodBeginDate datetime 8 1
ServicePeriodEndDate datetime 8 1
Assignment char 18 0
RefKey1 char 12 0
RefKey2 char 12 0
RefKey3 char 20 0
TypeCode char 2 0
DocDetailAddlData char 38 0
SetToComplete char 1 0
-
Update A set A.Col = 'WhatEver' from TableA A inner join TableB B on A.Pkid = B.Fkid
-
Do these fields relate?
Table1
(PK)DocumentHeadersID int 4 0
FileHeadersID int 4 1
Table2
(PK)DocumentTransactionsID int 4 0
(FK)DocumentHeadersID int 4 1
I wasn't born stupid - I had to study.
-
I would assume that PK and FK relate to each other in this case
. -
Here what do u mean by 'whatever'
-
Me too. I was trying to find out if this was a one to many relationship and where the problems might arise during an update... I should have been more specific...
I wasn't born stupid - I had to study.
-
yeah there is relation between Pk and FK.
OK what if I delete all the rows and try to insert it again with the this relationship. What will be INSERT command to do tht so that both tables have unique values in PK and FK columns.
-
Whatever = update whatever you need to.
Unique values for pk and fk?? They have to be MATCHING VALUES.
Can you show us the before and after picture of what you want to do???
-
what I mean is how can I INSERT fresh data with the joins I have, so that both tables syncronize with FK values.
-
Can't tell you untill you show us SAMPLE DATA, SAMPLE RESULTS.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply