Home How to insert with table creation?
Reply: 2

How to insert with table creation?

Christian4143
1#
Christian4143 Published in 2017-12-05 12:39:35Z

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.

Rahul Bhola
2#
Rahul Bhola Reply to 2017-12-05 14:04:16Z

The SELECT INTO statement copies data from one table into a new table, this might help you.

Example:-

SELECT * INTO newtable FROM oldtable WHERE condition

The above also supports joins.

gofr1
3#
gofr1 Reply to 2017-12-05 15:13:06Z

You can simplify your query into this one:

set nocount on

declare 
    @name sysname = '',
    @i int = 0,
    @sql nvarchar(4000) = N''

if object_id ('tempdb..#t1') is not null DROP TABLE #t1

;WITH cte AS (
    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' 
)

SELECT @sql = @sql + N'UNION ALL 
select distinct top 10 description 
from ' + QUOTENAME([name])  + CHAR(13)
FROM cte

SELECT @sql = N';WITH cte AS (' + STUFF(@sql,1,10,') SELECT * INTO #t1 FROM cte')

PRINT @sql
--EXEC (@sql)

select * from #t1
  • No cursor or while loop;
  • Temporary table is dropped (if exists) before query execution;

You got a weird query, as for now it takes the first table from sys.tables and SELECT TOP 10 Descriptions from this table as many times as there are columns in this table.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO