How do I generate an UPDATE TABLE script from the rows of an existing table

I have a table:

CREATE TABLE [dbo].[New Sector Change](
[operation_ver_id] [nvarchar](255) NOT NULL,
[OPERATION TYPE_ID_OLD] [smallint] NOT NULL,
[OPERATION_TYPE] [smallint] NOT NULL
) ON [PRIMARY];

Sample records are:

operation_ver_id                                                OPERATION TYPE_ID_OLD OPERATION_TYPE
4B202FFF-0F4B-4B7B-824E-7DADAA863517 93 88
0C8B5B20-F572-4DA2-9775-7E933A0D3037 93 88
7364F748-2112-431F-BC40-64B380847FAE 93 88
CFE436BF-8B66-4EA4-933B-58AAA466570B 93 88
B631EB1E-CF3D-4DE7-9E90-DABDC857AB8E 93

There are about 2000 rows in the table. I loaded it from an Excel spreadsheet. 

I need to generate an UPDATE script of the type for all 2000 rowsHow can this be done?:

UPDATE dbo.[New Sector Change]
SET OPERATION_TYPE =90
WHERE OPERATION_VER_ID = '4B202FFF-0F4B-4B7B-824E-7DADAA863517';


September 4th, 2015 2:10pm

If you want to update for all rows, why not the below? -Do not use the filter condition.

UPDATE dbo.[New Sector Change]
SET OPERATION_TYPE =90

Free Windows Admin Tool Kit Click here and download it now
September 4th, 2015 2:16pm

The OPERATION_TYPE should be different for each OPERATION_VER_ID? 
If not then @latheesh NK has given you the answer. If it is different please tell us how you decide the value of the OPERATION_TYPE.
September 4th, 2015 2:22pm

No. The update statement is actually going to run in a main table of a million or so rows. I have a spreadsheet of 2000 OPERATION_VER_IDs that require a change in the  OPERATION_TYPE. I thought it will be ridiculous to manually do the UPDATE statement 2000 times. So I loaded the spreadsheet in this temporary table dbo.[New Sector Change].

I was wondering if we can generate an update script from this new temporary table using each OPERATION_VER_ID in the where clause and set to the matching OPERATION_TYPE.

Thanks
 

Free Windows Admin Tool Kit Click here and download it now
September 4th, 2015 2:28pm

So if I understand well you have two tables. One is the main where the update must run and the second has two columns the OPERATION_VER_ID and the new value (let's say new_value). Correct me if I am wrong. 
If this is the case then you can solve your problem with a join.
Example:

UPDATE mt
SET mt.OPERATION_TYPE = nt.new_value
from 
[main_table] mt
JOIN [new_values_table] nt ON nt.OPERATION_VER_ID = mt.OPERATION_VER_ID 


Does this help?

September 4th, 2015 2:37pm

The update statement is actually going to run in a main table of a million or so rows. I have a spreadsheet of 2000 OPERATION_VER_IDs that require a change in the  OPERATION_TYPE. I thought it will be ridiculous to manually do the UPDATE statement 2000 times. So I loaded the spreadsheet in this temporary table dbo.[New Sector Change].

I was wondering if we can generate an update script from this new temporary table using each OPERATION_VER_ID in the where clause and set to the matching OPERATION_TYPE.

This is the main table:

CREATE TABLE [dbo].[OPERATION_VER](
[OPERATION_VER_ID] [dbo].[GUID] NOT NULL,
[ORIG_SRC_OPERATION_VER_ID] [dbo].[GUID] NULL,
[OPERATION_ID] [dbo].[GUID] NOT NULL,
[OPERATION_TYPE] [smallint] NOT NULL

 CONSTRAINT [PK_OPERATION_VER] PRIMARY KEY NONCLUSTERED 
(
[OPERATION_VER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

We are trying to update this table where the rows are in the dbo.[New Sector Change] table to the corresponding OPERATION_TYPE in that table.

Free Windows Admin Tool Kit Click here and download it now
September 4th, 2015 2:41pm

Could you provide the structure of you temporary table dbo.[New Sector Change]?
September 4th, 2015 2:45pm

Well. You did understand my question correctly. This JOIN does make sense.

However, please explain. I am simply testing on my local machine. How do I do this in the UAT database.

Should I load these records in a temp table before the join and then drop the TEMP table. What will the statements be?

Thanks very much.

Free Windows Admin Tool Kit Click here and download it now
September 4th, 2015 2:46pm

You said that you already have loaded the data in a temporary table. If so then use the join and then drop the temporary table. Otherwise clarify from where you need to get the data (ex excel file?) so as to help you.
September 4th, 2015 2:51pm

I loaded it into a permanent table using export import utility. I am an Oracle DBA and from my experience that I need to store it in a temp table not permanent. 

Yes, the data is in an excel file.

Free Windows Admin Tool Kit Click here and download it now
September 4th, 2015 2:56pm

So as to load your data you can use this :

INSERT INTO [tblTemp] ([Column1], [Column2], [Column3], [Column4])

SELECT A.[Column1], A.[Column2], A.[Column3], A.[Column4]
FROM OPENROWSET 
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=FILE_PATH;HDR=YES', 'select * from [Sheet1$]') AS A;
Replace the FILE_PATH, Sheet No and column names with your Excel. Then Run the join above. At the end just drop the temporary table. 

Hope it helped!

September 4th, 2015 3:00pm

Ok great. Thanks very much. I will try this.
Free Windows Admin Tool Kit Click here and download it now
September 4th, 2015 3:02pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics