2

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
Improve this question
8
  • That is quite sad. You posted a question on a forum because you are lazy and want to avoid writing explicit code? You should ALWAYS specify the column list in your insert AND your select statements. There is no shortcut for writing solid code. Intellisense makes this kind of thing quite simple. Commented Aug 13, 2014 at 15:28
  • @SeanLange I'm not blessed with intellisense. Also, this code is for probing the DB while trying to figure out some things. In the deliverable code, I'd never do such a thing. Thanks for pointing that out. I forgot to mention that detail in the question. :) Commented Aug 13, 2014 at 15:30
  • Second hand happiness would be having a generic set of columns that accept everything else, independent of type nor number, then why use a relational database at all?, you can use excel or a flat file Commented Aug 13, 2014 at 15:31
  • @Lamak I'm probing a DB to see what values I can pick up in order to recognize something. Then, I'll need to plug in those values to keep looking for others. It's quite poorly documented and the naming leaves a lot to wish. Also, I'm not sure I even have Excel on this computer not that it'd do me much good at this stage, hehe.) Commented Aug 13, 2014 at 15:35
  • 1
    @SeanLange If you think the OP's question or approach is bad, you should either downvote, or make a constructive comment explaining what's wrong. There is absolutely no need to insult someone on this site. Commented Aug 13, 2014 at 15:40

2 Answers 2

Reset to default
4

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

select 
 t.* 
into #beep
from ThisOrThatTable as t
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I've been instructed to use declared instead of temporary tables with basis of the former being "better" than the latter (without an actual specification of in what way that "betterness" would exhibit). I'm open to going by your suggestion, though.
@KonradViltersten better how?, please read this link to understand clearly what are the differences between a temp table and a table variable
This answer is merely predicated on, "facilitate my laziness." This is as easy as I can make it. The only way to use a table variable would be to leverage dynamic SQL to build up the declaration (column-names, types, etc) from the schema. That's a little more complicated than it would seem you'd like.
@canon I think I wasn't clear. I love your reply (can't check it as an answer yet, though). I was just explaining why I didn't went for the temporary option. Also, I wanted to check with you of there was some huge disadvantage to using temps that I forgot to ask about. There's absolutely nothing I can see as insufficient or wrong with your answer, matie. Just the good stuff, there.
Agreed! I was sarcastic in my comment. I got a "do this - it's better". When I asked in what way I got a frown back. As for the data, it's just me poking around so pretty much anything will do. It's +1 to you shortly. Thanks!
|
0

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
Improve this answer

5 Comments

Thanks. How does that resolve my issue of not wanting to redesign the temporary table for each different reading? I'm not very good at SQL so I'm simply missing it. :)
I started writing this before you updated your question so it doesn't really help any more :)
Not reading minds, are we? Shame on you! (Seriously, though - my bad. Sorry. +1 for helpful suggesting at that time.)
I guess that counters the random downvote I got for it too haha. I'll leave it on here anyway as it's quite a cool feature.
Just for the record - downvote isn't from me. I (almost) never downvote. In fact, you got +1 from me as explained above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.