>>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:
>

>/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
>/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
>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.

It doesn't help me much because the script needs to know the structure of the original table. I was trying to create something similar, but I didn't want to list every field.

Anyway, I already lost the data (there were just test data and I have insert scripts saved somethere), so no big deal.

Now, it's really time to work on the script.