Append / Insert Into Question

When appending data from one table to another...
If the structure is the same,
do you need to list all fields in the query
insert into t1 ( f1, f2, f3 etc..)
Select f1, f2, f3 etc from t2
Thanks in advance,
Bob.John 3:16 wrote:
> When appending data from one table to another...
> If the structure is the same,
> do you need to list all fields in the query
> insert into t1 ( f1, f2, f3 etc..)
> Select f1, f2, f3 etc from t2
> Thanks in advance,
> Bob.
Actually you don't have to but it's silly not to. It is valid syntax to
leave out the column list in an INSERT but if you do that SQL will
match the target columns by their relative positions, not by name. Your
code may be less reliable and certainly harder to maintain and support
if you don't reference columns by name. The wisest policy is to list
the column names every time.
You can save yourself some typing by dragging the column lists from the
Object Browser in Query Analyzer.
David Portas
SQL Server MVP
--|||Works for me:
insert t1 select * from t2
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:OQUh2Jq9FHA.1844@.TK2MSFTNGP11.phx.gbl...
> When appending data from one table to another...
> If the structure is the same,
> do you need to list all fields in the query
> insert into t1 ( f1, f2, f3 etc..)
> Select f1, f2, f3 etc from t2
> Thanks in advance,
> Bob.
>|||Nope - but it's good practice at any time.
For a shortcut to get started, in Query Analyzer's Object Browser, you
can right-click \ Script object to [desired target] As \ Insert.
John 3:16 wrote:
> When appending data from one table to another...
> If the structure is the same,
> do you need to list all fields in the query
> insert into t1 ( f1, f2, f3 etc..)
> Select f1, f2, f3 etc from t2
> Thanks in advance,
> Bob.
>|||...one other thing.
This is maybe a good idea for a one time thing.
I would not use this method in production code.
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:OQUh2Jq9FHA.1844@.TK2MSFTNGP11.phx.gbl...
> When appending data from one table to another...
> If the structure is the same,
> do you need to list all fields in the query
> insert into t1 ( f1, f2, f3 etc..)
> Select f1, f2, f3 etc from t2
> Thanks in advance,
> Bob.
>|||Thanks David.
..Like you said, the time savings doesn't justify
future potential problems resulting from source or target changes.
Thanks for the reply,
Bob.

> Actually you don't have to but it's silly not to. It is valid syntax to
> leave out the column list in an INSERT but if you do that SQL will
> match the target columns by their relative positions, not by name. Your
> code may be less reliable and certainly harder to maintain and support
> if you don't reference columns by name. The wisest policy is to list
> the column names every time.
> You can save yourself some typing by dragging the column lists from the
> Object Browser in Query Analyzer.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks Raymond
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23gnyFTq9FHA.472@.TK2MSFTNGP15.phx.gbl...
> ...one other thing.
> This is maybe a good idea for a one time thing.
> I would not use this method in production code.
> "John 3:16" <bobmcc@.tricoequipment.com> wrote in message
> news:OQUh2Jq9FHA.1844@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Trey.
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:eEBw2Sq9FHA.3132@.TK2MSFTNGP12.phx.gbl...
> Nope - but it's good practice at any time.
> For a shortcut to get started, in Query Analyzer's Object Browser, you can
> right-click \ Script object to [desired target] As \ Insert.
>
> John 3:16 wrote: