Bobski Bobski - 17 days ago 9
SQL Question

Selecting specific fields from a table with SELECT *

I have two tables - I want to be able to select data from tbl1 and insert it into tbl2. Now here's the deal - Tbl1 has roughly about 30 fields. There are three columns in that table that are not in tbl2. Instead of specifying each field individually, i was trying to see if there's any possible way from me to do something like this...

Insert into Tbl2 SELECT * (but three fields) from tbl1 where value='value'


or do something like

Insert Into tbl2 SELECT Field1, Field2


but because I'll be doing this for many different tables in each case tbl1 will have 3 extra fields then the corresponding tb2 - i'm trying to do it in the simples and cleanest way possible

Any suggestions?

Answer

This cannot be done, there is no syntax to insert or select 'all fields except'.

And if there were it would be a bad idea to use it, for exactly the same reason that select * in application code is a bad idea. (Assuming the table definition(s) will never change and that you always need all the data.)