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



Did you have any constraints of that table?
If not then you could use this (after a really good test of course)

SELECT * INTO dbo.Tmp_YourTable FROM YourTable
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.Tmp_YourTable', N'YourTable', 'OBJECT'
GO