Insert a new record in a temp tableq

Question

Insert a new record in a temp tableq

jbalbo

SSCertifiable

Points: 7583
More actions
October 5, 2023 at 2:57 pm

#4302122

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jbalbo SSCertifiable Points: 7583 More actions · Answer 4

Thank You Phil,

I think I can work with this ....

Much appreciated!!

drew.allen SSC Guru Points: 76985 More actions · Answer 5

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 1 year, 1 month ago by drew.allen.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Dennis Jensen SSChasing Mays Points: 604 More actions · Answer 6

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.

This reply was modified 1 year, 1 month ago by Dennis Jensen.