I have a stored procedure that creates a table, then copies that tables schema into another table. What I want to do is add 1 to the size of each of the columns in the new table. (This is for data paralleling purposes, to make sure that no data was truncated.) Here is what I have so far: --Declare the basename of the table (do not include rpt or qa, this will get filled in below based on which table it is creating)Declare @Table nvarchar(255)select @Table = 'mmmReport'--Create the Report Table----First Drop an existing table with that name.if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'rpt' + @Table)begin exec('DROP TABLE [dbo].[rpt' + @Table + ']') PRINT 'Table rpt' + @Table +' dropped'endelsebegin print 'No table exists with the name rpt' + @Tableend----second entire report Schema for a new table is below. Remove what is not needed and update datatypes and sizes for what is remaining. Declare @CreateRPT nvarchar(max)select @CreateRPT = 'CREATE TABLE [dbo].rpt' + @Table + '( [AdminId] [int] NOT NULL, [Grade] [varchar](2) NOT NULL, [RepLevel] [varchar](3) NOT NULL, [ProductId] [int] NOT NULL, [BurstGroup] [int] NOT NULL,CONSTRAINT [PK_rptCrystalSchema] PRIMARY KEY CLUSTERED ( [AdminId] ASC, [Grade] ASC, [RepLevel] ASC, [ProductId] ASC, [BurstGroup] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]) ON [PRIMARY]'----Third Create the actual table. exec (@CreateRPT)----Fourth Display if the table created or failed. if @@ERROR <> 0 begin PRINT 'rpt' + @Table + ' was NOT created due to an Error'endelsebegin PRINT 'rpt' + @Table + ' Created'end--Create the QA Table with the same name----First Drop an existing table with that name.if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'qa' + @Table)begin exec('DROP TABLE [dbo].[qa' + @Table + ']') PRINT 'Table qa' + @Table +' dropped'endelsebegin print 'No table exists with the name qa' + @Tableend----Second Copy the existing table with qa prefix instead of rptDeclare @CreateQA nvarchar(max)Select @CreateQA = 'SELECT * INTO dbo.qa' + @Table + ' FROM dbo.rpt' + @TableEXEC (@CreateQA)----Third Display if the table create or failed. if @@ERROR <> 0 begin PRINT 'qa' + @Table + ' was NOT created due to an Error'endelsebegin PRINT 'qa' + @Table + ' Created'end
Both tables create correctly, but they are identical. How can I add 1 to the size of each column in the qa table? (FYI, create code is truncated, the table actually has well over 100 columns)