CODEWITHSUNDEEP
sqlsql-server
kill9all
kill9all

Reputation: 111

Changing INSERT INTO into an UPDATE command

I need to convert and INSERT statement into an UPDATE with a couple of thousand rows.

I've used SSMS Import/Export wizard to INSERT the values into my TEST environment from a text file, but have been advised that I should use UPDATE.

I used RapidSQL to create the INSERT script, but need to convert it to an UPDATE. The 4 and 5 digit numeric values already exist in the target table and would presumably be used in the WHERE statement. Appreciate the help!

INSERT INTO TRAPEZE.STOPS ( STOPID, USERLONGSTRING1 ) 
        VALUES ( 10268, 'Westbound Ford Rd @ 189B St' )
/

INSERT INTO TRAPEZE.STOPS ( STOPID, USERLONGSTRING1 ) 
        VALUES ( 9603, 'Eastbound Adams Rd @ Cowan (Flag)' )
/

The target column USERLONGSTRING1 should eventually contain the the value 'Westbound Ford Rd @ 189B St' where the STOPID is 10268. The text file contains both the STOPID and location values.

This a 24x7 live database with many dependent applications using it for real-time application, so truncation is not an option.

My initial INSERT statement was created by importing into a STAGING table in a TEST server, but my DBAs (spoil-sports) do not want me to create additional tables, they just want an UPDATE script.

Upvotes: 0

Views: 1446

Answers (2)

kill9all
kill9all

Reputation: 111

I cheated and just put the values into a spreadsheet and then cut and paste into notepad ++ to add quotes, klugey, but it worked:

enter image description here

Upvotes: 0

Cetin Basoz
Cetin Basoz

Reputation: 23807

Using a temp table, your script changes would be easier:

declare @myTemp table (STOPID int, USERLONGSTRING1 varchar(200))

INSERT INTO @myTemp ( STOPID, USERLONGSTRING1 ) 
        VALUES ( 10268, 'Westbound Ford Rd @ 189B St' )

INSERT INTO @myTemp ( STOPID, USERLONGSTRING1 ) 
        VALUES ( 9603, 'Eastbound Adams Rd @ Cowan (Flag)' )

-- ...

update TRAPEZE.STOPS 
   set USERLONGSTRING1 = t.USERLONGSTRING1
from TRAPEZE.STOPS ts inner join @myTemp t on t.StopId = ts.StopId;

Upvotes: 1

Related Questions