August 12, 2009 at 12:41 am
Hi,
I have a table called MacMaster with the following column
MacID MacName ClusterID
the ClusterID here is a foreign key referencing the MacID(PK) of the same table.In order words, the clusterID should be a MacID from the same table. Ex
MacID(identity column;PK) MacName ClusterID
1 Mac1 2
2 Mac2 NULL
I was trying to load this table from an another table having following column
MacName ClusterName
Mac3 Mac4
Mac4 NULL
Mac5 NULL
Kindly suggest me a method to load the above data to MacMaster Table.
Finally my MacMaster table should have something like this:
MacID MacName ClusterID
1 Mac1 2
2 Mac2 NULL
3 Mac3 4
4 Mac4 NULL
5 Mac5 NULL
Thanks in advice for your time and suggestion
Regards,
August 20, 2009 at 6:35 pm
Hi,
Any suggestions on this? Is it possible to achieve this through TSql or SP or do i need to create a front end to achieve this.
Regards,
Navee
August 21, 2009 at 7:00 am
If I understand it right, what you're trying to do is, insert records into a table, and then after inserting the records, update the foreign key field, because you don't know what the value of the foreign key will be until after you insert the record?
What you can do is, disable the foreign key, insert the records, then run an update, fix the foreign key constraint, and then re-enable the foreign key.
August 21, 2009 at 7:07 am
Sorry, there's a better way actually,
What you can do is, insert the records with a NULL value for the foreign key field, and then after inserting, run an update and correct any NULL occurrences with their correct foreign key value.
Something like this:
Table Declarations:
DECLARE @MacMaster TABLE
(
MacID INT IDENTITY PRIMARY KEY,
MacName VARCHAR(MAX),
ClusterID INT
)
INSERT INTO @MacMaster(MacName, ClusterID)
SELECT 'Mac1', 2
UNION
SELECT 'Mac2', NULL
DECLARE @MacNameClusterNameTable TABLE
(
MacName VARCHAR(MAX),
ClusterName VARCHAR(MAX)
)
INSERT INTO @MacNameClusterNameTable (MacName, ClusterName)
SELECT 'Mac3', 'Mac4'
UNION
SELECT 'Mac4', NULL
UNION
SELECT 'Mac5', NULL
1Mac12
2Mac2NULL
Initial Insert:
INSERT INTO @MacMaster (MacName, ClusterID)
SELECT mct.MacName, mm.MacID
FROM @MacNameClusterNameTable mct
LEFT JOIN @MacMaster mm ON mct.MacName = mm.MacName
1Mac12
2Mac2NULL
3Mac3NULL
4Mac4NULL
5Mac5NULL
After Update:
UPDATE mm
SET ClusterID = mm2.MacID
FROM @MacMaster mm
JOIN @MacNameClusterNameTable mct ONmct.MacName = mm.MacName
AND mct.ClusterName IS NOT NULL
JOIN @MacMaster mm2 ON mm2.MacName = mct.ClusterName
1Mac12
2Mac2NULL
3Mac34
4Mac4NULL
5Mac5NULL
By the way, you really should post those table declarations and insertions with the question, since it saves people time when trying to help you.
August 23, 2009 at 10:04 pm
Hi kramaswamy,
Thank you so much!!!
Regards,
Naveen
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply