Add field at position I want in sql server

Asked

Viewed 1,491 times

4

Every time I make one ALTER TABLE and add a column, this field goes to the end.

In design mode, using the Sql Management graphical tool, there is the Insert Column option at any position, but it turns out I can’t save.

I have permission from the bank administrator. How do I, if there is how, include in an existing table, including with data, put this new field in the position I want?

  • 2

    Is there an error to save? You know this will recreate the whole table and do other manipulations in the base?

  • What error does it give? There might be a Management Studio configuration that would be blocking this (the error would indicate which).

  • Actually it is not an error, just a message that it is not possible to make the change, only if I drop the table and etc. Now I don’t understand the downvote. The question is straightforward and only one answer, I would like to understand these guys here within the forum.

3 answers

4

  • 1

    Important to note that this is only possible in graphic mode and recreating giant tables is unviable. Finally this is just an aesthetic change, for SQL Server the layout of the columns does not change at all.

  • That doesn’t stop the job. It was just an aesthetic question. Recreate, it means lose everything and that I can’t do, so I’ll leave it as it really is.

  • I think you don’t lose the data, but the operation might be slow @pnet.

  • Do I not lose? I will do a test with a table at home only and see if I really do not lose the data. If that is the case, the matter is settled, and otherwise.

  • You can create any table just to test this....

2

Usually, the error appears when you try to change the structure of a table that is already being used and has some reference, be it from FK, Constraint, Index, etc...

Below, I list two options that can be adopted:

  • If there is not too much data in your table, you can export, drop the current table, create the new table, edit the exported values and insert with the values in the order with your new column together.

  • If you have many records in your table, I suggest you leave the column at the end of the table even, will not affect at all, just will not view so easily, when return a select straight in Management Studio.

-1

I know it’s been a long time, but I’ve found out how to do such a feat

-- excluded the previous table

drop table cta_receber_recebimentos_backup
go

-- here put the fields I wanted and in the order I wanted, and I backed up the table

SELECT ID, NR_TITULO, DT_PAGAMENTO, TIPO_DOC, VL_SALDO, VL_JUROS, VL_MULTA, VL_DESCONTO, VL_PAGO, VL_SALDO_ATUAL, CXA_CODIGO, USUARIO  
INTO CTA_RECEBER_RECEBIMENTOS_BACKUP 
FROM CTA_RECEBER_RECEBIMENTOS
go

-- I visualized if everything was right

select * from cta_receber_recebimentos_backup
go

-- here excludes the table, previous

drop table cta_receber_recebimentos 
go

-- a new backup, but from _backup to the original table

select * into CTA_RECEBER_RECEBIMENTOS 
from cta_receber_recebimentos_backup
go

Browser other questions tagged

You are not signed in. Login or sign up in order to post.