Copying deleted into temp table in trigger

For some reason in Enterprise Manager for SQL Server 2000, I cannot
put the following line into a trigger:
select * into #deleted from deleted
When I hit the Apply button I get the following error:
Cannot use text, ntext, or image columns in the 'inserted' or
'deleted' tables

This seems like a weird error, since I am not actually doing anything
to the inserted or deleted tables, I am just trying to make a temp
copy.

I have another workaround but I am just curious why this happens.

Thanks,
RebeccaRebecca Lovelace (usagikawai@.yahoo.com) writes:
> For some reason in Enterprise Manager for SQL Server 2000, I cannot
> put the following line into a trigger:
> select * into #deleted from deleted
> When I hit the Apply button I get the following error:
> Cannot use text, ntext, or image columns in the 'inserted' or
> 'deleted' tables
> This seems like a weird error, since I am not actually doing anything
> to the inserted or deleted tables, I am just trying to make a temp
> copy.
> I have another workaround but I am just curious why this happens.

The message is very clear: there is a text, image, or ntext column in
your table, and cannot access that column. And since SELECT * implies
all columns, you access that column.

On another note, I fond recently that "SELECT * INTO #deleted FROM deleted"
in a trigger can be detrimental to performance. In my case, I was
running a one-by-one processing in a long transaction, and one table
had a trigger with a SELECT INTO like this. I had about given up to
get better speed, when I found that taking out the SELECT INTO and
using "inserted" directly gave a tremendous boost,

The reason this was such a winner, was that the locks on the system
tables in tempdb needed for all these temp tables were eating
resources. I also found that SELECT INTO #temp required more locks and
resources than CREATE TABLE #temp did. But there is a better alternative:
table variables, they don't need any tempdb locks at all.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> The message is very clear: there is a text, image, or ntext column in
> your table, and cannot access that column. And since SELECT * implies
> all columns, you access that column.

It would be clear, if there were any of those types of columns in my
table. But there aren't. It's just varchars and ints.

Does this work for you in SQL Server 2000?

It's more of a matter of curiousity at this point, I know this isn't
the best way to go, I just want to know why I can't do it.

Rebecca|||Rebecca Lovelace (usagikawai@.yahoo.com) writes:
>> The message is very clear: there is a text, image, or ntext column in
>> your table, and cannot access that column. And since SELECT * implies
>> all columns, you access that column.
> It would be clear, if there were any of those types of columns in my
> table. But there aren't. It's just varchars and ints.

That sounds very strange. I'm afraid that I don't have any answer. Is
possible for you to produce a script with a CREATE TABLE statement and
a CREATE TRIGGER that demonstrates the problem? In such case, I could
bring it up with Microsoft.

> Does this work for you in SQL Server 2000?

Yes, I have used SELECT * FROM #deleted FROM deleted in triggers with
success.

Well, success and success I have ran into two problems, but I have not
gotten that weird error message you got.

One problem I have mentioned: performance. The other problem may be
worth mentioning too. Just like you I called the temp table #deleted.
But then I had a trigger that updated another table which did the same
thing. This caused problems because when the second trigger was compiled,
#deleted already existed, but with different columns. So *if* you
this kind of thing, don't call the temp tables #inserted and #deleted,
but use some part of the table name to get a unique name.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

copying all rows from one table into another existing table and overwriting data

i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.

any ideas on how i could do this easily?

thanksUSE Northwind
GO

SET NOCOUNT ON
SELECT * INTO myTable1 FROM Employees
GO

-- Have a Look

SELECT * FROM myTable1

-- Make a Backup

SELECT * INTO myTable2 FROM myTable1
GO

-- oops

DELETE FROM myTable1 WHERE EmployeeID BETWEEN 2 AND 6
GO

-- Have a Look

SELECT * FROM myTable1
GO

SET IDENTITY_INSERT myTable1 ON
GO

-- Recover the Rows

INSERT INTO myTable1 (
EmployeeID
, LastName
, FirstName
, Title
, TitleOfCourtesy
, BirthDate
, HireDate
, Address
, City
, Region
, PostalCode
, Country
, HomePhone
, Extension
, Photo
, Notes
, ReportsTo
, PhotoPath
)
SELECT EmployeeID
, LastName
, FirstName
, Title
, TitleOfCourtesy
, BirthDate
, HireDate
, Address
, City
, Region
, PostalCode
, Country
, HomePhone
, Extension
, Photo
, Notes
, ReportsTo
, PhotoPath
FROM myTable2
WHERE EmployeeID NOT IN (SELECT EmployeeID
FROM myTable1)
GO

SET IDENTITY_INSERT myTable1 OFF
GO

-- Have a Look

SELECT * FROM myTable1
GO

-- Clean up

DROP TABLE myTable1
DROP TABLE myTable2
GO