Home How to insert with table creation?
Reply: 0

How to insert with table creation?

user1487
1#
user1487 Published in July 17, 2018, 7:57 am

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.33977 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO