|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Append / Insert Into QuestionWhen 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... Actually you don't have to but it's silly not to. It is valid syntax to> 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. 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 David.
...Like you said, the time savings doesn't justify future potential problems resulting from source or target changes. Thanks for the reply, Bob. Show quote > 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 Show quote "John 3:16" <bob***@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. > ....one other thing.
This is maybe a good idea for a one time thing. I would not use this method in production code. Show quote "John 3:16" <bob***@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 Raymond
Show quote "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" <bob***@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: Show quote > 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 Trey.
Show quote "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: >> 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.  |
|||||||||||||||||||||||
Other interesting topics