Add Columns to Existing Table 3

[kjv1611]

This may sound kind of hair-brained. If so, please forgive me. But I can't have too many hair-brained ideas, really, seeing as I don't have much hair to begin with.

I am wanting to know what would be the correct/best syntax to use when I want to query existing data from other tables/views, and add certain columns from those tables/views into an "mytable", and include the data that matches the records in "mytable."

I saw the ALTER command for adding columns, but is that the only way to go about this? Is there some way I can have SQL figure out the correct column attributes, other than name, from the source tables?

Can an "INSERT INTO" statement perhaps work in this way?

Thanks for any thoughts/suggestions/examples.

--

"If to err is human, then I must be some kind of human!" -Me

 

[AlexCuse]

I don't think there are many options for adding a column (with data) to an existing table, except maybe to create a custom stored proc that uses dynamic SQL (probably better to just do this manually IMO). But, say you wanted a few columns from tableX, and one column from tableY in your table.

You could do something like this:

select x.PK_COL, x.BLAH_COL, x.DATE_COL, y.NOTES
into NEW_TABLE
from tableX x inner join tableY y
on x.PK_COL = y.PK_COL

Data types and column names will be carried into the new table, but you've gotta add the indexes, constraints, etc...

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[SQLDenis]

create a view, this way the values are always up to date and you don't have to synch

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[kjv1611]

Hmm, I've not thought of creating a view. And actually, I'm not sure whether my access will permit the creation of views. That would be interesting to look into.

However for this particular assignment, I'm really just dealing with static data, so I'm not concerned so much with it being updated over time. At least, not yet.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

AlexCuse,

So you're suggesting I basically select the data from "mytable" along with any other tables I'm wanting to select from into a brand new table?

That's what I've done in the past, but this table is just so huge, I didn't want to make any more copies than I had to. The table has just over 7 million records (just a handful of rows, currently).

--

"If to err is human, then I must be some kind of human!" -Me

 

[AlexCuse]

Nah, I would just add the column, then put the data into it. But you wanted a way that would allow you to skip figuring out what the data types were, etc... ;-)

If you are worried about storage space, then the view is the way to go (or delete the other table once you've added your columns to the "keeper")

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[ESquared]

<off topic>
Whether spelled hairbrained or harebrained, it refers to a rabbit, not hair.
</off topic>

 

[kjv1611]

[off topic]
Just in case anyone else did not understand what I meant by "hair-brained:" I just basically meant "crazy" or "silly" or some similar term. If you've not heard the usage before, you have now.
[/off topic]

--

"If to err is human, then I must be some kind of human!" -Me

 

[harebrain]

<off topic>I object to this whole thread!</off topic>

 

[SQLDenis]

<off topic>I object to this whole thread!</off topic>

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ButterflyLover]

ESquared,

Leave the poor guy alone! This isn't a language forum.