CODEWITHSUNDEEP
sqlsql-server
Konrad Viltersten
Konrad Viltersten

Reputation: 39118

How to INSERT INTO table from SELECT without specifying columns?

Not sure if it's possible at all, especially after scrutinizing some info on the Internet. I'd like to rewrite the following as not to have to specify the columns at all, if possible. Second hand happiness would be having a generic set of columns that accept everything else, independent of type nor number.

The reason for that is, of course, that I'm lazy, sick and tired of rewriting the holder beep for each, single case.

declare @beep table(Hazaa int)
insert into @beep
select WholeSomeValue from ThisOrThatTable

That'd be nice to be able to use something like this.

declare @beep table(GenericColumns ???)
insert into @beep
select * from ThisOrThatTable

Or, at least, like so.

declare @beep table(GenericColumn1 ???, GenericColumn2 ???, GenericColumn3 ???)
insert into @beep
select Col1, Col2, Col3 from ThisOrThatTable

Upvotes: 2

Views: 403

Answers (2)

canon
canon

Reputation: 41675

What about using a temporary table? You don't have to know the structure in advance.

select 
 t.* 
into #beep
from ThisOrThatTable as t

Upvotes: 4

DavidG
DavidG

Reputation: 118957

You can create your own custom table type:

CREATE TYPE dbo.Beep AS TABLE 
(
    Column1 INT,
    Column2 VARCHAR(50)
)

Then use it like this:

DECLARE @beep dbo.Beep

INSERT INTO @beep 
SELECT 1, 'blah'

SELECT * FROM @beep

Upvotes: 0

Related Questions