error with SELECT INTO in trigger

Hi.
I have a little (big) problem with a trigger. I think the fault is in the MSSQL side, not on the mine - I hope you can give me some suggestion (you are my only hope [] ).
I have this sql query in a trigger:
"SELECT * INTO newtable FROM oldtable"
When I try to run this query, it is just fine - it works how I need. But when I run it in trigger it always gives me this error:
"There is already an object named 'newtable' in the database"
I am 100% sure that there is no such object, tried different names - but this is not the problem I think. The trigger I have done is very simple and I controlled it quite well, I think - the only problem is with the SELECT INTO.
Do I have to turn something on/off in trigger? I really don't understand why it doesn't work []
Thank you for your help!
Milan

Is newtable a temp table ?
Can you paste your trigger script ?
Cheers
Sat

I Modified the script and it worked for me, it does not like the SELECT INTO for some reason
CREATE TRIGGER tr_EMP_INSERT
ON EMPLOYEES
FOR INSERT
AS
INSERT INTO EmployeeTer
SELECT id FROM employees where id >9
Regards
Sat

Wow, that was really quick
Thanks, I will try to do something and I'll send you my trigger in the evening
Thank you!!!

Note that these are two fundamentally different actions:
(1) SELECT columns INTO target FROM source
This creates a new table, then inserts the data into it. The second time that this syntax is executed, the query fails because there is already a table under that name.
(2) INSERT INTO target SELECT columns FROM source
This inserts data into an existing table.
You should not use the (1) syntax in production, even if you do need to create a new table, or a temp table. This is because the syntax locks the tables in your FROM clause, and may do so for the duration of the procedure (or in this case the trigger). This can hold up other processes that need to access those tables.
So if you need to create a (temp) table, use the CREATE TABLE syntax, and use an INSERT INTO query. Also consider using table variables, instead of temp tables.

After modifying the trigger script as suggested, you still need to check the existance of table else insert will fail. so creation of this table, you can add as primary task during deployment.

In production, all permanent tables need to be in place. If you need to check for existence of (temporary) tables, then you should look at the flow of your procedure: either you've already created a table, or you have not - no need to check at run-time (except perhaps global temporary tables).
I would be very interested to know under what kind of scenario you could ever need to create a new permanent table from a trigger. Sounds like either a highly complicated data model, or a poorly designed data model.
Original poster was using SELECT INTO instead of INSERT INTO, that's why he got the error on the second execution of the trigger.

Ok, I will explain my trigger.
It is used for a geodatabase - I have a metadata table (called met.METADATA) where is info about geodata in the database. One of columns of this metadata table is a boolean "archive"
e.g.:
METADATA: name=CITIES, ...(more info)..., archive=TRUE
This means that in the database there is a table dbo.CITIES and I want to archive this table (e.g. I work with it and I need to have a history/backup in the database).
So - I am gonna create a trigger for the table met.METADATA - when the user checks "archive" to TRUE, it will copy the table dbo.CITIES into new table archive.CITIES. I have much more work there (I will add some more columns to this archived table) - but this is of no concern.
First I have to create this trigger - but I have no luck there
I know that the INSERT INTO can only copy data to an existing table - so now I am trying to do an sql query that would CREATE TABLE just the same as the table (e.g. CITIES) is. Do you have any suggestions with this? I have found 2 ways:
1. a simple query - something like "CREATE TABLE archive.CITIES LIKE dbo.CITIES" (but this doesnt work - that was a stupid suggestion I found on the internet)
2. a really VERY complicated query selecting from INFORMATION_SCHEMA.Columns and then somehow make this into CREATE TABLE archive.CITIES (@col1 @isnull1 @datatype1, @col2,.........) - pleeeeeaaaase give me another solution - this is really crazy to program it [:#] !
Here is my not working trigger (just a part of it):
CREATE TRIGGER [met].[METADATA_archivation] ON [met].[METADATA]
WITH EXECUTE AS CALLER
FOR UPDATE
AS
BEGIN
/* Trigger body */
declare @archive bit
declare @table varchar(255)
declare @query varchar(320)
SELECT @archive = (SELECT archive FROM inserted)
SELECT @table = (SELECT name FROM inserted)
IF (@table is not null)
BEGIN
IF (@archive = 1)
BEGIN
SET @query = 'SELECT * INTO [archive].['+@table+'] FROM [dbo].['+@table+']'
EXEC (@query)
END
IF (@archive = 0)
BEGIN
SET @query = 'DROP TABLE [archive].['+@table+']'
EXEC (@query)
END
END
END
GO
So - do you have any ideas, please? .... I will be grateful

Hi,
If I have understood your question, then I feel you dont need trigger at all. value for archive column in metadata table will be updated through sp query, so before or after firing update on the table, in the same SP you can retrive the table name updated.
Then check for the existance for new table using
IF object_id('newtable') IS null Create table
Else insert the data from old table

Hi.
Thanks, bu I am afraid I don't get it.
Well, maybe it is some misunderstanding - I need to create a copy of a table that was checked by user to archive it. How can I do it other way than by a trigger for the "archive" column?
Well, I work with database only for one year and I don't know everything about it - I am sorry but I don't know what do you mean by "sp query"...

How difficult can it be - you create the archive table as part of your database, so your trigger doesn't have to check for its existence. Other than that I agree with Ranitjain that if you are always using sprocs to implement the archiving, then you don't have to rely on a trigger, but should just build it into the sproc.

Ok - so you mean to manually create archive tables for all of the tables in geodatabase.
But there are 2 reasons I can't do it this way:
1. I have to make it automatically - it will be a part of an application - user uploads a geodatafile into the database using a special query defined in a DLL - I cannot edit this query to let it create 2 same tables so I still will need to create the table automatically - I really don't know how (something like a function duplicateTable would be good, but I didn't find it).
2. There will be about 1000 geotables - and only some of them have to be archived (that's why I wanted to create the trigger for the "archive" boolean column). If I will create 1000 archive tables and only 30 will be the backups - and the rest will be empty - I think it is just uneffective
You are talking about some sprocs, sp queries - I really don't know what you mean by this. Can you explain it to me better? Or is there any online documentation about this feature?
Thank you
Milan

Does the upload HAVE to be to a new table each time? Why not have an 'upload identifier' column - assuming that the data structure is identical for each upload?

Well, yes, when the user uploads a file CITIES.shp it creates a table dbo.CITIES. This is a fact - it is because every .shp file can have another structure.
But I think this is all of no concern - I really think that the idea of archiving via trigger is the best for my situation. So - there really doesn't exist any simple query for creating identical table? Or just creating a table with the identical structure? That would be most helpful for me.
Thanks!

So a user uploads a file CITIES.shp and this "creates" a table dbo.CITIES in your database. I assume that the "creation" is not a spontaneous event - there must be some application code that takes the shp file and turns it into a table.
You should be able to add to this same code a step which executes SELECT * INTO dbo.CITIES_ARCHIVE FROM dbo.CITIES WHERE 0 - you are correct that this is the easiest way to duplicate a table structure without specifying the column details. (By the way, this does not cover any foreign keys, indexes, permissions, defaults, check constraints or triggers defined for dbo.CITIES.) The WHERE 0 clause means you're creating an empty table.
Now your trigger can assume that the table exists.
And if you still want to do the check in the trigger, then use something like this:

IF OBJECT_ID('archivetable') IS NULL
SELECT * INTO archivetable FROM table
ELSE
INSERT INTO archivetable SELECT * FROM table

Yes, well that is useful I think. But again there are 2 reasons I can't do it:
1. the code used to create the table from .shp file is written in a .dll library, so I can't edit the code (I think these external libraries are called CLR - not sure). It is used by typing ST.ImportFromShapefile(filename). Maybe it is possible to make somehow an alias for this function - but I think that would take me to the same situation I am now
2. if this trigger would work it would be just the thing I need! But it won't work... As I wrote about this problem - the query SELECT * INTO table2 FROM table1 really doesn't work in triggers! I have searched the internet more - and it is said that this is just an mssql fault - but there is no microsoft update solving this thing

I tried this and is working for me on SQL 2005 enterprise edition with SP1.CREATE TRIGGER dbo.redtriggerTEST
ON dbo.TestTableAFTER INSERT
ASBEGIN
SET NOCOUNT ON;IF
object_id('redtest') is nullbegin
select
top 0 * into redtest from testtable --this is to create table schemaend
else
begin
insert
into redtest select columns from testtableend

Well, that's really interesting!
So why it gives me the error? On the net there are really more people with the same problem - but I couldn't find the solution
Maybe the thing is that I am using MS SQL 2005 Express Edition but I think it has to be the same as Enterprise (with some little limits)
Well I will try the "set nocount on;" or something but I don't believe this is the thing.
Thank you that you tried it - I really don't know why it isn't working for me

One problem with ranitjain's script is the TOP 0 clause under the IF. It means you're not actually inserting the data the first time, while I assume that you do need it copied over immediately.
You'll have to check for any restrictions on triggers, specific to Express Edition - it sounds bizarre.