INSERTING DEFAULT VALUES INTO TARGET TABLE
-
Hello
I need help in inserting records from one table into another. Sounds simple enough, right ? Well I'm using a trigger to move the INSERT table into a target table based upon 14 fields in another table. Lets say if the field PHONE is updated or if a record is added to the first table, then I want this record inserted into a second table. No problem. But....I only want populated fields sent to the second table. If a field in the first table is NULL, then I want to populate the second table with the default value defined in the second table.
Make sense ? Any ideas ?
-
Can you write a sub query to insert the defaults?
INSERT INTO tblA
(A, B, C)
SELECT A, B, C
FROM tblB
WHERE tblB.D = '?'
-
If you pass a value, (even a NULL) to table, then the DEFAULT set on the table will not fire. In other words, TableA has [id], [text], [date] with a DEFAULT of '01/01/1900' for the [date]. If you write an INSERT INTO TableA( [id], [text], [date] SELECT x, y, z FROM TableB, and z IS NULL, then the DEFUALT will not work.
You probably need to use the ISNULL function.
I wasn't born stupid - I had to study.
-
I believe the best way is to replace the values for explicit defaults on the inserted table using isnull() but if you still want code independence (I mean if you change a default or add a default you don't have to change trigger code) there is a very clumsy solution which I don't personally recomend but for the fun of it here you go:
Assuming the detination table has as primary key and identity column named ID
begin transaction
insert into table2 default vaules
if @error <> 0 goto QuitWithRollback
select @id = scope_indentity()
insert into table2 ( fld1, fld2, fld3)
select
isnull(i.fld1,def.fld1),
isnull(i.fld2,def.fld2),
isnull(i.fld3,def.fld3)
from
inserted i
cross join
(select * from table2 where id = @id ) def
if @error <> 0 goto QuitWithRollback
delete from table2 where id = @id
if @error <> 0 goto QuitWithRollback
commit transaction
return
QuitWithRollback:
if @@trancount > 0 rollback transaction
This is how it would look with the explicit defaults
insert into table2 ( fld1, fld2, fld3)
select
isnull(i.fld1,'Default1'), -- simpler right ? but code maintenance is a must
isnull(i.fld2,'Default2'),
isnull(i.fld3,'Default3')
from
inserted i
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply