Comment ajouter une colonne en TSQL après une colonne spécifique ?

Question

J'ai une table :

MyTable
    ID
    FieldA
    FieldB

Je veux modifier le tableau et ajouter une colonne pour qu'il ressemble à ceci :

MyTable
    ID
    NewField
    FieldA
    FieldB

Dans MySQL, j'aurais donc un :

ALTER TABLE MyTable ADD COLUMN NewField int NULL AFTER ID;

Une seule ligne, agréable, simple, qui fonctionne très bien. Comment faire dans le monde de Microsoft ?

Answer 1

Dans Microsoft SQL Server Management Studio (l'outil d'administration de MSSQL), il suffit d'aller dans "design" sur une table et de faire glisser la colonne vers la nouvelle position. Ce n'est pas une ligne de commande mais vous pouvez le faire.

Answer 2

Même si la question est ancienne, une réponse plus précise sur Management Studio serait nécessaire.

Vous pouvez créer la colonne manuellement ou avec Management Studio. Mais Management Studio nécessitera de recréer la table et entraînera un dépassement de délai si vous avez déjà trop de données dans celle-ci, à éviter sauf si la table est légère.

Pour modifier l'ordre des colonnes, il suffit de les déplacer dans Management Studio. Cela ne devrait pas nécessiter (il existe très probablement des exceptions) que Management Studio recrée la table, car cela modifie très probablement l'ordre des colonnes dans les définitions de la table.

J'ai procédé de cette manière à de nombreuses reprises avec des tables auxquelles je ne pouvais pas ajouter de colonnes avec l'interface graphique en raison des données qu'elles contenaient. J'ai ensuite déplacé les colonnes avec l'interface graphique de Management Studio et les ai simplement enregistrées.

Vous passerez d'un temps d'arrêt assuré à quelques secondes d'attente.

Answer 3

C'est tout à fait possible. Mais vous ne devriez pas le faire si vous ne savez pas à quoi vous avez affaire. Il m'a fallu environ 2 jours pour le découvrir. Voici une procédure stockée où j'entre : --nom de la base de données (le nom du schéma est "_" pour plus de lisibilité) ---nom de la table ---colonne ---type de données de la colonne (la colonne ajoutée est toujours nulle, sinon vous ne pourrez pas insérer) ---la position de la nouvelle colonne.

Comme je travaille avec des tables de SAM toolkit (et certaines d'entre elles ont plus de 80 colonnes), la variable typique ne pourra pas contenir la requête. Cela oblige à utiliser un fichier externe. Maintenant, faites attention à l'endroit où vous stockez ce fichier et à qui y a accès au niveau NTFS et réseau.

A la vôtre !

USE [master]
GO
/****** Object:  StoredProcedure [SP_Set].[TrasferDataAtColumnLevel]    Script Date: 8/27/2014 2:59:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [SP_Set].[TrasferDataAtColumnLevel]
(
    @database varchar(100),
    @table varchar(100),
    @column varchar(100),
    @position int,
    @datatype varchar(20)    
)
AS
BEGIN
set nocount on
exec  ('
declare  @oldC varchar(200), @oldCDataType varchar(200), @oldCLen int,@oldCPos int
create table Test ( dummy int)
declare @columns varchar(max) = ''''
declare @columnVars varchar(max) = ''''
declare @columnsDecl varchar(max) = ''''
declare @printVars varchar(max) = ''''

DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR 
select column_name, data_type, character_maximum_length, ORDINAL_POSITION  from ' + @database + '.INFORMATION_SCHEMA.COLUMNS where table_name = ''' + @table + '''
OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO @oldC, @oldCDataType, @oldCLen, @oldCPos WHILE @@FETCH_STATUS = 0 BEGIN

if(@oldCPos = ' + @position + ')
begin
    exec(''alter table Test add [' + @column + '] ' + @datatype + ' null'')
end

if(@oldCDataType != ''timestamp'')
begin

    set @columns += @oldC + '' , '' 
    set @columnVars += ''@'' + @oldC + '' , ''

    if(@oldCLen is null)
    begin
        if(@oldCDataType != ''uniqueidentifier'')
        begin
            set @printVars += '' print convert('' + @oldCDataType + '',@'' + @oldC + '')'' 
            set @columnsDecl += ''@'' + @oldC + '' '' + @oldCDataType + '', '' 
            exec(''alter table Test add ['' + @oldC + ''] '' + @oldCDataType + '' null'')
        end
        else
        begin
            set @printVars += '' print convert(varchar(50),@'' + @oldC + '')'' 
            set @columnsDecl += ''@'' + @oldC + '' '' + @oldCDataType + '', '' 
            exec(''alter table Test add ['' + @oldC + ''] '' + @oldCDataType + '' null'')
        end
    end
    else
    begin 
        if(@oldCLen < 0)
        begin
            set @oldCLen = 4000
        end
        set @printVars += '' print @'' + @oldC 
        set @columnsDecl += ''@'' + @oldC + '' '' + @oldCDataType + ''('' + convert(character,@oldCLen) + '') , '' 
        exec(''alter table Test add ['' + @oldC + ''] '' + @oldCDataType + ''('' + @oldCLen + '') null'')
    end
end

if exists (select column_name from INFORMATION_SCHEMA.COLUMNS where table_name = ''Test'' and column_name = ''dummy'')
begin
    alter table Test drop column dummy
end

FETCH NEXT FROM MY_CURSOR INTO  @oldC, @oldCDataType, @oldCLen, @oldCPos END CLOSE MY_CURSOR DEALLOCATE MY_CURSOR

set @columns = reverse(substring(reverse(@columns), charindex('','',reverse(@columns)) +1, len(@columns)))
set @columnVars = reverse(substring(reverse(@columnVars), charindex('','',reverse(@columnVars)) +1, len(@columnVars)))
set @columnsDecl = reverse(substring(reverse(@columnsDecl), charindex('','',reverse(@columnsDecl)) +1, len(@columnsDecl)))
set @columns = replace(replace(REPLACE(@columns, ''       '', ''''), char(9) + char(9),'' ''), char(9), '''')
set @columnVars = replace(replace(REPLACE(@columnVars, ''       '', ''''), char(9) + char(9),'' ''), char(9), '''')
set @columnsDecl = replace(replace(REPLACE(@columnsDecl, ''  '', ''''), char(9) + char(9),'' ''),char(9), '''')
set @printVars = REVERSE(substring(reverse(@printVars), charindex(''+'',reverse(@printVars))+1, len(@printVars))) 

create table query (id int identity(1,1), string varchar(max))

insert into query values  (''declare '' + @columnsDecl + ''
DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR '')

insert into query values   (''select '' + @columns + '' from ' + @database + '._.' + @table + ''')

insert into query values  (''OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO '' + @columnVars + '' WHILE @@FETCH_STATUS = 0 BEGIN '')

insert into query values   (@printVars )

insert into query values   ( '' insert into Test ('')
insert into query values   (@columns) 
insert into query values   ( '') values ( '' + @columnVars + '')'')

insert into query values  (''FETCH NEXT FROM MY_CURSOR INTO  '' + @columnVars + '' END CLOSE MY_CURSOR DEALLOCATE MY_CURSOR'')

declare @path varchar(100) = ''C:\query.sql''
declare @query varchar(500) = ''bcp "select string from query order by id" queryout '' + @path + '' -t, -c -S  '' + @@servername +  '' -T''

exec master..xp_cmdshell @query

set @query  = ''sqlcmd -S '' + @@servername + '' -i '' + @path

EXEC xp_cmdshell  @query

set @query = ''del ''  + @path

exec xp_cmdshell @query

drop table ' + @database + '._.' + @table + '

select * into ' + @database + '._.' + @table + ' from Test 

drop table query
drop table Test  ')

FIN

Answer 4

Si vous utilisez l'interface graphique pour ce faire, vous devez désélectionner l'option suivante permettant de déposer la table,

Answer 5

1. Create New Add new Column Table script ex : [DBName].[dbo].[TableName]_NEW
2. Copier les données de l'ancienne table vers la nouvelle : INSERT INTO newTable ( col1,col2,...) SELECT col1,col2,... FROM oldTable
3. Les enregistrements de contrôle, anciens et nouveaux, sont les mêmes :
4. DROP ancienne table
5. renomme newtable en oldtable

6. réexécutez votre sp et ajoutez la nouvelle valeur de la colonne.

   -- 1. Create New Add new Column Table Script CREATE TABLE newTable ( [new_column] [int] NOT NULL, <-- new column has been inserted here! [idx] [bigint] NOT NULL, [name] nvarchar NOT NULL, [active] [bit] NOT NULL ) -- 2. COPY old table data to new table: INSERT INTO newTable ([new_column],[idx],[name],[active]) SELECT [new_column],[idx],[name],[active] FROM oldTable -- 3. Check records old and new are the same: select sum(cnt) FROM ( SELECT 'table_1' AS table_name, COUNT() cnt FROM newTable UNION SELECT 'table_2' AS table_name, -COUNT() cnt FROM oldTable ) AS cnt_sum -- 4. DROP old table DROP TABLE oldTable -- 5. rename newtable to oldtable USE [DB_NAME] EXEC sp_rename newTable, oldTable