Results 1 to 23 of 23

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

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,263

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

    Hi.

    Code:
    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,515

    Re: IS there a way to change values while full select - insert into temp table?

    Huh? (though untested)
    Code:
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,263

    Re: IS there a way to change values while full select - insert into temp table?

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    4,326

    Re: IS there a way to change values while full select - insert into temp table?

    Quote Originally Posted by sapator View Post
    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>

  5. #5

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,263

    Re: IS there a way to change values while full select - insert into temp table?

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,515

    Re: IS there a way to change values while full select - insert into temp table?

    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
    Code:
    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)
    Code:
    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!
    Code:
    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;
    Last edited by Zvoni; Oct 7th, 2022 at 05:20 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,515

    Re: IS there a way to change values while full select - insert into temp table?

    Quote Originally Posted by wqweto View Post
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,515

    Re: IS there a way to change values while full select - insert into temp table?

    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
    Code:
    BEGIN TRANSACTION;
    UPDATE SourceTable SET Field4=IIF(Field4='11', '1', Field4);
    /*check result */
    COMMIT;
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,263

    Re: IS there a way to change values while full select - insert into temp table?

    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
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,515

    Re: IS there a way to change values while full select - insert into temp table?

    10.000 lines in a Stored Procedure?
    *doffing my hat*
    I don't envy you
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  11. #11

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,263

    Re: IS there a way to change values while full select - insert into temp table?

    Yep.
    Actually it's a pre backup SP sap accounting bridge, the complete is double the lines!
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  12. #12
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,515

    Re: IS there a way to change values while full select - insert into temp table?

    Quote Originally Posted by sapator View Post
    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)")
    Last edited by Zvoni; Oct 7th, 2022 at 07:48 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  13. #13
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,995

    Re: IS there a way to change values while full select - insert into temp table?

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  14. #14

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,263

    Re: IS there a way to change values while full select - insert into temp table?

    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)
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  15. #15
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,995

    Re: IS there a way to change values while full select - insert into temp table?

    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:
    Code:
    -- 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:
    Code:
    -- 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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  16. #16

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,263

    Re: IS there a way to change values while full select - insert into temp table?

    Quote Originally Posted by techgnome View Post
    Or you can do it like this:
    Code:
    -- 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
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  17. #17
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,995

    Re: IS there a way to change values while full select - insert into temp table?

    What's with the select * ????

    there's also this:
    Code:
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  18. #18
    PowerPoster
    Join Date
    Jun 2013
    Posts
    6,260

    Re: IS there a way to change values while full select - insert into temp table?

    Quote Originally Posted by sapator View Post
    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):
    Code:
    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:
    Code:
    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

  19. #19
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,515

    Re: IS there a way to change values while full select - insert into temp table?

    Quote Originally Posted by Schmidt View Post
    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):
    Code:
    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:
    Code:
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  20. #20
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    4,326

    Re: IS there a way to change values while full select - insert into temp table?

    To preserve original column order you can use something like this

    Code:
    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>

  21. #21
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,515

    Re: IS there a way to change values while full select - insert into temp table?

    Quote Originally Posted by wqweto View Post
    To preserve original column order you can use something like this

    Code:
    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!
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  22. #22
    PowerPoster
    Join Date
    Jun 2013
    Posts
    6,260

    Re: IS there a way to change values while full select - insert into temp table?

    Quote Originally Posted by Zvoni View Post
    ...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 ...:
    Code:
    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

  23. #23

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,263

    Re: IS there a way to change values while full select - insert into temp table?

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width