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?
4
As already said, this type of change requires that the table be recreated. You need to authorize Management Studio to do this:
Image origin: https://stackoverflow.com/a/12582648/825789
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 sql-server ssms
You are not signed in. Login or sign up in order to post.
Is there an error to save? You know this will recreate the whole table and do other manipulations in the base?
– Maniero
What error does it give? There might be a Management Studio configuration that would be blocking this (the error would indicate which).
– bfavaretto
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.
– pnet