Insert a new record in a temp tableq

  • Hi,

    Not sure if this is possible. I'm looking to insert a new row into a temp table if a field on the record read is a specific value.

    Example

    Row reads Name, Address, Date, Flag

    first row = Jane, 1 Main St, 10/5/23, z

    second row = Tarzan, 12 Main St, 10/4/23, x

    Now, flag = 'x' so I want to insert a row with the same data as second row , but add a day to Date field.

    Hope that makes sense....

    Thanks

    Joe

     

  • How are you ordering the data? I do not see what makes the second row come after first.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I was using this as a sample, I actually order by a personObjectId in the query that writes to the temp table.

  • Best guess. Please provide your sample data in consumable format in future.

    DROP TABLE IF EXISTS #Somedata;

    CREATE TABLE #SomeData
    (
    Id INT
    ,SomeData VARCHAR(50)
    ,SomeDate DATE
    ,Flag CHAR(1)
    );

    INSERT #SomeData
    (
    Id
    ,SomeData
    ,SomeDate
    ,Flag
    )
    VALUES
    (1, 'Data1', '20231005', 'Z')
    ,(2, 'Data2', '20231004', 'X');

    SELECT *
    FROM #SomeData sd;

    WITH LastR
    AS (SELECT TOP(1)
    *
    FROM #SomeData sd
    ORDER BY sd.Id DESC)
    INSERT LastR
    (
    Id
    ,SomeData
    ,SomeDate
    ,Flag
    )
    SELECT lr.Id
    ,lr.SomeData
    ,DATEADD (DAY, 1, lr.SomeDate)
    ,lr.Flag
    FROM LastR lr
    WHERE lr.Flag = 'X';

    SELECT *
    FROM #SomeData sd;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thank You Phil,

     

    I think I can work with this ....

    Much appreciated!!

  • Phil Parkin wrote:

    How are you ordering the data? I do not see what makes the second row come after first.

    I didn't see anything specifying a filter based on order.  The OP simply said, "Now, flag = 'x' so I want to insert a row with the same data...".  I think that the mention of the second row was to help identify it, not necessarily to be included in the criteria for the update.

    I updated your setup to change the ID to an IDENTITY column.

    DROP TABLE IF EXISTS #Somedata;

    CREATE TABLE #SomeData
    (
    Id INT IDENTITY
    ,SomeData VARCHAR(50)
    ,SomeDate DATE
    ,Flag CHAR(1)
    );


    /* Setup table */INSERT #SomeData
    (
    SomeData
    ,SomeDate
    ,Flag
    )
    VALUES
    ('Data1', '20231005', 'Z')
    ,('Data2', '20231004', 'X');

    I think the code is as simple as the following.

    INSERT #SomeData
    (
    SomeData
    ,SomeDate
    ,Flag
    )
    SELECT SomeData
    , DATEADD(DAY, 1, SomeDate)
    , Flag
    FROM #SomeData
    WHERE Flag = 'X'

    /* Optionally add a WHERE or EXCEPT clause to prevent re-inserting duplicate data. */
    SELECT *
    FROM #SomeData

    Drew

    • This reply was modified 5 months ago by  drew.allen.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • jbalbo I think you are missing an important aspect of database tables when inserting records into a table it makes no difference what order you put them in unless you are using an Identity field and even then if you really want them in a specific order ou put that in as a column called say SortOrder.

    This way when you pull the data from the table you can sort it as you want it to be sorted.

    Now I have seen SQL developers insert into a table using an ORDER BY which is very often totally meaningless and should not be done. As in the end you sort when you extract not when you insert and if you need a specific order you design that into the table.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply