[dbadmin] Agregar una columan en una determinada posicion

[Diego Campos]

En resumén... como dijo Maxi.. Si la tabla es grande o compleja no conviene hacer cosas extrañas. Saludos, Diego. Maxi Accotto wrote: Exacto, mira el resultado de hacer eso de una tablita de mi sistema :)   BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION ALTER TABLE dbo.ALLOCATION  DROP CONSTRAINT FKEY9484 GO COMMIT BEGIN TRANSACTION ALTER TABLE dbo.ALLOCATION  DROP CONSTRAINT FKEY0016 GO COMMIT BEGIN TRANSACTION ALTER TABLE dbo.ALLOCATION  DROP CONSTRAINT DF__ALLOCATIO__TRIGG__1A2ABD85 GO ALTER TABLE dbo.ALLOCATION  DROP CONSTRAINT DF__ALLOCATIO__POST___1B1EE1BE GO ALTER TABLE dbo.ALLOCATION  DROP CONSTRAINT DF__ALLOCATIO__POST___1C1305F7 GO ALTER TABLE dbo.ALLOCATION  DROP CONSTRAINT DF__ALLOCATIO__CREAT__1D072A30 GO CREATE TABLE dbo.Tmp_ALLOCATION  (  ROWID int NOT NULL IDENTITY (1, 1),  ID varchar(5) NOT NULL,  DESCRIPTION varchar(80) NULL,  GROUP_ID varchar(15) NULL,  FROM_GL_ACCT_ID varchar(30) NOT NULL,  FROM_ENTITY_ID varchar(5) NOT NULL,  TRIGGER_FREQ char(1) NOT NULL,  nn char(10) NULL,  TRIGGER_DAY smallint NULL,  VARIABLE_NAME_1 varchar(30) NULL,  VARIABLE_NAME_2 varchar(30) NULL,  VARIABLE_NAME_3 varchar(30) NULL,  VARIABLE_NAME_4 varchar(30) NULL,  VARIABLE_NAME_5 varchar(30) NULL,  VARIABLE_NAME_6 varchar(30) NULL,  VARIABLE_NAME_7 varchar(30) NULL,  VARIABLE_NAME_8 varchar(30) NULL,  VARIABLE_NAME_9 varchar(30) NULL,  VARIABLE_NAME_10 varchar(30) NULL,  VARIABLE_VALUE_1 decimal(13, 3) NULL,  VARIABLE_VALUE_2 decimal(13, 3) NULL,  VARIABLE_VALUE_3 decimal(13, 3) NULL,  VARIABLE_VALUE_4 decimal(13, 3) NULL,  VARIABLE_VALUE_5 decimal(13, 3) NULL,  VARIABLE_VALUE_6 decimal(13, 3) NULL,  VARIABLE_VALUE_7 decimal(13, 3) NULL,  VARIABLE_VALUE_8 decimal(13, 3) NULL,  VARIABLE_VALUE_9 decimal(13, 3) NULL,  VARIABLE_VALUE_10 decimal(13, 3) NULL,  TRIGGER_COUNT smallint NULL,  TRIGGER_MAX smallint NULL,  TRIGGER_LAST_DATE datetime NULL,  CURRENCY_ID varchar(15) NULL,  POST_ALL_TRACKING char(1) NOT NULL,  POST_AS_NATIVE char(1) NOT NULL,  CREATE_DATE datetime NULL,  LAST_EXECUTED_DATE datetime NULL  )  ON [PRIMARY] GO ALTER TABLE dbo.Tmp_ALLOCATION ADD CONSTRAINT  DF__ALLOCATIO__TRIGG__1A2ABD85 DEFAULT ('N') FOR TRIGGER_FREQ GO ALTER TABLE dbo.Tmp_ALLOCATION ADD CONSTRAINT  DF__ALLOCATIO__POST___1B1EE1BE DEFAULT ('Y') FOR POST_ALL_TRACKING GO ALTER TABLE dbo.Tmp_ALLOCATION ADD CONSTRAINT  DF__ALLOCATIO__POST___1C1305F7 DEFAULT ('Y') FOR POST_AS_NATIVE GO ALTER TABLE dbo.Tmp_ALLOCATION ADD CONSTRAINT  DF__ALLOCATIO__CREAT__1D072A30 DEFAULT (getdate()) FOR CREATE_DATE GO SET IDENTITY_INSERT dbo.Tmp_ALLOCATION ON GO IF EXISTS(SELECT * FROM dbo.ALLOCATION)   EXEC('INSERT INTO dbo.Tmp_ALLOCATION (ROWID, ID, DESCRIPTION, GROUP_ID, FROM_GL_ACCT_ID, FROM_ENTITY_ID, TRIGGER_FREQ, TRIGGER_DAY, VARIABLE_NAME_1, VARIABLE_NAME_2, VARIABLE_NAME_3, VARIABLE_NAME_4, VARIABLE_NAME_5, VARIABLE_NAME_6, VARIABLE_NAME_7, VARIABLE_NAME_8, VARIABLE_NAME_9, VARIABLE_NAME_10, VARIABLE_VALUE_1, VARIABLE_VALUE_2, VARIABLE_VALUE_3, VARIABLE_VALUE_4, VARIABLE_VALUE_5, VARIABLE_VALUE_6, VARIABLE_VALUE_7, VARIABLE_VALUE_8, VARIABLE_VALUE_9, VARIABLE_VALUE_10, TRIGGER_COUNT, TRIGGER_MAX, TRIGGER_LAST_DATE, CURRENCY_ID, POST_ALL_TRACKING, POST_AS_NATIVE, CREATE_DATE, LAST_EXECUTED_DATE)   SELECT ROWID, ID, DESCRIPTION, GROUP_ID, FROM_GL_ACCT_ID, FROM_ENTITY_ID, TRIGGER_FREQ, TRIGGER_DAY, VARIABLE_NAME_1, VARIABLE_NAME_2, VARIABLE_NAME_3, VARIABLE_NAME_4, VARIABLE_NAME_5, VARIABLE_NAME_6, VARIABLE_NAME_7, VARIABLE_NAME_8, VARIABLE_NAME_9, VARIABLE_NAME_10, VARIABLE_VALUE_1, VARIABLE_VALUE_2, VARIABLE_VALUE_3, VARIABLE_VALUE_4, VARIABLE_VALUE_5, VARIABLE_VALUE_6, VARIABLE_VALUE_7, VARIABLE_VALUE_8, VARIABLE_VALUE_9, VARIABLE_VALUE_10, TRIGGER_COUNT, TRIGGER_MAX, TRIGGER_LAST_DATE, CURRENCY_ID, POST_ALL_TRACKING, POST_AS_NATIVE, CREATE_DATE, LAST_EXECUTED_DATE FROM dbo.ALLOCATION TABLOCKX') GO SET IDENTITY_INSERT dbo.Tmp_ALLOCATION OFF GO ALTER TABLE dbo.ALLOCATION_DETAIL  DROP CONSTRAINT FKEY0217 GO DROP TABLE dbo.ALLOCATION GO EXECUTE sp_rename N'dbo.Tmp_ALLOCATION ', N'ALLOCATION', 'OBJECT' GO ALTER TABLE dbo.ALLOCATION ADD CONSTRAINT  PK_ALLOCATION PRIMARY KEY CLUSTERED  (  ID  ) WITH FILLFACTOR = 90 ON [PRIMARY] GO ALTER TABLE dbo.ALLOCATION WITH NOCHECK ADD CONSTRAINT  CHK0064 CHECK ((([TRIGGER_FREQ] = 'M' or [TRIGGER_FREQ] = 'W' or [TRIGGER_FREQ] = 'N') and ([POST_ALL_TRACKING] = 'Y' or [POST_ALL_TRACKING] = 'N') and ([POST_AS_NATIVE] = 'Y' or [POST_AS_NATIVE] = 'N'))) GO ALTER TABLE dbo.ALLOCATION WITH NOCHECK ADD CONSTRAINT  FKEY0016 FOREIGN KEY  (  FROM_ENTITY_ID  ) REFERENCES dbo.ENTITY  (  ID  ) GO ALTER TABLE dbo.ALLOCATION WITH NOCHECK ADD CONSTRAINT  FKEY9484 FOREIGN KEY  (  CURRENCY_ID  ) REFERENCES dbo.CURRENCY  (  ID  ) GO CREATE TRIGGER UPDATE_ALLOCATION on dbo.ALLOCATION for UPDATE as SET NOCOUNT ON DECLARE @nRcd    INT DECLARE @O_ID    VARCHAR(15) DECLARE @N_ID    VARCHAR(15) DECLARE @TRIGGER_FREQ   CHAR DECLARE @FROM_GL_ACCT_ID  VARCHAR(30) DECLARE @FROM_ENTITY_ID  VARCHAR(15) SELECT @nRcd = 0 IF @nRcd = 0 BEGIN DECLARE ALLOCATION_UPD CURSOR LOCAL FOR select i.trigger_freq, i.id, d.id from inserted i, deleted d where i.rowid = d.rowid OPEN ALLOCATION_UPD FETCH ALLOCATION_UPD INTO @TRIGGER_FREQ, @N_ID, @O_ID WHILE (@nRcd = 0 and @@fetch_status <> -1) BEGIN   update ALLOCATION_DETAIL set ALLOC_ID = @N_ID where ALLOC_ID = @O_ID   FETCH ALLOCATION_UPD INTO @TRIGGER_FREQ, @N_ID, @O_ID END DEALLOCATE ALLOCATION_UPD END IF (@nRcd <> 0) RAISERROR('VMFG-%d error in trigger UPDATE_ALLOCATION', 16, -1, @nRcd) IF (@nRcd <> 0 Or @@ERROR <> 0) ROLLBACK TRANSACTION GO GRANT SELECT ON dbo.ALLOCATION TO SYSADM  AS dbo GRANT SELECT ON dbo.ALLOCATION TO RIOS  AS dbo GRANT SELECT ON dbo.ALLOCATION TO LORENA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO JOSELUIS  AS dbo GRANT SELECT ON dbo.ALLOCATION TO BORDON  AS dbo GRANT SELECT ON dbo.ALLOCATION TO VICENTE  AS dbo GRANT SELECT ON dbo.ALLOCATION TO BRIZUELA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO TAPIA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO MARTIN  AS dbo GRANT SELECT ON dbo.ALLOCATION TO CASAO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO CLAUDIA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO TOLOZA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO PABLO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO MANFREDI  AS dbo GRANT SELECT ON dbo.ALLOCATION TO VAZQUEZ  AS dbo GRANT SELECT ON dbo.ALLOCATION TO RODOLFO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO INGPROC  AS dbo GRANT SELECT ON dbo.ALLOCATION TO FERNANDO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO DECAROLI  AS dbo GRANT SELECT ON dbo.ALLOCATION TO EUGENIO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO WEISS  AS dbo GRANT SELECT ON dbo.ALLOCATION TO SILVINA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO DANIELA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO CARLOS  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ESTECHE  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ANA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ADRIANA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO FAURE  AS dbo GRANT SELECT ON dbo.ALLOCATION TO MECANICA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ROSACE  AS dbo GRANT SELECT ON dbo.ALLOCATION TO PACILIO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ENZO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO JAVIER  AS dbo GRANT SELECT ON dbo.ALLOCATION TO CORONEL  AS dbo GRANT SELECT ON dbo.ALLOCATION TO NICOLAS  AS dbo GRANT SELECT ON dbo.ALLOCATION TO [NO]  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ROJAS  AS dbo GRANT SELECT ON dbo.ALLOCATION TO DICANDIA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ALBERTO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO MANTE_PA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO GOMES  AS dbo GRANT SELECT ON dbo.ALLOCATION TO CALUSIC  AS dbo GRANT SELECT ON dbo.ALLOCATION TO REBABA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ARAGUNDE  AS dbo GRANT SELECT ON dbo.ALLOCATION TO HORNO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO GABRIELN  AS dbo GRANT SELECT ON dbo.ALLOCATION TO RICARDO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO JOSE  AS dbo GRANT SELECT ON dbo.ALLOCATION TO COIRA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO MOLDEO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO HECTORF  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ANTONIO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ACOSTA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO SADRIANA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO MATTIOLI  AS dbo GRANT SELECT ON dbo.ALLOCATION TO DALLORSO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO CALIDAD  AS dbo GRANT SELECT ON dbo.ALLOCATION TO MAURO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO POVIS  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ALMACEN  AS dbo GRANT SELECT ON dbo.ALLOCATION TO CARLOSM  AS dbo GRANT SELECT ON dbo.ALLOCATION TO LUCIANA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO MIGRA641  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ANDRES  AS dbo GRANT SELECT ON dbo.ALLOCATION TO VANESA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO BOTTI  AS dbo GRANT SELECT ON dbo.ALLOCATION TO INVDES  AS dbo GRANT SELECT ON dbo.ALLOCATION TO MAXI  AS dbo GRANT SELECT ON dbo.ALLOCATION TO NOEMI  AS dbo GRANT SELECT ON dbo.ALLOCATION TO JORGES  AS dbo GRANT SELECT ON dbo.ALLOCATION TO VILANOVA  AS dbo GRANT REFERENCES ON dbo.ALLOCATION TO FSC_VM  AS dbo GRANT SELECT ON dbo.ALLOCATION TO FSC_VM  AS dbo GRANT UPDATE ON dbo.ALLOCATION TO FSC_VM  AS dbo GRANT INSERT ON dbo.ALLOCATION TO FSC_VM  AS dbo GRANT DELETE ON dbo.ALLOCATION TO FSC_VM  AS dbo GRANT SELECT ON dbo.ALLOCATION TO DELOR  AS dbo GRANT SELECT ON dbo.ALLOCATION TO HSMA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO BENGOCHE  AS dbo GRANT SELECT ON dbo.ALLOCATION TO INVENT  AS dbo GRANT SELECT ON dbo.ALLOCATION TO CINTIA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO MARINA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO CARO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO RINAUDO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO FRANCISC  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ZVIOVICH  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ESPOSITO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO CAMPOS  AS dbo GRANT SELECT ON dbo.ALLOCATION TO MICHEL  AS dbo GRANT SELECT ON dbo.ALLOCATION TO MEDICI  AS dbo GRANT SELECT ON dbo.ALLOCATION TO TAUS  AS dbo GRANT SELECT ON dbo.ALLOCATION TO EDGARDO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO BLUM  AS dbo GRANT SELECT ON dbo.ALLOCATION TO DLORENZO  AS dbo GRANT SELECT ON dbo.ALLOCATION TO SSANCHEZ  AS dbo GRANT SELECT ON dbo.ALLOCATION TO DIIORIOM  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ADRI  AS dbo GRANT SELECT ON dbo.ALLOCATION TO PUENTE  AS dbo GRANT SELECT ON dbo.ALLOCATION TO MIRACOLA  AS dbo GRANT SELECT ON dbo.ALLOCATION TO CALING  AS dbo GRANT SELECT ON dbo.ALLOCATION TO BENITEZD  AS dbo GRANT SELECT ON dbo.ALLOCATION TO VANDERH  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ACORONEL  AS dbo GRANT SELECT ON dbo.ALLOCATION TO FUSION  AS dbo GRANT SELECT ON dbo.ALLOCATION TO NCADMIN  AS dbo GRANT SELECT ON dbo.ALLOCATION TO USER1  AS dbo GRANT SELECT ON dbo.ALLOCATION TO ESCALHAO  AS dbo COMMIT BEGIN TRANSACTION ALTER TABLE dbo.ALLOCATION_DETAIL WITH NOCHECK ADD CONSTRAINT  FKEY0217 FOREIGN KEY  (  ALLOC_ID  ) REFERENCES dbo.ALLOCATION  (  ID  ) ON DELETE CASCADE   GO COMMIT   El día 12/10/06, Diego Campos <[EMAIL PROTECTED]> escribió: Maxi, Gracias por la advertencia.. En la edicion de la tabla, podes ver el codigo SQL que el EM generó para realizar la modificación que uno solicito. Con el código en la mano uno puede evaluar el costo de lo que se le esta pidiendo. Esto lo podes hacer siempre y cuando la tabla sea chica (como vos decis)....ya que si la tabla es grande, directamente no conviene hacer nada extraño y agregar la nueva columna al final como corresponde. Me falto hacer esta aclaración/comentario en mi mail anterior... Saludos, Diego.- Maxi Accotto wrote: Diego, ojo con esto, si la tabla es muy grande puedes tener serios problemas en hacer esta operatoria porque el EM no hace magia sino que hace lo que yo indique en los pasos anteriores y esto va a demorar segun el tamaño de la tabla, puede ser tan malo que hasta podria hacer colgar al servidor. El día 12/10/06, Diego Campos < [EMAIL PROTECTED]> escribió: En el Enterprise Manager, lo podes hacer.. posicionate sobre la columna que quedaria a continuación de la nueva columna. boton derecho del mouse y Add Column Del resto se encarga el SQL Server. Diego.- Maxi Accotto wrote: Hola, a ver, primero te diria que las posiciones de las columnas no deberian importante en lo absoluto y que si estas preocupado por eso puede ser por algun acostumbramiento o bien por tener algun desarrollo inadecuado que este buscando el orden,   De tsql directo no lo podes hacer, deberias para hacerlo aplicar lo mismo que hace el Managemenbt Studio   1) Crear una tabla temporal y pasar los datos ahi 2) Guardar todo el tema de indices y relaciones 3) Borrar la tabla 4) crarla con los campos nuevos 5) Pasarle la data y los indices   Esto es lo que haces si lo intentas ejecutar desde el Management studio o el viejo Entherprise manager.   Para que queres poner las columnas en un orden especifico?   El día 12/10/06, Ricci Mauro < [EMAIL PROTECTED] > escribió: Hola gente no se si es el lugar adecuado para hacer una pregunta de este tipo pero necesito insertar una columna en una tabla en una determinada posición se puede?   ALTER TABLE [TABLA] ADD COLUMNA_10 BIT NULL -- This message has been scanned for viruses and dangerous content by MailScanner , and is believed to be clean. -- ---------------------------------------------------- Microsoft MVP (Most Valuable Professional) SQL Server Orador Culminis (www.sqlgurus.org) ------------------------------------------- -- Diego Campos SISTEMATICA ingenieria de software srl Pasco 1675 (2000) Rosario, Santa Fe, Argentina. Tel./Fax: +54 (341) 485-1432 / 568-0183 -- ---------------------------------------------------- Microsoft MVP (Most Valuable Professional) SQL Server Orador Culminis ( www.sqlgurus.org) ------------------------------------------- -- Diego Campos SISTEMATICA ingenieria de software srl Pasco 1675 (2000) Rosario, Santa Fe, Argentina. Tel./Fax: +54 (341) 485-1432 / 568-0183 -- ---------------------------------------------------- Microsoft MVP (Most Valuable Professional) SQL Server Orador Culminis (www.sqlgurus.org) ------------------------------------------- -- Diego Campos SISTEMATICA ingenieria de software srl Pasco 1675 (2000) Rosario, Santa Fe, Argentina. Tel./Fax: +54 (341) 485-1432 / 568-0183