October 31, 2010 at 10:25 am
Table A:-
ID(P.K),ADD,STREET#
Table B:-
ID(P.K),LNAME,FNAME,ADD,L_ID(link to Table A)
QUESTION:- How i can Link Table B to Table A.
I want Table B.L_ID = Table A.ID. Table A and Table B is already created and has data.
How we can do in SSIS/T-SQL.
Please reply. Thanks.
October 31, 2010 at 10:42 am
Hi there,
Syntax is as below:
I don't really understand on what criteria you would like to link the data together though?
UPDATE B
SET B.L_ID = A.ID
FROM TableB B
INNER JOIN TableA A ON A.? = B.?
Andreas Goldman
Andreas Goldman
October 31, 2010 at 12:47 pm
Update Table B
set I_id = tableA.id
from tableB
inner join tableB
on tableA.snumber = tableB.snumber
and tableA.add = tableB.add
"Query Success 0 row infected"
Please guide me where i am wrong.One more thing
tableA.Snumber = 1 2 3 4
tableB.Snumber = 1234
How i can delete space and match with tableA and tableB?
October 31, 2010 at 1:30 pm
If the problem is with the spaces, and addresses are actually matching, the following should work
UPDATE B
SET B.L_ID = A.ID
FROM TableB B
INNER JOIN TableA A
ON A.Snumber = REPLACE(B.Snumber, ' ', '')
AND A.add = B.add
Andreas Goldman
October 31, 2010 at 2:02 pm
Sorry, just realized spaces occured in the first table
UPDATE B
SET B.L_ID = A.ID
FROM TableB B
INNER JOIN TableA A
ON REPLACE(A.Snumber, ' ', '') = REPLACE(B.Snumber, ' ', '')
AND A.add = B.add
Andreas Goldman
October 31, 2010 at 2:03 pm
I really appreciate your prompt reply.
Now i m getting this error "A.Snumber could not be bound" :crying:
October 31, 2010 at 2:18 pm
Is your database case-sensitive?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2010 at 2:32 pm
its work now but result showing "Only 1 row effected"
but i m sure A.snumber and B.snumber are same except dashes.
Any advice?
Once again Thanks all of you guys.
October 31, 2010 at 2:39 pm
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2010 at 2:59 pm
Create Table A (
ID Varchar(15),
ADDR VARCHAR(20),
STREETNOVARCHAR(25)
)
Create Table B(
ID Varchar(15),
LNameVarchar(20),
FNameVarchar(20),
ADDRVarchar(20),
L_IDVarchar(15)
)
-- Insert values in Table A
Insert into A Values ('1','abc','2')
Insert into A Values ('2','xyz','5')
Insert into A Values ('3','abc','9')
--Insert values in Table B
Insert into B Values ('12','Frank','Smith','2','1')
Insert into B Values ('23','Frank','Smith','5','1')
Insert into B Values ('32','Frank','Smith','9','1')
Note:- I want to link Table B with Table A,
Table A = ID
Table B = L_ID
I want same value Table A "id" has in Table B "L_ID". Please feel free to let me know if need more info. Thanks for your help.
October 31, 2010 at 3:05 pm
That doesn't match with queries or data examples that you've given previously in this thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2010 at 3:23 pm
This is just a example bro! The original tableA has 42 fields and TableB has 34.
October 31, 2010 at 3:31 pm
So?
If you want a query that will work give us the correct table structure and sample data that matches the problems in the real data. Leave out irrelevant columns if you like, but include necessary ones, like the join columns. Otherwise you'll get something that doesn't do what you want.
Now, which columns are supposed to be equal for the join? you previously said 'A.snumber and B.snumber', but neither column is in that sample table structure. I could guess (A.STREETNO = B.ADDR) but that's just a guess and if I'm wrong I've wasted your time and mine.
What should Table A column Id look like after the update?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2010 at 3:53 pm
Let me re post my reply with correction and let me know if u still want me to copy structure table and data?
Create Table A (
ID Varchar(15),
ADDR VARCHAR(20),
STREETNOVARCHAR(25)
)
Create Table B(
ID Varchar(15),
LNameVarchar(20),
FNameVarchar(20),
ADDRVarchar(20),
L_IDVarchar(15)
)
-- Insert values in Table A
Insert into A Values ('1','abc','2')
Insert into A Values ('2','xyz','5')
Insert into A Values ('3','abc','9')
--Insert values in Table B
Insert into B Values ('1','Frank','Smith','2','1')
Insert into B Values ('2','Frank','Smith','5','1')
Insert into B Values ('3','Frank','Smith','9','1')
Note:- I want to link Table B with Table A,
Table A = ID
Table B = L_ID
I want same value Table A "id" has in Table B "L_ID". Please feel free to let me know if need more info. Thanks for your help.
October 31, 2010 at 3:58 pm
The information on how to join the tables (on what values/columns) is still missing. Joining on the ADDR column, which would be my guess to would give no matches, so yes, I think you need to publish the real structure and data.
Andreas Goldman
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply