I have two table CustomerProfile and NewData. The CustomerProfile has CustomerId,ProfileId,startDate and endDate. The NewData table is new data that is coming in just has the ProfileId.I create a temp table with ids from both tables named #groupedProfileIds. I then loop the temp table checking if the id is in any of the main table. Then base the the count I do an insert or update . I have tried to do it with while and checking the count of both tables but wanted to see if there is a better way.

The goal of this is to have a updated CustomerProfile table base on the newData table. If the id are not in the newData table the CustomerProfile table will be updated with a end date. If they are new Id they will be inserted with a start date. I use ‘ @OldProfileId = 1 and @NewProfileId = 0’ to do the update because the data is not in the new table. For the insert I do ‘ @OldProfileId = 0 and @NewProfileId = 1’ because the data is just in the newData. I don’t need to check 1 because they would be in both table .


            select * INTO #groupedProfileIds from(
            SELECT ProfileId FROM CustomerProfile
                SELECT ProfileId FROM NewData)  as #tmp

            declare @Rowcount int 
            select @Rowcount=count(*) from #groupedProfileIds
            while( @Rowcount>0)
             select @Rowcount=@Rowcount-1;
             DECLARE @ProfileIds int
             set @ProfileIds = (SELECT Id FROM #groupedProfileIds order by Id desc OFFSET @Rowcount ROWS FETCH NEXT 1 ROWS ONLY);
             print @ProfileIds

             DECLARE @OldProfileId INT 
             SET @OldProfileId = (SELECT Count(ProfileId) FROM CustomerProfile where ProfileId = @ProfileIds)

             DECLARE @NewProfileId INT
             SET @NewProfileId = (SELECT Count(ProfileId) FROM #NewData where ProfileId = @ProfileIds)

             IF @OldProfileId = 1 and @NewProfileId = 0
                update DBO.CustomerProfile set endDate = GETDATE() where ProfileId = @ProfileIds 


             IF @OldProfileId = 0 and @NewProfileId = 1
                INSERT INTO DBO. CustomerProfile (CustomerId, ProfileId,startDate)
                VALUES (CustomerId,@ProfileIds,GETDATE())
  • You don't really say what you want. You say your NewTable has only profileId, what do you want? Describe the intention. From a proper specification of your requirement the approach almost writes itself. The code you present could not be further away from a specification of your requirement, aside from fighting against relational database concepts. Sep 10, 2022 at 16:54
  • What do you INSERT? How do you find a customerId from only a profileId? And where suddenly do you get "cancer" and "cancer id"??? Sep 10, 2022 at 17:03
  • I updated the post with new information hope it helps
    – Jefferson
    Sep 10, 2022 at 17:28

2 Answers 2


The best way to do this is you create a query which has the data you want. Then you can decide what to insert and what to update.

If the profile id is not in CustomerProfile you want to insert a new row with a customerId, you don't say from where you get that customerId since all you have is a profileId??

The deficiency by which you specify your requirement is the reason why you don't find a solution. And nobody can help you because you don't say what you want. Suddenly inserting "cancer" somewhere makes it so much worse!

Nobody can answer your question because you don't say what you want. However, if we make some assumptions then the best we do is outline a direction which you can go.

Can we guess anything from your incomplete table schema?

CREATE TABLE CustomerProfile (
  customerProfileId integer PRIMARY KEY,
  customerId        integer NULL,
  profileId         integer,
  startDate         datetime,
  endDate           datetime NULL
  profileId         integer

The way this is written there should be a Customer table and a Profile table

  customerId        integer PRIMARY KEY,
  profileId         integer PRIMARY KEY,

then your tables you did mention should be completed as:

CREATE TABLE CustomerProfile (
  customerProfileId integer PRIMARY KEY,
  customerId        integer REFERENCES Customer(customerId),
  profileId         integer REFERENCES Profile(profileId),
  startDate         timestamp,
  endDate           timestamp
  profileId         integer REFERENCES Profile(profileId)

I first retained your customerId NULL non-constraint to give myself an out to not having to worry how I infer the customerId from a mere profileId when inserting. But then I figured might just complete your schema, to have it logical. This creates additional tasks to insert a new Customer and Profile row, I know how I can do that, but you didn't ask for it, so I won't do it.

So the above is just for me to get my head around what you want. You still haven't said it quite sufficiently.

I assume from your table that your relationship between Customer and Profile is many-to-many. And I doubt that. I think you have not really thought through your business data logic. But for what I am proposing it doesn't matter.

I am also not interested in SQL Server stuff, but I will use standard SQL (my database is PostgreSQL). If you don't think SQL (the language, not your software) you will not be a good database designer / programmer / administrator, whatever you want to call yourself.

In standard SQL "datetime" is called "timestamp" and the standard way to get the current timestamp is current_timestamp.

About your endDate you say:

If the id are not in the newData table the CustomerProfile table will be updated with a end date.

So when inserting into your table, I assume that startDate = current_timestamp and endDate = NULL.

Finally I assume that these ids are "sequences" but I will abstain from any sequence extension to any database and just determine the next value from

SELECT max(fooId) + 1 FROM Foo

where "Foo" may be Customer, Profile, or CustomerProfile.

With all these assumptions made, I begin, as I said, the way you should always begin. Write a SELECT which will give you the final desired table, or, here, I begin with the INSERT part:

INSERT INTO CustomerProfile(customerProfileId, customerId, profileId, startDate, endDate)
WITH DistinctNewProfileIds AS (
), MaxCustomerProfileId AS (
  SELECT max(customerProfileId) AS maxCustomerProfileId
    FROM CustomerProfile
), MaxCustomerId AS (
  SELECT max(customerId) AS maxCustomerId
    FROM Customer
), ProfileIdWithOrdinal AS (
  SELECT rank() OVER (ORDER BY profileId) AS ordinal,
    FROM DistinctNewProfileIds
SELECT maxCustomerProfileId + ordinal AS customerProfileId,
       maxCustomerId + ordinal AS customerId,
       current_timestamp AS startDate,
       NULL AS endDate
  FROM ProfileIdWithOrdinal 
  CROSS JOIN MaxCustomerProfileId
  CROSS JOIN CustomerId

Now the UPDATE. You seem to expect all profileIds which are still not ended to appear in your NewData every time. I initially understood you would extend the endDate every time you find the profileId in the NewData. It makes me wonder what happens if a profileId suddenly re-appears in the NewData after you already have missed it once and you added an endDate already? Are you supposed to wipe out the endDate with NULL again? You didn't ask for it, so I will not do it. But you haven't thought through your design if you don't consider that.

Here is the partition of your table which you update, after the update:

WITH DistinctNewProfileIds AS (
SELECT customerProfileId, customerId, profileId, 
       CASE WHEN p.profileId IS NULL
            THEN current_timestamp
            ELSE NULL END as endDate
  FROM CustomerProfile cp
  LEFT OUTER JOIN DistinctNewProfileIds p
    ON(p.profileId = cp.profileId)

So here I actually would reset your endDate to NULL if the profileId re-appears. But the update anyway gets really simple:

UPDATE CustomerProfile 
   SET endDate = current_timestamp
 WHERE profileId NOT IN (SELECT profileId FROM NewTable)

You run the UPDATE part first, then the INSERT. And you're done.

The best description of your requirement is a SELECT query which will make what you want. What you INSERT vs. UPDATE is then just a secondary issue. This is even more true if you had to also insert Customer and Profile rows to satisfy foreign key constraints. In PostgreSQL I do these things in a single query. In lesser databases like Oracle or MS SQL Server I guess you have to do multiple steps in a transaction.

  • Is this using a loop? How is it doing the insert?
    – Jefferson
    Sep 10, 2022 at 19:42
  • If you think about "loop" in relational databases you are thinking wrong. The INSERT ... SELECT is inserting the whole batch of new rows at once. Sure the system runs in some kind of loop to do that, but you should never think procedurally in loops in SQL. In fact, nested loop joins can be the kiss of death in large table joins. Sep 11, 2022 at 6:59
  • What is the DistinctNewProfileIds table? sqlfiddle.com/#!18/9eecb/169726
    – Jefferson
    Sep 11, 2022 at 11:04
  • As you can see, it is just distinct profileId, because I cannot trust that profile in your NewTable is not repeating and since I am ranking and inserting from that table, I need to be sure no profileId rows repeat. Sep 12, 2022 at 8:03

I am not sure if I understood your question in a right way, but if what I understood was true the query below will compares ProfileId of two tables and updates the endDate field with current date it also inserts profileIds from newdata table which are not exist in CustomerProfile (customerId field is not available for them) and fills StartDate field with current date:

drop table if exists #groupedProfileIds
drop table if exists #groupedProfileIds_agg
select * INTO #groupedProfileIds 
from(SELECT ProfileId ,1 as flag FROM CustomerProfile
    SELECT ProfileId, 2 as flag FROM NewData)  as #tmp
-- we use this flag to determine the table which record came from-------
select * INTO #groupedProfileIds_agg
from (select  ProfileId,sum(flag) as flag_agg
      from #groupedProfileIds
      group by ProfileId) #temp2
-- we used second temp table to store summation of flag to avoid using aggregate function in where clause  
-- when flag is 1  it shows the ProfileId only exists in CustomerProfile, 
--when it is 2 the ProfileId only exists in NewData and it can be 3 if same profileId is available in both ----------

update DBO.CustomerProfile set endDate = GETDATE()
        where  ProfileId in (select ProfileId from #groupedProfileIds_agg
                                where flag_agg = 1) 
insert INTO DBO. CustomerProfile (ProfileId ,startDate) 
select ProfileId,getdate()
from #groupedProfileIds_agg
    where flag_agg=2
  • There also need to be an insert not just an update
    – Jefferson
    Sep 10, 2022 at 12:54
  • 1
    Hi Jefferson I edited the answer please check and let me know if any change is needed Sep 12, 2022 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.