/*0. create test destination table for you*/ select 'Mr. Robin Rex' as CustomerName, identity(int,1,1) as CustomerID into dbo.Destination_table -- drop table dbo.Destination_table
/*1. Create the #Temp table from the destination table: */ select top 0 * into #Source from dbo.Destination_table -- DROP TABLE #Source
/*2. This is key of the solution! */ alter table #Source drop column CustomerID
/*3. Create new data or what ever it is you need to do */ insert into #Source select 'Mrs. Tom' union all select 'Dr. French'
/*4. The following will then work NO FIELDS SPECIFIED! */ insert into dbo.Destination_table select * from #Source
/*5. look at the results! */ select * from dbo.Destination_table