Copying columns to same table in other database

Question

Copying columns to same table in other database

ulisseslourenco

SSC-Addicted

Points: 417
More actions
December 14, 2011 at 6:09 am

#251968

Hi people,
I have made a script to copy columns to same table in other database, however I have problems when I copy columns which have defaut value, it doesn´t copy and no happen errors.
Do someone help me how to copy columns with default value?
This is a code below:
select * from #teste
SELECT COLUMN_NAME,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE INTO #TESTE
FROM nectar.INFORMATION_SCHEMA.COLUMNS a
WHERE COLUMN_NAME NOT IN ( SELECT COLUMN_NAME
from NectarTeste.INFORMATION_SCHEMA.COLUMNS b
where b.TABLE_NAME ='TABLE')
AND a.TABLE_NAME ='TABLE'
DECLARE @COLUNA VARCHAR(150)
DECLARE @DEFAULT VARCHAR(100)
DECLARE @ISNULL VARCHAR(5)
DECLARE @TIPO VARCHAR(100)
DECLARE @TAMANHO INT
DECLARE @PRECISAO INT
DECLARE @ESCALA INT
WHILE (SELECT COUNT(*) FROM #TESTE) > 0
BEGIN
SET @COLUNA = (SELECT TOP 1 COLUMN_NAME FROM #TESTE)
SELECT @DEFAULT=COLUMN_DEFAULT ,@ISNULL= IS_NULLABLE,@TIPO=DATA_TYPE,@TAMANHO=CHARACTER_MAXIMUM_LENGTH,@PRECISAO=NUMERIC_PRECISION,@ESCALA=NUMERIC_SCALE
FROM #TESTE
WHERE COLUMN_NAME = @COLUNA
-- TIPO STRING
IF @TAMANHO IS NOT NULL AND @ISNULL ='YES' AND @DEFAULT IS NULL
BEGIN
EXEC ('ALTER TABLE NECTARTESTE..TB_CONTRATO ADD '+@COLUNA+' '+@TIPO+'('+@TAMANHO+')')
END
IF @TAMANHO IS NOT NULL AND @ISNULL ='YES' AND @DEFAULT IS NOT NULL
BEGIN
EXEC ('ALTER TABLE NECTARTESTE..TB_CONTRATO ADD '+@COLUNA+' '+@TIPO+'('+@TAMANHO+') DEFAULT '+@DEFAULT+'')
END
IF @TAMANHO IS NOT NULL AND @ISNULL ='NO' AND @DEFAULT IS NULL
BEGIN
EXEC ('ALTER TABLE NECTARTESTE..TB_CONTRATO ADD '+@COLUNA+' '+@TIPO+'('+@TAMANHO+') NOT NULL')
END
IF @TAMANHO IS NOT NULL AND @ISNULL ='NO' AND @DEFAULT IS NOT NULL
BEGIN
EXEC ('ALTER TABLE NECTARTESTE..TB_CONTRATO ADD '+@COLUNA+' '+@TIPO+'('+@TAMANHO+') NOT NULL DEFAULT '+@DEFAULT+'')
END
-- TIPO NUMERICO/INT
IF @TAMANHO IS NULL AND @ISNULL ='YES' AND @DEFAULT IS NOT NULL AND (@TIPO ='NUMERIC' OR @TIPO ='DECIMAL')
BEGIN
EXEC ('ALTER TABLE NECTARTESTE..TB_CONTRATO ADD '+@COLUNA+' '+@TIPO+'('+@PRECISAO+','+@ESCALA+') DEFAULT '+@DEFAULT+'')
END
IF @TAMANHO IS NULL AND @ISNULL ='YES' AND @DEFAULT IS NULL AND (@TIPO ='NUMERIC' OR @TIPO ='DECIMAL')
BEGIN
EXEC ('ALTER TABLE NECTARTESTE..TB_CONTRATO ADD '+@COLUNA+' '+@TIPO+'('+@PRECISAO+','+@ESCALA+')')
END
IF @TAMANHO IS NULL AND @ISNULL ='NO' AND @DEFAULT IS NULL AND (@TIPO ='NUMERIC' OR @TIPO ='DECIMAL')
BEGIN
EXEC ('ALTER TABLE NECTARTESTE..TB_CONTRATO ADD '+@COLUNA+' '+@TIPO+'('+@PRECISAO+','+@ESCALA+') NOT NULL')
END
IF @TAMANHO IS NULL AND @ISNULL ='NO' AND @DEFAULT IS NOT NULL AND (@TIPO ='NUMERIC' OR @TIPO ='DECIMAL')
BEGIN
EXEC ('ALTER TABLE NECTARTESTE..TB_CONTRATO ADD '+@COLUNA+' '+@TIPO+'('+@PRECISAO+','+@ESCALA+') NOT NULL DEFAULT '+@DEFAULT+'')
END
IF @TAMANHO IS NULL AND @ISNULL ='YES' AND @DEFAULT IS NULL AND (@TIPO <> 'NUMERIC' OR @TIPO <> 'DECIMAL')
BEGIN
EXEC ('ALTER TABLE NECTARTESTE..TB_CONTRATO ADD '+@COLUNA+' '+@TIPO+'')
END
IF @TAMANHO IS NULL AND @ISNULL ='YES' AND @DEFAULT IS NULL AND (@TIPO <>'NUMERIC' OR @TIPO <>'DECIMAL')
BEGIN
EXEC ('ALTER TABLE NECTARTESTE..TB_CONTRATO ADD '+@COLUNA+' '+@TIPO+'')
END
IF @TAMANHO IS NULL AND @ISNULL ='NO' AND @DEFAULT IS NULL AND (@TIPO <>'NUMERIC' OR @TIPO <>'DECIMAL')
BEGIN
EXEC ('ALTER TABLE NECTARTESTE..TB_CONTRATO ADD '+@COLUNA+' '+@TIPO+' NOT NULL')
END
IF @TAMANHO IS NULL AND @ISNULL ='NO' AND @DEFAULT IS NOT NULL AND (@TIPO <> 'NUMERIC' OR @TIPO <>'DECIMAL')
BEGIN
EXEC ('ALTER TABLE NECTARTESTE..TB_CONTRATO ADD '+@COLUNA+' '+@TIPO+' NOT NULL ')
EXEC ('ALTER TABLE NECTARTESTE..TB_CONTRATO ADD CONSTRAINT DEFAULT' +@DEFAULT+' FOR'+@COLUNA+'')
END

Best regards

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Try building your ALTER_TABLE statement as a @sql variable. Then you can PRINT @sql as well as EXEC (@sql). If any of the statements fail to add the column, please post back and tell us which ones.

John

ulisseslourenco SSC-Addicted Points: 417 More actions · Answer 2

John Mitchell-245523 (12/14/2011)
Try building your ALTER_TABLE statement as a @sql variable. Then you can PRINT @sql as well as EXEC (@sql). If any of the statements fail to add the column, please post back and tell us which ones.
John

Hello

I got copy the columns using @sql variable, however when i try moving varchar(max) or text, it didn't copy. Show me errors, althought other type of data( int,numeric,bit,varchar(8000),char) the script works fine.