Rebecca Lovelace (us********@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,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
