January 5, 2011 at 12:22 pm
Greetings. I am needing to create a temp table on the fly, with SELECT INTO from an existing table, but I need to add some new columns with all NULL values, by adding new_col = NULL. The problem is that the new columns always end up as INT columns. I can make the new column be VARCHAR(8000) by using new_col = ' '. However, when I go to update the new column, I get the 'data will be truncated' error. I can look at the data type and length in the sys tables, and they seem to indicate the 8000 legnth, so I'm not sure why the update fails. Based on the example below, does anyone have any ideas? Essentially, what I need to do is add new columns of VARCHAR(100) with NULL values. Thanks.
IF OBJECT_ID('TempDB..#test','u') IS NOT NULL
DROP TABLE #test
SELECT TOP 1
[name],
new_col = ' '
INTO #test
FROM sysobjects
SELECT
table_name = so.name,
column_name = sc.name,
data_type = st.name,
date_len = st.length
FROM tempdb..sysobjects so INNER JOIN
tempdb..syscolumns sc
ON so.id = sc.id INNER JOIN tempdb..systypes st
ON sc.xusertype = st.xusertype
WHERE LEFT(so.name,5) = '#test'
Now, try this...
UPDATE #test
SET new_col = 'xx'
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 5, 2011 at 12:30 pm
IF OBJECT_ID('TempDB..#test','u') IS NOT NULL
DROP TABLE #test
SELECT TOP 1
[name],
CAST(NULL as Varchar(8000)) as new_col
INTO #test
FROM sysobjects
January 5, 2011 at 2:02 pm
DOH! I always forget about CAST. Thank you John, that did the trick.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 5, 2011 at 2:31 pm
You can also add columns with an Alter Table command for temp tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply