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

Hi,

With out dropping and recreating the table you can not insert a column in between 2 existing columns.
Actually enterprise manager internally does below events while inserting a new 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 execution time...

Thanks
Hari
SQL Server MVP

  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

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
  With out dropping and recreating the table you can not insert a column in between 2 existing columns.
  Actually enterprise manager internally does below events while inserting a new 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 execution time...

  Thanks
  Hari
  SQL Server MVP

    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

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. These need to be dropped from the existing table and later readded to the tmp table. Also keep an eye out for any views created using select * from tablename, as they'll need to be refreshed afterwards. Finally, I'd also question whether it's worth it, when there's such little gain. Client apps should access 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:
....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?

Show quoteHide quote

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.

The actual field order 'should' not matter. If you add a new column and it is at the 'end of the list', you can always retrieve the list of columns as you want -with the new column in the 'middle'. Of course, that gets in the way of using 'SELECT *' -but you shouldn't be doing that anyway!

(There some considerations that SQL Server will take in regard to field/datatype placement in the row on the datapage but that should not interfere with your getting the data in the fashion you desire.)

  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

Alex AU wrote:
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

TOP 50 invoices by company
Denormalizing a database table
2005, Ambiguous column name, and ORDER BY
Fractional truncation of Input Paramater in a UDF
Deleting Different Records at different Parts of the day