>Unfortunately, this is not that simple.
>
>I tried
>
>IF object_id('tempdb..#temp_Registration') is not null
> insert into Registration select * from #Temp_Registration
>
>But I'm getting an error Msg 213, Level 16, State 1, Line 10
>Insert Error: Column name or number of supplied values does not match table definition.
>
>I guess I need to research this problem in more details ...
>
>>>Hi everybody,
>>>
>>>I want to have a create table script which I can execute multiple times (so I would need to have drop statements first).
>>>
>>>I also would like to be able to preserve existing data.
>>>
>>>Is there a way to write such a script?
>>>
>>>Thanks a lot in advance.
>>
>>Isn't there something like a temp table in sqlserver ?
Here what SQL Server script when you insert Identity field:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_ALLVAR
(
MES tinyint NOT NULL,
GOD smallint NOT NULL,
Idd int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_ALLVAR OFF
GO
IF EXISTS(SELECT * FROM dbo.ALLVAR)
EXEC('INSERT INTO dbo.Tmp_ALLVAR (MES, GOD)
SELECT MES, GOD FROM dbo.ALLVAR WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.ALLVAR
GO
EXECUTE sp_rename N'dbo.Tmp_ALLVAR', N'ALLVAR', 'OBJECT'
GO
ALTER TABLE dbo.ALLVAR ADD CONSTRAINT
PK_ALLVAR PRIMARY KEY CLUSTERED
(
MES,
GOD
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
As you see first it drop every constraints of the table.
Then create a real table (not #temp one)
insert values from old one, drop old one and then rename temp table as old one.
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.