How would I go about this ...checking if data exists in a table

Question

How would I go about this ...checking if data exists in a table

gothaimviii

SSC-Addicted

Points: 404
More actions
July 16, 2018 at 9:08 am

#373586

So I am working within a stored procedure that is loading data into a blank table from table 1. There is one field called "EmpFc" in the blank table that is either going to be an 'A' or 'C' depending on what is found. Seeing if table 1 SSN = table 2 SSN (PROD data).
The default is to always be an add "A" in that blank table field, unless the EmpFicaNbr (SSN) is found existing already in another table it is comparing to.
I have the statement wrote as this, but it just produces an "A" for that field no matter what.
case when exists(select FICA_NBR from PROD.DB.TABLE2
    where FICA_NBR = dbo.TABLE1.EmpFicaNbr)
    then 'C'
    else 'A' end + ',' +
Basically, I am wanting it to put a C in that field if it sees that the SSN already exists in the table it is comparing to. If it does not, it accepts whats already in there.

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

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

sgmunson SSC Guru Points: 110639 More actions · Answer 1

gothaimviii - Monday, July 16, 2018 9:08 AM
So I am working within a stored procedure that is loading data into a blank table from table 1. There is one field called "EmpFc" in the blank table that is either going to be an 'A' or 'C' depending on what is found. Seeing if table 1 SSN = table 2 SSN (PROD data).
The default is to always be an add "A" in that blank table field, unless the EmpFicaNbr (SSN) is found existing already in another table it is comparing to.
I have the statement wrote as this, but it just produces an "A" for that field no matter what.
case when exists(select FICA_NBR from PROD.DB.TABLE2
    where FICA_NBR = dbo.TABLE1.EmpFicaNbr)
    then 'C'
    else 'A' end + ',' +
Basically, I am wanting it to put a C in that field if it sees that the SSN already exists in the table it is comparing to. If it does not, it accepts whats already in there.

Need at least the rest of that query to be able to see what might be going on. it would be best to provide table create statements for all the tables referenced in the query, along with SAMPLE data and the expected results based on that sample data. No real SSNs, please. Problem could be anything from an issue with how a column is being referenced to a genuine data issue.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

gothaimviii SSC-Addicted Points: 404 More actions · Answer 2

this is actually the SP...

you can see its taking data from a table, and inputting data into another table based on situations.

Just basically manipulating the data that is going into the new table, its going to be used to produce a CSV file.

USE [InterfaceDEV];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[iCIMS_HIRES_SP] AS

----delete from dbo.iCIMS_HIRES_OUT

INSERT INTO dbo.iCIMS_HIRES_OUT
SELECT '1' as SORT_ORDER,
        'EmpFc,EmpCompany,EmpEmployee,EmpLastName,EmpFirstName,EmpMiddleName,EmpFicaNbr,EmpEmpStatus,' +
        'EmpDateHired,EmpAdjHireDate,EmpProcessLevel,EmpJobCode,PemLocatCode,EmpUnionCode,EmpSalaryClass,' +
        'EmpPayFrequency,EmpPayRate,EmpAnnualHours,EmpNbrFte,EmpOtPlanCode,EmpAutoDeposit,EmpHmDistCo,' +
        'EmpHmAcctUnit,EmpHmAccount,PemBirthdate,PemTrueMarStat,PemSex,PemEeoClass,EmpAddr1,EmpCity,EmpState,' +
        'EmpZip,EmpCounty,PemHmPhoneNbr,PemBenDateC,PemSmoker,PemClockNbr,PepPosLevel,PepEffectDate' as F1 -- HEADER
UNION
SELECT '2' as SORT_ORDER,
        case when exists(select * from PROD.EMPLOYEE
    where FICA_NBR = dbo.iCIMS_HIRES.EmpFicaNbr)
    then 'C'
    else replace(upper (rtrim(EmpFc)), '"', '') end + ',' +
        replace(rtrim(EmpCompany), '"', '') + ',' +
        replace(rtrim(EmpEmployee), '"', '') + ',' +
        replace(upper(rtrim(EmpLastName)), '"', '') + ',' +
        replace(upper(rtrim(EmpFirstName)), '"', '') + ',' +
        replace(upper(rtrim(EmpMiddleName)), '"', '') + ',' +
        replace(rtrim(EmpFicaNbr), '"', '') + ',' +
        replace(upper(rtrim(EmpEmpStatus)), '"', '') + ',' +
        replace(rtrim(EmpDateHired), '"', '') + ',' +
        replace(rtrim(EmpAdjHireDate), '"', '') + ',' +
--        rtrim(EmpProcessLevel) + ',' +
case when replace(rtrim(EmpProcessLevel), '"', '') = '001IL'
    then '001'
    else replace(rtrim(EmpProcessLevel), '"', '') end + ',' +
        replace(rtrim(EmpJobCode), '"', '') + ',' +
        replace(rtrim(PemLocatCode), '"', '') + ',' +
        replace(rtrim(EmpUnionCode), '"', '') + ',' +
        replace(rtrim(EmpSalaryClass), '"', '') + ',' +
        replace(rtrim(EmpPayFrequency), '"', '') + ',' +
        replace(rtrim(EmpPayRate), '"', '') + ',' +
        replace(rtrim(EmpAnnualHours), '"', '') + ',' +
        replace(rtrim(EmpNbrFte), '"', '') + ',' +
        replace(rtrim(EmpOtPlanCode), '"', '') + ',' +
        replace(upper(rtrim(EmpAutoDeposit)), '"', '') + ',' +
--        replace(rtrim(EmpHmDistCo), '"', '') + ',' +
case when replace(rtrim(EmpHmDistCo), '"', '') = '001IL' --or
--    replace(rtrim(EmpHmDistCo), '"', '') = '001'
    then '1'
    else replace(rtrim(EmpHmDistCo), '"', '') end + ',' +
--        replace(rtrim(EmpHmAcctUnit), '"', '') + ',' +
case when replace(rtrim(EmpHmAcctUnit), '"', '') = '001IL' or
     replace(rtrim(EmpHmAcctUnit), '"', '') = '001'
    then '000001'
    else replace(rtrim(EmpHmAcctUnit), '"', '') end + ',' +
        replace(rtrim(EmpHmAccount), '"', '') + ',' +
        replace(rtrim(PemBirthdate), '"', '') + ',' +
        replace(upper(rtrim(PemTrueMarStat)), '"', '') + ',' +
        replace(upper(rtrim(PemSex)), '"', '') + ',' +
        replace(upper(rtrim(PemEeoClass)), '"', '') + ',' +
        replace(upper(rtrim(EmpAddr1)), '"', '') + ',' +
        replace(upper(rtrim(EmpCity)), '"', '') + ',' +
        replace(upper(rtrim(EmpState)), '"', '') + ',' +
        replace(rtrim(EmpZip), '"', '') + ',' +
        replace(upper(rtrim(EmpCounty)), '"', '') + ',' +
        replace(rtrim(PemHmPhoneNbr), '"', '') + ',' +
        replace(rtrim(PemBenDateC), '"', '') + ',' +
        replace(upper(rtrim(PemSmoker)), '"', '') + ',' +
        replace(rtrim(PemClockNbr), '"', '') + ',' +
        replace(rtrim(PepPosLevel), '"', '') + ',' +
        replace(rtrim(PepEffectDate), '"', '')
        as F1
        from iCIMS_HIRES

        select F1 from dbo.iCIMS_HIRES_OUT
order by SORT_ORDER
GO

sgmunson SSC Guru Points: 110639 More actions · Answer 3

gothaimviii - Monday, July 16, 2018 9:22 AM
this is actually the SP...
you can see its taking data from a table, and inputting data into another table based on situations.
Just basically manipulating the data that is going into the new table, its going to be used to produce a CSV file.
USE [InterfaceDEV];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[iCIMS_HIRES_SP] AS
----delete from dbo.iCIMS_HIRES_OUT
INSERT INTO dbo.iCIMS_HIRES_OUT
SELECT '1' as SORT_ORDER,
        'EmpFc,EmpCompany,EmpEmployee,EmpLastName,EmpFirstName,EmpMiddleName,EmpFicaNbr,EmpEmpStatus,' +
        'EmpDateHired,EmpAdjHireDate,EmpProcessLevel,EmpJobCode,PemLocatCode,EmpUnionCode,EmpSalaryClass,' +
        'EmpPayFrequency,EmpPayRate,EmpAnnualHours,EmpNbrFte,EmpOtPlanCode,EmpAutoDeposit,EmpHmDistCo,' +
        'EmpHmAcctUnit,EmpHmAccount,PemBirthdate,PemTrueMarStat,PemSex,PemEeoClass,EmpAddr1,EmpCity,EmpState,' +
        'EmpZip,EmpCounty,PemHmPhoneNbr,PemBenDateC,PemSmoker,PemClockNbr,PepPosLevel,PepEffectDate' as F1 -- HEADER
UNION
SELECT '2' as SORT_ORDER,
        case when exists(select * from PROD.EMPLOYEE
    where FICA_NBR = dbo.iCIMS_HIRES.EmpFicaNbr)
    then 'C'
    else replace(upper (rtrim(EmpFc)), '"', '') end + ',' +
        replace(rtrim(EmpCompany), '"', '') + ',' +
        replace(rtrim(EmpEmployee), '"', '') + ',' +
        replace(upper(rtrim(EmpLastName)), '"', '') + ',' +
        replace(upper(rtrim(EmpFirstName)), '"', '') + ',' +
        replace(upper(rtrim(EmpMiddleName)), '"', '') + ',' +
        replace(rtrim(EmpFicaNbr), '"', '') + ',' +
        replace(upper(rtrim(EmpEmpStatus)), '"', '') + ',' +
        replace(rtrim(EmpDateHired), '"', '') + ',' +
        replace(rtrim(EmpAdjHireDate), '"', '') + ',' +
--        rtrim(EmpProcessLevel) + ',' +
case when replace(rtrim(EmpProcessLevel), '"', '') = '001IL'
    then '001'
    else replace(rtrim(EmpProcessLevel), '"', '') end + ',' +
        replace(rtrim(EmpJobCode), '"', '') + ',' +
        replace(rtrim(PemLocatCode), '"', '') + ',' +
        replace(rtrim(EmpUnionCode), '"', '') + ',' +
        replace(rtrim(EmpSalaryClass), '"', '') + ',' +
        replace(rtrim(EmpPayFrequency), '"', '') + ',' +
        replace(rtrim(EmpPayRate), '"', '') + ',' +
        replace(rtrim(EmpAnnualHours), '"', '') + ',' +
        replace(rtrim(EmpNbrFte), '"', '') + ',' +
        replace(rtrim(EmpOtPlanCode), '"', '') + ',' +
        replace(upper(rtrim(EmpAutoDeposit)), '"', '') + ',' +
--        replace(rtrim(EmpHmDistCo), '"', '') + ',' +
case when replace(rtrim(EmpHmDistCo), '"', '') = '001IL' --or
--    replace(rtrim(EmpHmDistCo), '"', '') = '001'
    then '1'
    else replace(rtrim(EmpHmDistCo), '"', '') end + ',' +
--        replace(rtrim(EmpHmAcctUnit), '"', '') + ',' +
case when replace(rtrim(EmpHmAcctUnit), '"', '') = '001IL' or
     replace(rtrim(EmpHmAcctUnit), '"', '') = '001'
    then '000001'
    else replace(rtrim(EmpHmAcctUnit), '"', '') end + ',' +
        replace(rtrim(EmpHmAccount), '"', '') + ',' +
        replace(rtrim(PemBirthdate), '"', '') + ',' +
        replace(upper(rtrim(PemTrueMarStat)), '"', '') + ',' +
        replace(upper(rtrim(PemSex)), '"', '') + ',' +
        replace(upper(rtrim(PemEeoClass)), '"', '') + ',' +
        replace(upper(rtrim(EmpAddr1)), '"', '') + ',' +
        replace(upper(rtrim(EmpCity)), '"', '') + ',' +
        replace(upper(rtrim(EmpState)), '"', '') + ',' +
        replace(rtrim(EmpZip), '"', '') + ',' +
        replace(upper(rtrim(EmpCounty)), '"', '') + ',' +
        replace(rtrim(PemHmPhoneNbr), '"', '') + ',' +
        replace(rtrim(PemBenDateC), '"', '') + ',' +
        replace(upper(rtrim(PemSmoker)), '"', '') + ',' +
        replace(rtrim(PemClockNbr), '"', '') + ',' +
        replace(rtrim(PepPosLevel), '"', '') + ',' +
        replace(rtrim(PepEffectDate), '"', '')
        as F1
        from iCIMS_HIRES

        select F1 from dbo.iCIMS_HIRES_OUT
order by SORT_ORDER
GO

Let's try giving your primary source table an alias of "H", and then applying that alias everywhere. Try this on for size:
USE InterfaceDEV; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO

ALTER PROCEDURE dbo.iCIMS_HIRES_SP AS

--DELETE FROM dbo.iCIMS_HIRES_OUT INSERT INTO dbo.iCIMS_HIRES_OUT (F1) SELECT '1' AS SORT_ORDER, 'EmpFc,EmpCompany,EmpEmployee,EmpLastName,EmpFirstName,EmpMiddleName,EmpFicaNbr,EmpEmpStatus,' + 'EmpDateHired,EmpAdjHireDate,EmpProcessLevel,EmpJobCode,PemLocatCode,EmpUnionCode,EmpSalaryClass,' + 'EmpPayFrequency,EmpPayRate,EmpAnnualHours,EmpNbrFte,EmpOtPlanCode,EmpAutoDeposit,EmpHmDistCo,' + 'EmpHmAcctUnit,EmpHmAccount,PemBirthdate,PemTrueMarStat,PemSex,PemEeoClass,EmpAddr1,EmpCity,EmpState,' + 'EmpZip,EmpCounty,PemHmPhoneNbr,PemBenDateC,PemSmoker,PemClockNbr,PepPosLevel,PepEffectDate' AS F1 -- HEADER UNION SELECT '2' as SORT_ORDER, CASE WHEN EXISTS ( SELECT 1 FROM PROD.EMPLOYEE AS E WHERE E.FICA_NBR = H.EmpFicaNbr ) THEN 'C' ELSE REPLACE(UPPER(RTRIM(H.EmpFc)), '"', '') END + ',' + REPLACE(RTRIM(H.EmpCompany), '"', '') + ',' + REPLACE(RTRIM(H.EmpEmployee), '"', '') + ',' + REPLACE(UPPER(RTRIM(H.EmpLastName)), '"', '') + ',' + REPLACE(UPPER(RTRIM(H.EmpFirstName)), '"', '') + ',' + REPLACE(UPPER(RTRIM(H.EmpMiddleName)), '"', '') + ',' + REPLACE(RTRIM(H.EmpFicaNbr), '"', '') + ',' + REPLACE(UPPER(RTRIM(H.EmpEmpStatus)), '"', '') + ',' + REPLACE(RTRIM(H.EmpDateHired), '"', '') + ',' + REPLACE(RTRIM(H.EmpAdjHireDate), '"', '') + ',' + -- RTRIM(H.EmpProcessLevel) + ',' + CASE WHEN REPLACE(RTRIM(H.EmpProcessLevel), '"', '') = '001IL' THEN '001' ELSE REPLACE(RTRIM(H.EmpProcessLevel), '"', '') END + ',' + REPLACE(RTRIM(H.EmpJobCode), '"', '') + ',' + REPLACE(RTRIM(H.PemLocatCode), '"', '') + ',' + REPLACE(RTRIM(H.EmpUnionCode), '"', '') + ',' + REPLACE(RTRIM(H.EmpSalaryClass), '"', '') + ',' + REPLACE(RTRIM(H.EmpPayFrequency), '"', '') + ',' + REPLACE(RTRIM(H.EmpPayRate), '"', '') + ',' + REPLACE(RTRIM(H.EmpAnnualHours), '"', '') + ',' + REPLACE(RTRIM(H.EmpNbrFte), '"', '') + ',' + REPLACE(RTRIM(H.EmpOtPlanCode), '"', '') + ',' + REPLACE(UPPER(RTRIM(H.EmpAutoDeposit)), '"', '') + ',' + -- REPLACE(RTRIM(H.EmpHmDistCo), '"', '') + ',' + CASE WHEN REPLACE(RTRIM(H.EmpHmDistCo), '"', '') = '001IL' THEN '1' -- or REPLACE(RTRIM(H.EmpHmDistCo), '"', '') = '001' ELSE REPLACE(RTRIM(H.EmpHmDistCo), '"', '') END + ',' + -- REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '') + ',' + CASE WHEN REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '') = '001IL' OR REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '') = '001' THEN '000001' ELSE REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '') END + ',' + REPLACE(RTRIM(H.EmpHmAccount), '"', '') + ',' + REPLACE(RTRIM(H.PemBirthdate), '"', '') + ',' + REPLACE(UPPER(RTRIM(H.PemTrueMarStat)), '"', '') + ',' + REPLACE(UPPER(RTRIM(H.PemSex)), '"', '') + ',' + REPLACE(UPPER(RTRIM(H.PemEeoClass)), '"', '') + ',' + REPLACE(UPPER(RTRIM(H.EmpAddr1)), '"', '') + ',' + REPLACE(UPPER(RTRIM(H.EmpCity)), '"', '') + ',' + REPLACE(UPPER(RTRIM(H.EmpState)), '"', '') + ',' + REPLACE(RTRIM(H.EmpZip), '"', '') + ',' + REPLACE(UPPER(RTRIM(H.EmpCounty)), '"', '') + ',' + REPLACE(RTRIM(H.PemHmPhoneNbr), '"', '') + ',' + REPLACE(RTRIM(H.PemBenDateC), '"', '') + ',' + REPLACE(UPPER(RTRIM(H.PemSmoker)), '"', '') + ',' + REPLACE(RTRIM(H.PemClockNbr), '"', '') + ',' + REPLACE(RTRIM(H.PepPosLevel), '"', '') + ',' + REPLACE(RTRIM(H.PepEffectDate), '"', '') AS F1 FROM iCIMS_HIRES AS H;

SELECT HO.F1 FROM dbo.iCIMS_HIRES_OUT AS HO ORDER BY HO.SORT_ORDER; GO

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

gothaimviii SSC-Addicted Points: 404 More actions · Answer 4

sgmunson - Monday, July 16, 2018 9:40 AM
gothaimviii - Monday, July 16, 2018 9:22 AM
this is actually the SP...
you can see its taking data from a table, and inputting data into another table based on situations.
Just basically manipulating the data that is going into the new table, its going to be used to produce a CSV file.
USE [InterfaceDEV];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[iCIMS_HIRES_SP] AS
----delete from dbo.iCIMS_HIRES_OUT
INSERT INTO dbo.iCIMS_HIRES_OUT
SELECT '1' as SORT_ORDER,
        'EmpFc,EmpCompany,EmpEmployee,EmpLastName,EmpFirstName,EmpMiddleName,EmpFicaNbr,EmpEmpStatus,' +
        'EmpDateHired,EmpAdjHireDate,EmpProcessLevel,EmpJobCode,PemLocatCode,EmpUnionCode,EmpSalaryClass,' +
        'EmpPayFrequency,EmpPayRate,EmpAnnualHours,EmpNbrFte,EmpOtPlanCode,EmpAutoDeposit,EmpHmDistCo,' +
        'EmpHmAcctUnit,EmpHmAccount,PemBirthdate,PemTrueMarStat,PemSex,PemEeoClass,EmpAddr1,EmpCity,EmpState,' +
        'EmpZip,EmpCounty,PemHmPhoneNbr,PemBenDateC,PemSmoker,PemClockNbr,PepPosLevel,PepEffectDate' as F1 -- HEADER
UNION
SELECT '2' as SORT_ORDER,
        case when exists(select * from PROD.EMPLOYEE
    where FICA_NBR = dbo.iCIMS_HIRES.EmpFicaNbr)
    then 'C'
    else replace(upper (rtrim(EmpFc)), '"', '') end + ',' +
        replace(rtrim(EmpCompany), '"', '') + ',' +
        replace(rtrim(EmpEmployee), '"', '') + ',' +
        replace(upper(rtrim(EmpLastName)), '"', '') + ',' +
        replace(upper(rtrim(EmpFirstName)), '"', '') + ',' +
        replace(upper(rtrim(EmpMiddleName)), '"', '') + ',' +
        replace(rtrim(EmpFicaNbr), '"', '') + ',' +
        replace(upper(rtrim(EmpEmpStatus)), '"', '') + ',' +
        replace(rtrim(EmpDateHired), '"', '') + ',' +
        replace(rtrim(EmpAdjHireDate), '"', '') + ',' +
--        rtrim(EmpProcessLevel) + ',' +
case when replace(rtrim(EmpProcessLevel), '"', '') = '001IL'
    then '001'
    else replace(rtrim(EmpProcessLevel), '"', '') end + ',' +
        replace(rtrim(EmpJobCode), '"', '') + ',' +
        replace(rtrim(PemLocatCode), '"', '') + ',' +
        replace(rtrim(EmpUnionCode), '"', '') + ',' +
        replace(rtrim(EmpSalaryClass), '"', '') + ',' +
        replace(rtrim(EmpPayFrequency), '"', '') + ',' +
        replace(rtrim(EmpPayRate), '"', '') + ',' +
        replace(rtrim(EmpAnnualHours), '"', '') + ',' +
        replace(rtrim(EmpNbrFte), '"', '') + ',' +
        replace(rtrim(EmpOtPlanCode), '"', '') + ',' +
        replace(upper(rtrim(EmpAutoDeposit)), '"', '') + ',' +
--        replace(rtrim(EmpHmDistCo), '"', '') + ',' +
case when replace(rtrim(EmpHmDistCo), '"', '') = '001IL' --or
--    replace(rtrim(EmpHmDistCo), '"', '') = '001'
    then '1'
    else replace(rtrim(EmpHmDistCo), '"', '') end + ',' +
--        replace(rtrim(EmpHmAcctUnit), '"', '') + ',' +
case when replace(rtrim(EmpHmAcctUnit), '"', '') = '001IL' or
     replace(rtrim(EmpHmAcctUnit), '"', '') = '001'
    then '000001'
    else replace(rtrim(EmpHmAcctUnit), '"', '') end + ',' +
        replace(rtrim(EmpHmAccount), '"', '') + ',' +
        replace(rtrim(PemBirthdate), '"', '') + ',' +
        replace(upper(rtrim(PemTrueMarStat)), '"', '') + ',' +
        replace(upper(rtrim(PemSex)), '"', '') + ',' +
        replace(upper(rtrim(PemEeoClass)), '"', '') + ',' +
        replace(upper(rtrim(EmpAddr1)), '"', '') + ',' +
        replace(upper(rtrim(EmpCity)), '"', '') + ',' +
        replace(upper(rtrim(EmpState)), '"', '') + ',' +
        replace(rtrim(EmpZip), '"', '') + ',' +
        replace(upper(rtrim(EmpCounty)), '"', '') + ',' +
        replace(rtrim(PemHmPhoneNbr), '"', '') + ',' +
        replace(rtrim(PemBenDateC), '"', '') + ',' +
        replace(upper(rtrim(PemSmoker)), '"', '') + ',' +
        replace(rtrim(PemClockNbr), '"', '') + ',' +
        replace(rtrim(PepPosLevel), '"', '') + ',' +
        replace(rtrim(PepEffectDate), '"', '')
        as F1
        from iCIMS_HIRES

        select F1 from dbo.iCIMS_HIRES_OUT
order by SORT_ORDER
GO
Let's try giving your primary source table an alias of "H", and then applying that alias everywhere. Try this on for size:
USE InterfaceDEV; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO
ALTER PROCEDURE dbo.iCIMS_HIRES_SP AS
--DELETE FROM dbo.iCIMS_HIRES_OUT INSERT INTO dbo.iCIMS_HIRES_OUT (F1) SELECT '1' AS SORT_ORDER, 'EmpFc,EmpCompany,EmpEmployee,EmpLastName,EmpFirstName,EmpMiddleName,EmpFicaNbr,EmpEmpStatus,' + 'EmpDateHired,EmpAdjHireDate,EmpProcessLevel,EmpJobCode,PemLocatCode,EmpUnionCode,EmpSalaryClass,' + 'EmpPayFrequency,EmpPayRate,EmpAnnualHours,EmpNbrFte,EmpOtPlanCode,EmpAutoDeposit,EmpHmDistCo,' + 'EmpHmAcctUnit,EmpHmAccount,PemBirthdate,PemTrueMarStat,PemSex,PemEeoClass,EmpAddr1,EmpCity,EmpState,' + 'EmpZip,EmpCounty,PemHmPhoneNbr,PemBenDateC,PemSmoker,PemClockNbr,PepPosLevel,PepEffectDate' AS F1 -- HEADER UNION SELECT '2' as SORT_ORDER,     CASE         WHEN EXISTS (             SELECT 1             FROM PROD.EMPLOYEE AS E             WHERE E.FICA_NBR = H.EmpFicaNbr             ) THEN 'C'         ELSE REPLACE(UPPER(RTRIM(H.EmpFc)), '"', '')     END + ',' +     REPLACE(RTRIM(H.EmpCompany), '"', '') + ',' +     REPLACE(RTRIM(H.EmpEmployee), '"', '') + ',' +     REPLACE(UPPER(RTRIM(H.EmpLastName)), '"', '') + ',' +     REPLACE(UPPER(RTRIM(H.EmpFirstName)), '"', '') + ',' +     REPLACE(UPPER(RTRIM(H.EmpMiddleName)), '"', '') + ',' +     REPLACE(RTRIM(H.EmpFicaNbr), '"', '') + ',' +     REPLACE(UPPER(RTRIM(H.EmpEmpStatus)), '"', '') + ',' +     REPLACE(RTRIM(H.EmpDateHired), '"', '') + ',' +     REPLACE(RTRIM(H.EmpAdjHireDate), '"', '') + ',' +     -- RTRIM(H.EmpProcessLevel) + ',' +     CASE         WHEN REPLACE(RTRIM(H.EmpProcessLevel), '"', '') = '001IL' THEN '001'         ELSE REPLACE(RTRIM(H.EmpProcessLevel), '"', '')     END + ',' +     REPLACE(RTRIM(H.EmpJobCode), '"', '') + ',' +     REPLACE(RTRIM(H.PemLocatCode), '"', '') + ',' +     REPLACE(RTRIM(H.EmpUnionCode), '"', '') + ',' +     REPLACE(RTRIM(H.EmpSalaryClass), '"', '') + ',' +     REPLACE(RTRIM(H.EmpPayFrequency), '"', '') + ',' +     REPLACE(RTRIM(H.EmpPayRate), '"', '') + ',' +     REPLACE(RTRIM(H.EmpAnnualHours), '"', '') + ',' +     REPLACE(RTRIM(H.EmpNbrFte), '"', '') + ',' +     REPLACE(RTRIM(H.EmpOtPlanCode), '"', '') + ',' +     REPLACE(UPPER(RTRIM(H.EmpAutoDeposit)), '"', '') + ',' +     -- REPLACE(RTRIM(H.EmpHmDistCo), '"', '') + ',' +     CASE         WHEN REPLACE(RTRIM(H.EmpHmDistCo), '"', '') = '001IL' THEN '1'         --    or REPLACE(RTRIM(H.EmpHmDistCo), '"', '') = '001'         ELSE REPLACE(RTRIM(H.EmpHmDistCo), '"', '')     END + ',' +     -- REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '') + ',' +     CASE         WHEN REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '') = '001IL' OR             REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '') = '001' THEN '000001'         ELSE REPLACE(RTRIM(H.EmpHmAcctUnit), '"', '')     END + ',' +     REPLACE(RTRIM(H.EmpHmAccount), '"', '') + ',' +     REPLACE(RTRIM(H.PemBirthdate), '"', '') + ',' +     REPLACE(UPPER(RTRIM(H.PemTrueMarStat)), '"', '') + ',' +     REPLACE(UPPER(RTRIM(H.PemSex)), '"', '') + ',' +     REPLACE(UPPER(RTRIM(H.PemEeoClass)), '"', '') + ',' +     REPLACE(UPPER(RTRIM(H.EmpAddr1)), '"', '') + ',' +     REPLACE(UPPER(RTRIM(H.EmpCity)), '"', '') + ',' +     REPLACE(UPPER(RTRIM(H.EmpState)), '"', '') + ',' +     REPLACE(RTRIM(H.EmpZip), '"', '') + ',' +     REPLACE(UPPER(RTRIM(H.EmpCounty)), '"', '') + ',' +     REPLACE(RTRIM(H.PemHmPhoneNbr), '"', '') + ',' +     REPLACE(RTRIM(H.PemBenDateC), '"', '') + ',' +     REPLACE(UPPER(RTRIM(H.PemSmoker)), '"', '') + ',' +     REPLACE(RTRIM(H.PemClockNbr), '"', '') + ',' +     REPLACE(RTRIM(H.PepPosLevel), '"', '') + ',' +     REPLACE(RTRIM(H.PepEffectDate), '"', '') AS F1 FROM iCIMS_HIRES AS H;
SELECT HO.F1 FROM dbo.iCIMS_HIRES_OUT AS HO ORDER BY HO.SORT_ORDER; GO

I tried this as stated. when I leave the (F1) on the insert statement, i get an error of Lookup Error - SQL Server Database Error: The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

gothaimviii SSC-Addicted Points: 404 More actions · Answer 5

i figured it out...sort order was not defined in the insert statement.

Anyway, I tried the whole alias thing and your case statement, and I still get A as the value even though it should be a C for the field I am testing.

gothaimviii SSC-Addicted Points: 404 More actions · Answer 6

I think I see what is going on. The table I am comparing to does not have "" in each field.

The table I am pulling data from has "" in each field. (hence the reason I am removing the "" in each field I am inserting into the new table)

Jeff Moden SSC Guru Points: 1003889 More actions · Answer 7

gothaimviii - Monday, July 16, 2018 10:14 AM
I think I see what is going on. The table I am comparing to does not have "" in each field.
The table I am pulling data from has "" in each field. (hence the reason I am removing the "" in each field I am inserting into the new table)

Heh... cool. Now all you have to do is to ...
1. Stop transmitting/receiving SSNs in clear text files.
2. Stop storing SSNs in clear text tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)