September 13, 2004 at 9:22 pm
Okay,
I have a table that holds "pending info". It has an identity column. Once the info is no longer pending, it needs to be inserted into two permanent tables. Here's an example
Pending Info table:
IdentityColumn Value1 Value2
Permanent Info Parent Table:
IdentityColumn, Value1
Permanent Info Child Table:
ForeignKeyField, Value 2
How can I insert the information from the Pending Info table into the Permanent Parent and Child Tables? I figured out how to do it one record at a time, but it takes too long if there are many records. I'm hoping there is a way to do it using INSERT Into PermanentInfoTable SELECT from PendingInfoTable (if you get my meaning)...
September 14, 2004 at 6:53 am
If ForeignKeyField is available from PendingInfo I don't see why you couldn't do the following three statements:
INSERT INTO PermanentInfoTable SELECT IdentityColumn, Value1 FROM PendingInfoTable
INSERT INTO PermanentInfoChildTable SELECT ForeignKeyField, Value2 FROM PendingInfoTable
DELETE FROM PendingInfoTable
(or "TRUNCATE TABLE PendingInfoTable" if there is no WHERE clause)
If ForeignKeyField is only visible from PermanentInfoTable then the second INSERT could be
INSERT INTO PermanentInfoChildTable SELECT ForeignKeyField, PendingInfoTable.Value2 FROM PendingInfoTable LEFT JOIN PermanentInfoTable ON PermanentInfoTable.IdentityColumn = PendingInfoTable.IdentityColumn
If you are concerned about PendingInfoTable rows being added/updated in the middle of the process you could pull them into a @TempTable first and use the following:
INSERT INTO @TempTable SELECT IdentityColumn, Value1, Value2 FROM PendingInfoTable
INSERT INTO PermanentInfoChildTable SELECT ForeignKeyField, TT.Value2 FROM @TempTable TT LEFT JOIN PermanentInfoTable ON PermanentInfoTable.IdentityColumn = TT.IdentityColumn
INSERT INTO PermanentInfoChildTable SELECT ForeignKeyField, Value2 FROM @TempTable
DELETE FROM PendingInfoTable WHERE PendingInfoTable.IdentityColumn IN ( SELECT IdentityColumn FROM @TempTable )
I hope I'm not way off base here on what you are trying to do. Although I've never used a trigger before I suppose you could create a DELETE trigger on PendingInfo that would move the content over to the permanent and child tables, but I don't think you would get any performance gains out of that at all and it seems like a kludgy bad idea to me.
Hope this helps.
September 14, 2004 at 12:26 pm
Thank you for your reply. The problem is that I can't carry the key field from the pending table to the permanent table. The permanent table is going to assign a new value to the key field as the records are added to it.
September 14, 2004 at 1:10 pm
Consider adding an unofficial foreign key to the Permanent table. You can then use the @Temp table approach I posted earlier except between the INSERT into the Permanent table and the INSERT into the child table(s) you would run an UPDATE on the @Temp table to populate the (additional) column containing the Permanent table's key.
UPDATE @TempTable TT SET PermKey = PermanentInfoTable.IdentityColumn
LEFT JOIN PermanentInfoTable ON PermanentInfoTable.PendingKey = TT.IdentityColumn
My UPDATE with JOIN syntax is a little weak so that may not quite be valid SQL, but should get you going. The downside to this is that you have to permanantly add this PendingTable's key to every row in your parent Permanent table, but I'll let you judge whether its warrented.
As an alternative, you could still cursor through @TempTable and individually INSERT into the parent Permanent table and then UPDATE WHERE CURRENT OF on the @TempTable with the generated identity column within the parent PermanentTable. This would avoid adding the column to the real table and still allow bulk inserts into the child table(s).
P.S. If you went the add a permanant column to the parent table route you could make the PendingKey column NULLable (or empty string if varchar) and then run an UPDATE on the table to NULL/empty out the column once you are done with it. This should reduce the storage size of the row so that more things could fit on the page, but it may not take effect until the table is reloaded and so may not ultimately be of any benefit (if clustered DBCC DBREINDEX should make it take effect).
September 14, 2004 at 2:46 pm
Thanks a lot, I'll give it a shot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply