[RESOLVED] IS there a way to change values while full select - insert into temp table?

[sapator]

Hi.

SELECT	top 100 C.*
INTO	#ZZ_Cash
--select *,Case when C.transC_strType ='11' THEN '1' ELSE  C.transC_strType END
FROM tblTrans_Cash C

What I was looking is if the is a way to just change the value type without having to specify all the values one by one (yes I know * bad , water good but still)
and of course without having to update as that will mean double cycle on a millions row table.

Thanks.

[Zvoni]

Huh? (though untested)

BEGIN TRANSACTION;
SELECT top 100 C.* INTO #ZZ_Cash FROM tblTrans_Cash C;
UPDATE #ZZ_CASH SET transC_strType=IIF(transC_strType='11', '1', transC_strType);
/*Check Values if correct*/
COMMIT;

How do you figure some millions rows?
You've put a limit to TOP 100

[sapator]

Holdonholdonholdon.
You are updating, as I've said I don't want to give an update since it's extra burden , although nice coding with the IIF.
Yes I've put top 100 for test since this is a live DB, I sure wouldn't want to be inserting and updating millions of rows while the DB is active and fully running.

[wqweto]

What I was looking is if the is a way to just change the value type without having to specify all the values one by one (yes I know * bad , water good but still)

You are confusing values for columns. No, there is no way in standard SQL (and T-SQL dialect particularly) to SELECT all the columns but one particular column.

For instance in BigQuery SQL dialect there is SELECT * EXCEPT(MyColumn) but this is not available in T-SQL so you have to either explicitly list all columns but the one particular or resort to UPDATE to change values or ALTER TABLE .. ALTER COLUMN to change data-type of the column (not sure what you are talking about in OP).

cheers,
</wqw>

[sapator]

Hmm, yes that's what I was thinking.
I will see if I can find another way as the tables are 100+ columns * 10+ tables so it would be very messy to specify everything.
Probably selecting in data portions and updating but I will have to check the performance.
Anyway thanks.

[Zvoni]

How many columns are you talking about?
If it's a one-time thing (From SSMS?) and you're just to lazy to write out all the Column-names, why not query the Information-Schema?
https://www.mssqltips.com/sqlservertutorial/183/information-schema-columns/
https://www.sqlservertutorial.net/sql-server-string-functions/sql-server-string_agg-function/

Aircode

SELECT STRING_AGG(ColumnName, ', ') As MyColumns FROM INFORMATION_SCHEMA.Columns Where Table_Name=MyTable

That way you would get one String of all ColumnNames already concatenated "Field1, Field2, Field3, ...."

Construct your Insert-Query (copy&paste the Fieldnames from Result of above)

INSERT INTO #ZZ_Cash (Field1, Field2, Field3, Field4, Field5,  .....) 
SELECT Field1, Field2, Field3, IIF(Field4='11', '1', Field4), Field5 ... FROM SourceTable

EDIT: And since you said it's a "live" Database: USE TRANSACTION!

BEGIN TRANSACTION;
INSERT INTO #ZZ_Cash (Field1, Field2, Field3, Field4, Field5,  .....)  SELECT Field1, Field2, Field3, IIF(Field4='11', '1', Field4), Field5 ... FROM SourceTable;
/* Check Result */
COMMIT; / ROLLBACK;

[Zvoni]

ALTER COLUMN to change data-type of the column (not sure what you are talking about in OP).

cheers,
</wqw>

He has a column of type varchar (?), which has Char-values.
if the value is "11", change it to "1" else leave it as is.
Nothing to do with Datatype of the column itself

[Zvoni]

btw: Just noticed something: You're talking about inserting into a temp-table.
Yes, correct on the cycles (Performance) but what's going to happen, when you have the temp. table in that "new" state?
Copy it back to somewhere into the "live"-tables?

because if your goal is to "copy" it back to the source-table...

Just do a backup (of the table) beforehand
Aircode

BEGIN TRANSACTION;
UPDATE SourceTable SET Field4=IIF(Field4='11', '1', Field4);
/*check result */
COMMIT;

[sapator]

You would be absolutely correct but the issue is the performance as the temp table gets millions of rows and do other calculations but it does not get the WHOLE live table.
The result is actually 24 minutes with temp tables vs 50 minutes without so it is preferred. Also it's a part of a 10.000 lines SP so it would take me a month

[Zvoni]

10.000 lines in a Stored Procedure?
*doffing my hat*
I don't envy you

[sapator]

Yep.
Actually it's a pre backup SP sap accounting bridge, the complete is double the lines!

[Zvoni]

Yep.
Actually it's a pre backup SP sap accounting bridge, the complete is double the lines!

Just to make sure i understood your original problem correctly:
You're actually writing that SP, and you'd need above mentioned INSERT-Routine in that SP?
as in "Write once, Fire it when needed"?

If that's the case, i gave you a possible approach with the Query on the INFORMATION_SCHEMA

EDIT: And yes, i'm aware that by NOT using SELECT * you don't have any "dynamic" with the query
as in: if you change (add/delete) fields, you have to change the SQL-Statement.
But your conditional "update" is the roadblock here (and that MSSQL doesn't have a "SELECT * EXCEPT(MyColumn)")

[techgnome]

If this is SQL Server, you can also right-click on the table in SSMS Select SCRIPT AS then Select ... And you get a complete select with all the cols in it. You can also select Insert and you'll get a complete Insert statement with all the cols. Use the Insert into query along with the Select one... replace the field value in the right spot ... done.

-tg

[sapator]

I wish it was that easy.
That along is 10*1000 lines.
Anyway I just needed to know if my OP was doable. It's not, I will figure out another way.
Thanks.

P.S. I'm not sure about the information schema. If the goal is to shorten or widthen the column then I don't want that, I need the actual value changed (11 could be 123)

[techgnome]

Why select all the fields anyways? If all you're updateing is one field, at most all you need from the oritginal table is the field in question and hte ID.

Here's how I'd probably do it:

-- Get the data
select id, transC_strType, transC_strType as newValue
into #temp_table_update
from someTable;

-- update it....
update #temp_table_update
set newValue = Case when C.transC_strType ='11' THEN '1' ELSE  C.transC_strType END;

-- return the rows that were changed...
select * from #temp_table_update
where transC_strType <> newValue;

Or you can do it like this:

-- Get the data and update it
select id, transC_strType, 
    Case when C.transC_strType ='11' THEN '1' ELSE  C.transC_strType END as newValue
into #temp_table_update
from someTable;

-- return the rows that were changed...
select * from #temp_table_update
where transC_strType <> newValue;

To update the original table, just issue an update and join to the temp table on the id.

-=tg

[sapator]

Or you can do it like this:

-- Get the data and update it
select id, transC_strType, 
    Case when C.transC_strType ='11' THEN '1' ELSE  C.transC_strType END as newValue
into #temp_table_update
from someTable;

;

-=tg

Ye that would be ideal if done like Select *

Anyways have a nice weekend

[techgnome]

What's with the select * ????

there's also this:

select *, 
    Case when C.transC_strType ='11' THEN '1' ELSE  C.transC_strType END as newValue
into #temp_table_update
from someTable;

;

But I don't see why you need ALL of the columns...


-tg

[Schmidt]

P.S. I'm not sure about the information schema. If the goal is to shorten or widthen the column...

This suggestion was made, to retrieve the *current* Field-Names of a given Table dynamically.
(it has nothing to do with "changing the data-type of a Field").

You can store the retrieved Field-List in a Variable first (minus some excluded Fields):

Declare @cols varchar(max);
Select  @cols = (Select '[' + STRING_AGG(Column_Name, '],[') + ']' 
  FROM  INFORMATION_SCHEMA.Columns 
  Where Table_Name = 't' --<<--define your tablename here
  And Column_Name Not In ('a','c') --<<--and the field-exclude-list here
);

And then follow up with an Exec-based (String-)Select, where you re-define the prior excluded fields:

Exec ('Select ' + @cols + 
  ', IIF(a=11, 1, a) a' +
  ', IIF(c=33, 3, c) c' +
  '  From t'
);

Here is an online-fiddle for the above:
https://dbfiddle.uk/_N8GFESS

HTH

Oaf

[Zvoni]

This suggestion was made, to retrieve the *current* Field-Names of a given Table dynamically.
(it has nothing to do with "changing the data-type of a Field").

Correct

You can store the retrieved Field-List in a Variable first (minus some excluded Fields):

Declare @cols varchar(max);
Select  @cols = (Select '[' + STRING_AGG(Column_Name, '],[') + ']' 
  FROM  INFORMATION_SCHEMA.Columns 
  Where Table_Name = 't' --<<--define your tablename here
  And Column_Name Not In ('a','c') --<<--and the field-exclude-list here
);

And then follow up with an Exec-based (String-)Select, where you re-define the prior excluded fields:

Exec ('Select ' + @cols + 
  ', IIF(a=11, 1, a) a' +
  ', IIF(c=33, 3, c) c' +
  '  From t'
);

Here is an online-fiddle for the above:
https://dbfiddle.uk/_N8GFESS

HTH

Oaf

Though, with this approach it's pretty much garanteed you won't get the "original" column-order of the source-table.
If you then want to write it back from your temp-table to the original one, you're pretty much forced to use an UPDATE combined with an INNER JOIN

[wqweto]

To preserve original column order you can use something like this

DECLARE @cols VARCHAR(MAX)

SELECT  @cols = STRING_AGG(CASE WHEN c.COLUMN_NAME = 'ID' THEN
                                'IIF(ID=0x11, 0x1, ID) AS ID'
                            ELSE
                                c.COLUMN_NAME
                            END, ', ') WITHIN GROUP (ORDER BY c.ORDINAL_POSITION)
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_NAME = 't'

SELECT  @cols

cheers,
</wqw>

[Zvoni]

To preserve original column order you can use something like this

DECLARE @cols VARCHAR(MAX)

SELECT  @cols = STRING_AGG(CASE WHEN c.COLUMN_NAME = 'ID' THEN
                                'IIF(ID=0x11, 0x1, ID) AS ID'
                            ELSE
                                c.COLUMN_NAME
                            END, ', ') WITHIN GROUP (ORDER BY c.ORDINAL_POSITION)
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_NAME = 't'

SELECT  @cols

cheers,
</wqw>

Nice one!

[Schmidt]

...with this approach it's pretty much garanteed you won't get the "original" column-order

Ok, similar to wqwetos approach, but in an easier to type fashion (IMO),
because the Schema-Table-Select can be left generic -
and the "exception-fields" replaced in their own code-line each ...:

Declare @tn nvarchar(max)='t'; --<<-- define the table-name here
Declare @cl nvarchar(max);     --<<-- receives the colname-list (in brackets)
Select  @cl = (Select '['+ STRING_AGG(Column_Name, '],[') +']' FROM INFORMATION_SCHEMA.Columns Where Table_Name=@tn);

Select  @cl = Replace(@cl, '[A]', 'IIF(a=11, 1, a) As a');
Select  @cl = Replace(@cl, '[C]', 'IIF(c=33, 3, c) As c');

Exec ('Select '+ @cl + ' From ' + @tn); --<<--get the final result in the right col-order

https://dbfiddle.uk/qmALJ5Ta

Olaf

[sapator]

Sorry to much work this week.
So if I'm to start using dynamic I would much rather prefer to cut down the select into query into parts and only update the needed but thanks for all the approaches.