Home How to insert with table creation?
 I am looking for a more propriate way to execute several inserts into a nonexisting table. To create the table before is not easily possible, as I don't know the datatype of the selected column. An "insert with create" would do, but I think there is nothing like that. Is there any better way to do so than to select into and then to insert? Here is the "bad" way I do in an example very much striped down to the problem. set nocount on declare @name sysname = '', @i int = 0, @sql nvarchar(4000) = '' declare test cursor for select top 10 a.name from sys.tables a inner join sys.columns b on a.object_id = b.object_id --and b.name = 'description' open test fetch next from test into @name while (@@FETCH_STATUS <> -1) begin if @i = 0 begin set @sql = 'select distinct top 10 description into #t1 from ' + @name + '' select @sql -- exec sp_executesql @sql end else begin set @sql = 'insert #t1 select distinct top 10 description into #t1 from ' + @name + '' select @sql -- exec sp_executesql @sql end set @i = @i + 1 fetch next from test into @name end close test deallocate test if object_id ('tempdb..#t1') is not null select * from #t1  This solution is "bad" as you need the statement at two positions. In the case shown here this is trivial. But when the statement gets more complex this can be an issue.