mister.magoo (1/16/2015)

---

halifaxdal (1/16/2015)

---

Hello, I have a script to be used to backup a specific table in a weekly basis, here is the approach what I take, if you have a better recommendation, it would be appreciated if you can share it with me (and others reading this post):

If not exists (select * from sysobjects where name='EventlogHistory' and xtype='U')

CREATE TABLE [dbo].[EventlogHistory](

[LogID] [int] IDENTITY(1,1) NOT NULL,

[ProjectID] [int] NOT NULL,

[Description] [nvarchar](max) NOT NULL,

[EventType] [varchar](10) NOT NULL,

127.0.0.1 [varchar](50) NOT NULL,

[UserLogon] [varchar](30) NOT NULL,

[CreatedOn] [datetime] NOT NULL,

ArchivedOn datetime default getdate())

Problem #1 : The identity column means you can't be inserting without specifying the columns.

Problem #2 : That table is not an exact replica of the source table, it has at least one extra column, so you can't be inserting into it from a SELECT *.

Problem #3 : Don't ever use insert without a column list, and don't use select *

Problem #4 : If you are using an IDENTITY column it's because you want to have a new set of numbers in this archive table for LogID, or else you would just have it as an int, wouldn't you?

It seems like you might be trying to obfuscate the real problem (as we have all had to do from time to time) and in the process you may be hiding the real problem, because there is no way that an insert into that table without a column list from a select star would even get compiled, let alone run and produce that error.

Of course, it is late and I could be wrong 😎