Changing a table

Asked

Viewed 252 times

0

There is the possibility to change a table field without I lose the data that was previously entered?
I have a table that has a field varchar and I need to change it to int. It already has entered data of type varchar.

sql-server
  • 1

    If all data can be converted to int, I see no problem. Now if you have non-numeric characters, it will not be possible.

  • 1

    Search the SQL help for "ALTER COLUMN"

  • As @Pagotti commented, you can use the command alter table, but if some data cannot be converted it will generate an error (size, or some non-numerical character). The best way to do this would be to create a temporary table with the same structure, but with the field already in type int and try to enter the data there first to see if everything is ok.

  • @Flaviorodrigues: The column to be modified is foreign key from another table?

1 answer

1

There are a few approaches to what you request. I suggest backing up the table (or database) before starting the procedures.

The procedure that follows is an outline and has not been tested. I suggest you first test the development database.

nomebanco -> nome do banco de dados
tbDados -> nome da tabela
nomecoluna -> nome da coluna a alterar o tipo de dados
chaveprimaria -> coluna(s) que identifica, de forma única, cada linha da tabela

(1) The first step is to check whether the entire contents of the column can be converted from string for numerical. 

-- código #1
SELECT chaveprimária. nomecoluna
  from tbDados
  where isNumeric(nomecoluna) <> 1;
go

If the above query returns any lines, analyze the information and correct it. Only perform the next steps after you are sure that the column to be modified has numerical value (or no information - NULL) on all rows.

(2) Put the database in restricted mode.

-- código #2
ALTER DATABASE nomebanco RESTRICTED_USER;
go

(3) Create a temporary table, containing only the primary key of the table and the column to be changed, already in numerical format.

-- código #3 v3
SELECT chaveprimaria, convert(int, nomecoluna) as nomecoluna
  into tbDados2
  from tbDados;
go

(4) Change the type of data in the column. Add any restrictions you deem necessary (for example, NOT NULL).

-- código #5
ALTER TABLE tbDados ALTER COLUMN nomecoluna int;
go

(5) Enter the information in the column, in the numeric format.

-- código #6
UPDATE T1
  set nomecoluna= T2.nomecoluna
  from tbDados as T1
       inner join tbDados2 as T2 on T1.chaveprimaria = T2.chaveprimaria;
go

(6) Make sure the conversion is correct. Make sure everything is correct, release the database and delete the temporary table.

-- código #7
DROP TABLE tbDados2;
ALTER DATABASE nomebanco MULTI_USER;
go

Reading suggestion: Modify Columns

Browser other questions tagged sql-server

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