Temp Table Error

[ryand09]

I'm creating the temporary table below...

CREATE TABLE #TempTest(P_ID int not null,
Type_1 varchar(1000) null default('N/A'),
Type_2 varchar(1000) default('N/A'),
Type_3 varchar(1000) default('N/A'),
Type_4 varchar(1000) default('N/A'),
Type_5 varchar(1000) default('N/A'),
Type_6 varchar(1000) default('N/A'),
Type_7 varchar(1000) default('N/A'),
Type_8 varchar(1000) default('N/A'),
Type_9 varchar(1000) default('N/A'),
Type_10 varchar(1000) default('N/A'),
Type_11 varchar(1000) default('N/A'),
Type_12 varchar(1000) default('N/A'),
Type_13 varchar(1000) default('N/A'))

When I attempt to dump data to it I get the followig error...

The total row size (13052) for table '#TempTest' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.

Is there any way to get around this as the field I'm taking the data from is varChar(1000) and I don't have any problems with that?

Thanks in Advance

[rmiao]

If you have 8 varchar(1000) columns or single varchar(8000) column in the table, that's fine. Since it does not exceed 8060 bytes limit. But 13 varchar(1000) columns in the table is different story.

[Rawhide]

Yes, there is a way around it. Store one Type entry per record and use an int field to indicate which one it is:

CREATE TABLE #TempTest(
P_ID int not null,
Type_Number int not null
Type_Text varchar(1000) not null default('N/A'))