I am in the process of writing a trigger that will insert a new record into
another table when a record is added to a specific table. I was wondering if
there is a way to just 'copy' the entire row without using INSERT INTO
...and VALUES?
adv-thanks-anceYou could use
INSERT INTO [AnotherTable]
SELECT * FROM inserted
assuming that [AnotherTable] has the same number of columns, in the same
order, with the same datatypes as the table that has the trigger on it.
"CSHARPITPRO" wrote:
> I am in the process of writing a trigger that will insert a new record int
o
> another table when a record is added to a specific table. I was wondering
if
> there is a way to just 'copy' the entire row without using INSERT INTO
> ...and VALUES?
> adv-thanks-ance|||Thanks Mark,
Is this being copied from the 'INSERTED' table in SQL?
"Mark Williams" wrote:
> You could use
> INSERT INTO [AnotherTable]
> SELECT * FROM inserted
> assuming that [AnotherTable] has the same number of columns, in the same
> order, with the same datatypes as the table that has the trigger on it.
> --
> "CSHARPITPRO" wrote:
>|||I am not sure if there is a way to do this (I have not seen one) but I would
avoid this like the plague.
The tables are identical today, but who knows what will change tomorrow.
Something as simple as adding and audit or archive date to either table
would break any app using such an approach. Structures have a tendency to
evolve over time, and any code that assumes two structures will be identical
is begging for trouble down the line.
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:0078FFA9-4BEB-4A92-9949-CA8B733E7B63@.microsoft.com...
> I am in the process of writing a trigger that will insert a new record
into
> another table when a record is added to a specific table. I was wondering
if
> there is a way to just 'copy' the entire row without using INSERT INTO
> ...and VALUES?
> adv-thanks-ance|||Yes. If all you want to do is a straight copy of the rows inserted into the
table with the trigger on it, you can use the syntax
INSERT INTO [AnotherTable] SELECT * FROM inserted
inserted is the virtual table that conains the inserted rows that caused the
trigger to fire. Note that inserted will also contain rows when an UPDATE is
performed (The new values will be in the inserted virtual table, and the old
values will be in the deleted virtual table).
--
If"CSHARPITPRO" wrote:
> Thanks Mark,
> Is this being copied from the 'INSERTED' table in SQL?
> "Mark Williams" wrote:
>|||Thanks Jim,
What do you think the best method is to copy the 'changed' records to
my AuditTable with out using the INSERTED Table? Should I just use INSERT
INTO ..and VALUES?
Thanks
"Jim Underwood" wrote:
> I am not sure if there is a way to do this (I have not seen one) but I wou
ld
> avoid this like the plague.
> The tables are identical today, but who knows what will change tomorrow.
> Something as simple as adding and audit or archive date to either table
> would break any app using such an approach. Structures have a tendency to
> evolve over time, and any code that assumes two structures will be identic
al
> is begging for trouble down the line.
>
>
> "CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
> news:0078FFA9-4BEB-4A92-9949-CA8B733E7B63@.microsoft.com...
> into
> if
>
>|||I would use insert into with a specific list of values.
i.e. Insert into table1 (field1, field2, field3) values (select field1,
field2, field3 from inserted)
As everyone else mentioned you can use "insert into table select * from
inserted", but if any structures change you will runinto trouble.
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:C1DFF71C-B888-42FC-A6EB-E115C2C25EC0@.microsoft.com...
> Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
> my AuditTable with out using the INSERTED Table? Should I just use INSERT
> INTO ..and VALUES?
> Thanks
> "Jim Underwood" wrote:
>
would
to
identical
wondering|||Yes. Performance and consistency will both be served if you do so. It does
require a bit more work now, and a bit more when you make changes, but if
you design things, this won't be too much of a problem. You can also use
the information_schema.columns view to get the columns that are needed and
build a quicky insert generator (or use the ones in QA/SSMS) to do much of
the hard work of typing out column names.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:C1DFF71C-B888-42FC-A6EB-E115C2C25EC0@.microsoft.com...
> Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
> my AuditTable with out using the INSERTED Table? Should I just use INSERT
> INTO ..and VALUES?
> Thanks
> "Jim Underwood" wrote:
>|||On Wed, 18 Jan 2006 09:23:03 -0800, CSHARPITPRO wrote:
>Thanks Jim,
> What do you think the best method is to copy the 'changed' records to
>my AuditTable with out using the INSERTED Table? Should I just use INSERT
>INTO ..and VALUES?
Hi CSHARPITPRO,
No. INSERT ... VALUES inserts just one row at a time; a trigger can be
fired with a large number of rows in the inserted pseudo-table.
Use
INSERT INTO Tablename (Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM inserted
Hugo Kornelis, SQL Server MVP
Kolf wrote: