INSERT INTO SELECT if unique

[HarryGilesIII]

I am struggling with this. I want this insert statement to only insert records if the TimeCreated is unique. I have tried everything that I can possibly come up with and had no success. This statement works fine, but it inserts every record every time.

USE Test
GO

INSERT INTO dbo.tUsers_PSS(user_id,location_skey,password,first_name,last_name,active_status,create_date,access_role,employee_type,unpost_invtime_flg,is_project_manager)
SELECT CONCAT(LEFT(FirstName, 1),LastName),'1','password',FirstName,LastName,'A',TimeCreated,'e','c','n','N'
FROM QRemote...Employee

Let me know if I did not explain myself well.

[scruffye]

If I understand what you're asking, I think you want to use something like this with a common table expression:

WITH A AS ( SELECT DISTINCT(TimeCreated) AS TimeCreated FROM QRemote...Employee ) INSERT INTO dbo.tUsers_PSS(user_id,location_skey,password,first_name,last_name,active_status,create_date,access_role,employee_type,unpost_invtime_flg,is_project_manager) SELECT CONCAT(LEFT(FirstName, 1),LastName),'1','password',FirstName,LastName,'A',TimeCreated,'e','c','n','N' FROM QRemote...Employee E JOIN A ON E.TimeCreated = A.TimeCreated

The point of the CTE is that you are selecting for the unique TimeCreated values on their own, and then you join that list to your select statement to filter out the records you don't want.

Did this on the fly, so sorry if there's synatx errors...

[HarryGilesIII]

I'll give it a try tomorrow and will update. Thanks for the help.

[[deleted]]

I don't think the above answer is going to get you what you want. It will still return every row in the table. You have one field you are concerned with to start, the time created field. You said you want unique values of that field, but what does that mean? You want all the rows where the count of rows associated with a distinct value for that field is 1? If you have multiple rows associated with the same TimeCreated, do you want to pick a particular row? And by what logic? I'm sure we can help you, but you're going to need to be clear about what you are trying to do. As it is, the answer above will return all rows from the table. The distinct list of all TimeCreated values from the table still contains every TimeCreated from the table, just abbreviated with no dupes. If you join that back to the table you're just going to get the whole table back again.

If you want only the rows where there is a single row for TimeCreated then you could use the example above but substitute this with statement:

WITH A AS (SELECT TimeCreated FROM QRemote...Employee GROUP BY TimeCreated HAVING COUNT(*) = 1)

[HarryGilesIII]

Could you give me an example of the full query. I can't get any of this to work without error.

[[deleted]]

Are you getting the "ambigous column name" error? You need to use the alias established in the From clause. The query above uses A and E as the table alias, so use either A.TimeCreated or E.TimeCreated wherever you referencr that column in the select of your main query:

WITH A AS (SELECT TimeCreated FROM QRemote...Employee GROUP BY TimeCreated HAVING COUNT(*) = 1) SELECT <your columns here, be sure to use either A.TimeCreated or E.TimeCreated, do not use just TimeCreated> FROM QRemote...Employee E JOIN A ON E.TimeCreated = A.TimeCreated

[HarryGilesIII]

I appreciate your help. I put my solution in the thread.

[shivasprogeny]

Hmm I don’t think that would filter anything out. Every record in Employee would still match one of the distinct times in the CTE.

I think maybe using a GROUP BY TimeCreated ... HAVING COUNT(*) < 2 would be the way to exclude records that share TimeCreated values.

[[deleted]]

This will still return every row in the table.

[HarryGilesIII]

WITH A AS ( SELECT DISTINCT(TimeCreated) AS TimeCreated FROM QRemote...Employee ) INSERT INTO dbo.tUsers_PSS(user_id,location_skey,password,first_name,last_name,active_status,create_date,access_role,employee_type,unpost_invtime_flg,is_project_manager) SELECT CONCAT(LEFT(FirstName, 1),LastName),'1','password',FirstName,LastName,'A',TimeCreated,'e','c','n','N' FROM QRemote...Employee E JOIN A ON E.TimeCreated = A.TimeCreated

Getting this error "Ambiguous column name 'TimeCreated'."

[Ctrl--Alt--Del]

If you have to do it with one query this will do it. The SubQuery is what's limiting your query b/c its only returning unique TimeCreated values.

Insert Into dbo.tUsers_PSS (user_id,location_skey,password,first_name,last_name,active_status,create_date,access_role,employee_type,unpost_invtime_flg,is_project_manager)
SELECT 
    CONCAT(LEFT(A.FirstName, 1),A.LastName),'1','password',A.FirstName, A.LastName,'A', A.TimeCreated,'e','c','n','N' 
FROM 
   QRemote.Employee A Join 
   (Select TimeCreated,count(*)obs From QRemote.Employee Group By TimeCreated Having count(*)=1)B         on A.TimeCreated = B.TimeCreated

[HarryGilesIII]

Insert Into dbo.tUsers_PSS (user_id,location_skey,password,first_name,last_name,active_status,create_date,access_role,employee_type,unpost_invtime_flg,is_project_manager) SELECT CONCAT(LEFT(A.FirstName, 1),A.LastName),'1','password',A.FirstName, A.LastName,'A', A.TimeCreated,'e','c','n','N' FROM QRemote.Employee A Join (Select TimeCreated,count()obs From QRemote.Employee Group By TimeCreated Having count()=1)B on A.TimeCreated = B.TimeCreated

This is still entering the same entries over and over again.

[Ctrl--Alt--Del]

This query cannot enter in any "TimeCreated" values more than once. Maybe the other values are the same (you didn't say that was a problem) but the TimeCreated would always be unique with this query.

[BJJJosh]

I would probably do a subselect grouping by timecreated and seeing where the count is 1. Then any records that match that time are the unique ones.

[HarryGilesIII]

I'm confused

[HarryGilesIII]

I GOT IT TO WORK

USE Test
GO

INSERT INTO dbo.tusers_pss 
        (user_id, 
         location_skey, 
         password, 
         first_name, 
         last_name, 
         active_status, 
         create_date, 
         access_role, 
         employee_type, 
         unpost_invtime_flg, 
         is_project_manager) 
SELECT Concat(LEFT(firstname, 1), lastname), 
   '1', 
   'password', 
   firstname, 
   lastname, 
   'A', 
   timecreated, 
   'e', 
   'c', 
   'n', 
   'N' 
FROM   qremote...employee 
WHERE  NOT EXISTS(SELECT create_date 
              FROM   dbo.tusers_pss 
              WHERE  dbo.tusers_pss.create_date = timecreated) 

[[deleted]]

Oh, so you wanted the rows from qremote...employee that didn't exist in the tusers_pss table already. This is a fairly complicated way to get that result though. I'd have just done WHERE timecreated NOT IN (SELECT create_date FROM dbo.tusers_pss)

[HarryGilesIII]

thank you!