CODEWITHSUNDEEP
sql-servert-sql
Christian4145
Christian4145

Reputation: 543

How to insert with table creation

I am looking for a more appropriate way to execute several inserts into a nonexistent table. To create the table beforehand is not easily possible, as I don't know the data type of the selected column. An "insert with create" would do, but I don't think there is anything 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 it, in an example very much stripped down to demonstrate 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 become an issue.

Upvotes: 0

Views: 97

Answers (2)

gofr1
gofr1

Reputation: 15977

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.

Upvotes: 1

Rahul Bhola
Rahul Bhola

Reputation: 11

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.

Upvotes: 0

Related Questions