December 3, 2006 at 11:15 pm
I have 2 tables one table "A" is Staging Table which is in a de-normalize fashion & doesn't as any constraints or Identity column. Table "B" is DEV table which is Normalize & has PK & Identity column. my question is when i transfer Data from Table "A" to Table "B" it is not going to work & will fail due to constraint & identity. how i can resolve this issue, IDENTITY Column is must for me to, so as to have each customer with Unique ID & even though customers are repeated still they will have same ID. any help is appreciated. thanking in advance.
December 4, 2006 at 8:20 am
Can you be more specific? Does table "A" have the same number of column as table "B" except there is no constraint and identity column ?
For example:
Table A (col1 varchar(10), col2 varchar(20))
Table B (TableID INT IDENTITY(1,1), col1 varchar(10), col2 varchar(20)) and col1 is the foreign key to another table.
December 4, 2006 at 8:46 am
Table "A" as 20 columns & some are Char & Varchar. Table "B" as 7 columns including COL1 as Identity & PK.
There is also Table "C" which as 7 columns & one of the column which is Identity & FK refreences to Table "B". please let me know if this helps. thanks again. appreciate your help.
December 4, 2006 at 10:28 am
So I assume Table B the identity column is the customer key and the customer may exist in table B already.
You should do two steps, one is insert customer that is not existed in B and the other is updated the customer that is already in B.
For example:
Table A (A1 varchar(10),.....A20 varchar(10))
Table B (col1 INT IDENTITY, col2 varchar(10)...col7 varchar(10))
Table C(c1 varchar(10) ....c7)
Assume A1 is the customer name and you need to put in col2 and col7 is foreign key table C column c1.
INSERT INTO TableB (col2, ....col7)
SELECT A1, A3....A7
FROM TableA a LEFT OUTER JOIN TableB b ON a.A1 = b.col2
INNER JOIN TableC c ON b.col7 = c.c1
WHERE b.col2 IS NULL
Table B col1 is an identity field so it will populate the value by itself. The where clause makes sure the customer is not in table B. The inner join of Table C makes sure the column in table B is also in table C.
UPDATE TableB
SET col3 = ....
FROM TableA a INNER JOIN TableB b ON a.A1 = b.col2
INNER JOIN TableC c ON b.col7 = c.c1
When TableA inner join TableB, that means the customer exists in both tables, and inner to table C makes sure the foreign key constraint is valid.
I hope this helps.
December 4, 2006 at 11:30 am
Let me see that if this logic works & will update accordingly. once again thanks for your time & help.I appreciate that.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply