CODEWITHSUNDEEP
sql-servert-sql
Internet Engineer
Internet Engineer

Reputation: 2534

Insert Into temp table and Then Update

I use a temp table to insert data that will be later on updated.

For example:

SELECT 
    Name, 
    Address, 
    '' as LaterTobeUpdateField
INTO #MyTempTable
FROM OriginalTable

Then I update the temp table

UPDATE #MyTempTable
SET LaterTobeUpdateField = 'new text'

Since the original value of LaterTobeUpdateField was '', during the update I will get the following error:

String or binary data would be truncated.

Is there a workaround other than declaring the temp table before is being used?

This also works, but its ugly

SELECT
    Name, 
    Address, 
    '             ' as LaterTobeUpdateField
INTO #MyTempTable
FROM OriginalTable

Upvotes: 0

Views: 3287

Answers (3)

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

A little wordy but safe solution:

declare @myTempTable(name varchar(50),
        address varchar(50),
        LaterTobeUpdateField varchar(20))
insert @myTempTable
select name, address, '' a from originalTable
--do whatever you want

Upvotes: 1

DaveX
DaveX

Reputation: 745

SELECT 
    Name, 
    Address, 
    '' as LaterTobeUpdateField
INTO #MyTempTable
FROM OriginalTable

ALTER TABLE #MyTempTable
ALTER COLUMN LaterTobeUpdateField NVARCHAR(20)

UPDATE #MyTempTable
SET LaterTobeUpdateField = 'new text'

Upvotes: 0

Lamak
Lamak

Reputation: 70638

As per my comment: You could create the temp table first or defining the length of your column beforehand

SELECT
    Name, 
    Address, 
    CONVERT(VARCHAR(20),'') as LaterTobeUpdateField
INTO #MyTempTable
FROM OriginalTable;

Upvotes: 2

Related Questions