How can I insert a new field between existing field?

Dear all,
Is there any way can I insert a new field between existing fields through TS
QL or other means, without using Enterprise Manager?
Thanks a lot.
Regards,
Alex AUHi,
With out dropping and recreating the table you can not insert a column in be
tween 2 existing columns.
Actually enterprise manager internally does below events while inserting a n
ew field...
1. Pull data out
2. Generate script of table and dependants
3. Drop and recreate the table with new structure
4. Load the data
So while you do a schema change on huge tables; it will result in longer ex
ecution time...
Thanks
Hari
SQL Server MVP
"Alex AU" <acawh@.msn.com> wrote in message news:5003A40C-1603-4C8F-B591-DFFF
69DFC567@.microsoft.com...
Dear all,
Is there any way can I insert a new field between existing fields through TS
QL or other means, without using Enterprise Manager?
Thanks a lot.
Regards,
Alex AU|||The actual field order 'should' not matter. If you add a new column and it i
s at the 'end of the list', you can always retrieve the list of columns as y
ou want -with the new column in the 'middle'. Of course, that gets in the wa
y of using 'SELECT *' -but you shouldn't be doing that anyway!
(There some considerations that SQL Server will take in regard to field/data
type placement in the row on the datapage but that should not interfere with
your getting the data in the fashion you desire.)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Alex AU" <acawh@.msn.com> wrote in message news:5003A40C-1603-4C8F-B591-DFFF
69DFC567@.microsoft.com...
Dear all,
Is there any way can I insert a new field between existing fields through TS
QL or other means, without using Enterprise Manager?
Thanks a lot.
Regards,
Alex AU|||Thanks Hari,
If I want to mimic what Enterprise Manager do, what will be the right way?
Can I do as follows:-
- Create a temp table with the new fields inserted
- insert the data from old table to temp table
- drop the old table
- Create the table with the new structure
- insert the data back from temp table
- drop the temp table
Can I rename the temp table to the original table name to replace the last 3
steps?
Thanks a lot.
Regards,
Alex AU
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:uweZCACvGHA.
2260@.TK2MSFTNGP03.phx.gbl...
Hi,
With out dropping and recreating the table you can not insert a column in be
tween 2 existing columns.
Actually enterprise manager internally does below events while inserting a n
ew field...
1. Pull data out
2. Generate script of table and dependants
3. Drop and recreate the table with new structure
4. Load the data
So while you do a schema change on huge tables; it will result in longer ex
ecution time...
Thanks
Hari
SQL Server MVP
"Alex AU" <acawh@.msn.com> wrote in message news:5003A40C-1603-4C8F-B591-DFFF
69DFC567@.microsoft.com...
Dear all,
Is there any way can I insert a new field between existing fields through TS
QL or other means, without using Enterprise Manager?
Thanks a lot.
Regards,
Alex AU|||Alex AU wrote:
> Dear all,
> Is there any way can I insert a new field between existing fields
> through TSQL or other means, without using Enterprise Manager?
> Thanks a lot.
>
> Regards,
> Alex AU
Field order is irrelevant. Given two tables:
TableA: RowID, Value, Description, UpdateDate
TableB: RowID, Value, UpdateDate, Description
The field order means nothing, because your queries should always
specify a field list:
SELECT Value, Description, UpdateDate
FROM TableA
WHERE RowID = 1
SELECT Value, Description, UpdateDate
FROM TableB
WHERE RowID = 1
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Unless you're really lucky, it's far more complicated that than. You'll have
to take account of all constraints - Defaults, FKs etc and all indexes. The
se need to be dropped from the existing table and later readded to the tmp t
able. Also keep an eye out for any views created using select * from tablena
me, as they'll need to be refreshed afterwards. Finally, I'd also question w
hether it's worth it, when there's such little gain. Client apps should acce
ss columns by name rather than position, so typically the main gain is just
neatness for the DBA.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Paul Ibison SQL Server MVP said:
> "Client apps should access columns by name rather than position, so
> typically the main gain is just neatness for the DBA."
...Unless you're trying to do something completely off-the-wall and "crazy"
like writing a custom application to Bulk Load data into your table in which
case you have to specify columns by ordinal position...|||Then why not create a VIEW with the fields in the necessary order, and bulk
load the VIEW?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mike C#" <xyz@.xyz.com> wrote in message
news:4X7Dg.6916$5L4.3133@.newsfe10.lga...
> Paul Ibison SQL Server MVP said:
> ...Unless you're trying to do something completely off-the-wall and
> "crazy" like writing a custom application to Bulk Load data into your
> table in which case you have to specify columns by ordinal position...
>|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OiQESADwGHA.4460@.TK2MSFTNGP05.phx.gbl...
> Then why not create a VIEW with the fields in the necessary order, and
> bulk load the VIEW?
As long as the columns in the view are in the correct ordinal positions...
Table or view, bulk operations API's reference columns by ordinal position.