SQL Server : multiple inserts into temp table while creating a value for each different insert

Question

Pardon my code, I am still learning.

What I want to do is take several select statements and insert them into a single temp table. For each select statement I would like some sort of identification to know which select statement the data came from.

I have a crude idea below to use the case statement below.

Error:

Msg 8152, Level 16, State 14, Line 14
String or binary data would be truncated.

Code:

if object_id('tempdb..#PP') is not null 
     drop table #PP

SELECT 
    #Pr.*, 
    Case
       When TranID = TranID then 'BK Problem'
       Else 'Error' End as 'Test'
INTO #PP
FROM #Pr
WHERE TypeID = 't'

--CCA Test
INSERT INTO #PP
    SELECT 
        #Pr.*, 
        Case
           When TranID = TranID then 'CCA Problem'
           Else 'Error' End as 'Test'
    FROM #Pr
    WHERE TypeID = 'r'

I appreciate any help pointing me in the right direction.

Thanks

score 2 · Answer 1 · answered May 16 '17 at 19:19

The issue is your case expression. When you create the table using select into it creates the "Test" column as varchar(10). But in your insert statement the value is 11 characters long. You can avoid this by cast/convert on your original case expression to make the column wide enough.

SELECT #Pr.*, 
       convert(varchar(20), Case
       When TranID = TranID then 'BK Problem'
       Else 'Error' End) as 'Test'
INTO #PP
FROM #Pr
WHERE TypeID = 't'

score 2 · Accepted Answer · answered May 16 '17 at 19:26

2

If you use SELECT.. INTO.. like this, the column length is decided by the max length of each column on the first insert.

So.. inserting 'BK Problem' first, creates the Test column with a length of 'BK Problem', or 10.
(Do this to see: PRINT LEN('BK Problem'))

Then inserting 'CCA Problem' into Test fails, because it is a length of 11 and is too long.

I don't understand the need for your case statement either, try this:

SELECT #Pr.*, CAST('BK Problem' as VARCHAR(11)) Test
INTO #PP
FROM #Pr
WHERE TypeID = 't'

--CCA Test
INSERT INTO #PP
SELECT #Pr.*, 'CCA Problem'
FROM #Pr
WHERE TypeID = 'r'

answered May 16 '17 at 19:26

Aaron Dietz

10,137
1
14
26

Thank you, I used case because I am still new to this and it is the only way I could thing to get a new column with the values I wanted. – Nlub May 16 '17 at 19:45
@Nlub NP. You were very close, and your case logic even worked just wasn't needed. Happy coding. PS. Very well written question, we all appreciate that. – Aaron Dietz May 16 '17 at 19:49

SQL Server : multiple inserts into temp table while creating a value for each different insert

2 Answers2