Inserting into Temp tables

  • I have a query that is not optimized or the best way to perform my task. This was done by a person who was here before me. I have tried to build on it but have been away from SQL for a while now. What I am trying to do is get data for an alternative ID field from three different fields (SIN #, Health Card Number, and File #) from a people table. This alternative ID field will be migrated to our new database. I hope I am making sense. This is why I need to write three different queries to get one field populated through a union query.

    Below is what I have and I am having trouble using the IDENTITY function while trying to insert into an existing table. I have made in bold and the error code I am getting.

    Below is my script.

    --PART_160 - Alternative ID

    -- Social Insurance number

    --Temporary Registered Person Table

    SELECT DISTINCT p.PersonID

    INTO #temp_p

    FROM FamilyMembers fm

    INNER JOIN Families f ON fm.FamilyID = f.FamilyID

    INNER JOIN Cases c ON f.FamilyID = c.FamilyID

    INNER JOIN Departments d ON c.DepartmentID = d.ImmutableID

    LEFT OUTER JOIN People p ON fm.PersonID = p.PersonID

    WHERE p.FirstName NOT LIKE '%?%' AND p.LastName NOT LIKE '%?%'

    AND p.FirstName NOT LIKE '%Unknown%' AND p.LastName NOT LIKE '%Unknown%'

    AND p.FirstName NOT LIKE '%Not Indicated%' AND p.LastName NOT LIKE '%Not Indicated%'

    AND p.FirstName NOT LIKE '%No first%'

    AND p.FirstName NOT LIKE '%Anony%' AND p.LastName NOT LIKE '%Anony%'

    AND d.DepartmentName NOT LIKE 'CFT%' AND d.DepartmentName NOT LIKE 'CMH%' AND d.DepartmentName NOT LIKE 'GROUP%' AND d.DepartmentName NOT LIKE 'ISW%' AND d.DepartmentName NOT LIKE 'ISC%' AND d.DepartmentName NOT LIKE 'AYM' AND d.DepartmentName NOT LIKE 'G.A.P.' AND p.PersonID IS NOT NULL

    SELECT DISTINCT 'Participant_Alternate_ID||' AS 'Entity Name||',

    'SMCY||' AS 'CAS Site Identifier||',

    'Matrix||' AS 'CAS System Identifier||',

    'SMCY-Alternate ID-' + CAST(tp.PersonID AS varchar) + '||' AS 'Legacy Alternate ID||',

    'SMCY-Alternate ID-' + CAST(tp.PersonID AS varchar) + '||' AS 'Alternate ID||',

    '||SMCY' + CAST (tp.PersonID AS varchar) + '||' AS 'Participant ID||',

    --CASE WHEN p.Sin IS NULL OR p.Sin = '' THEN '||' ELSE p.Sin + '||' END AS 'Document Number||',

    'MC_CA019' AS 'Type CD||',

    CONVERT (VARCHAR,getdate(), 103) + ' ' + CONVERT(VARCHAR, DATEPART(hh, getdate())) + ':' + RIGHT(CONVERT(VARCHAR, DATEPART(mi, getdate())), 2) + '||' AS 'Start Date||',

    '||' AS 'End Date||',

    '||' AS 'Comments||',

    '||' AS 'Create Date||',

    '||' AS 'Create User||',

    '||' AS 'Last Update Date||',

    '||' AS 'Last Update User||',

    p.sin AS 'Document Number'

    INTO #temp

    FROM #temp_p tp

    LEFT OUTER JOIN People p ON tp.PersonID = p.PersonID

    LEFT OUTER JOIN Families f ON tp.PersonID = f.PersonID

    LEFT OUTER JOIN Cases c ON f.FamilyID = c.FamilyID

    where p.sin > ''

    SELECT IDENTITY(int, 1,1) AS 'RN', #temp.[Participant ID||] AS 'tp' INTO #temp_2

    FROM #temp

    SELECT CAST(t2.RN AS varchar) + '||' AS 'Record Number||',

    t.* FROM #temp_2 t2

    LEFT OUTER JOIN #temp t ON t2.[tp] = t.[Participant ID||]

    --DROP TABLE #temp

    --DROP TABLE #temp_2

    --DROP TABLE #temp_p

    ---------------------------------

    --PART_160 - ChildFileNumber

    -- Mapping this as Other.

    --Temporary Registered Person Table

    Insert INTO #temp_p

    SELECT DISTINCT p.PersonID

    FROM FamilyMembers fm

    INNER JOIN Families f ON fm.FamilyID = f.FamilyID

    INNER JOIN Cases c ON f.FamilyID = c.FamilyID

    INNER JOIN Departments d ON c.DepartmentID = d.ImmutableID

    LEFT OUTER JOIN People p ON fm.PersonID = p.PersonID

    WHERE p.FirstName NOT LIKE '%?%' AND p.LastName NOT LIKE '%?%'

    AND p.FirstName NOT LIKE '%Unknown%' AND p.LastName NOT LIKE '%Unknown%'

    AND p.FirstName NOT LIKE '%Not Indicated%' AND p.LastName NOT LIKE '%Not Indicated%'

    AND p.FirstName NOT LIKE '%No first%'

    AND p.FirstName NOT LIKE '%Anony%' AND p.LastName NOT LIKE '%Anony%'

    AND d.DepartmentName NOT LIKE 'CFT%' AND d.DepartmentName NOT LIKE 'CMH%' AND d.DepartmentName NOT LIKE 'GROUP%' AND d.DepartmentName NOT LIKE 'ISW%' AND d.DepartmentName NOT LIKE 'ISC%' AND d.DepartmentName NOT LIKE 'AYM' AND d.DepartmentName NOT LIKE 'G.A.P.' AND p.PersonID IS NOT NULL

    Insert INTO #temp

    SELECT DISTINCT 'Participant_Alternate_ID||' AS 'Entity Name||',

    'SMCY||' AS 'CAS Site Identifier||',

    'Matrix||' AS 'CAS System Identifier||',

    'SMCY-Alternate ID-' + CAST(tp.PersonID AS varchar) + '||' AS 'Legacy Alternate ID||',

    'SMCY-Alternate ID-' + CAST(tp.PersonID AS varchar) + '||' AS 'Alternate ID||',

    '||SMCY' + CAST (tp.PersonID AS varchar) + '||' AS 'Participant ID||',

    --CASE WHEN p.ChildFileNumber IS NULL OR p.ChildFileNumber = '' THEN '||' ELSE p.ChildFileNumber + '||' END AS 'Document Number||',

    'MC_CA024' AS 'Type CD||',

    CONVERT (VARCHAR,getdate(), 103) + ' ' + CONVERT(VARCHAR, DATEPART(hh, getdate())) + ':' + RIGHT(CONVERT(VARCHAR, DATEPART(mi, getdate())), 2) + '||' AS 'Start Date||',

    '||' AS 'End Date||',

    'Child File Number' AS 'Comments||',

    '||' AS 'Create Date||',

    '||' AS 'Create User||',

    '||' AS 'Last Update Date||',

    '||' AS 'Last Update User||',

    p.ChildFileNumber AS 'Document Number'

    FROM #temp_p tp

    LEFT OUTER JOIN People p ON tp.PersonID = p.PersonID

    LEFT OUTER JOIN Families f ON tp.PersonID = f.PersonID

    LEFT OUTER JOIN Cases c ON f.FamilyID = c.FamilyID

    where p.ChildFileNumber > ''

    SELECT IDENTITY(int, 1,1) AS 'RN', #temp.[Participant ID||] AS 'tp' INTO #temp_2

    FROM #temp -- There is already an object named '#temp_2' in the database.

    SELECT CAST(t2.RN AS varchar) + '||' AS 'Record Number||',

    t.* FROM #temp_2 t2

    LEFT OUTER JOIN #temp t ON t2.[tp] = t.[Participant ID||]

    --DROP TABLE #temp

    --DROP TABLE #temp_2

    --DROP TABLE #temp_p

    ------------------------------

    --PART_160 - Greenshield

    -- Mapping this as Other.

    --Temporary Registered Person Table

    Insert INTO #temp_p

    SELECT DISTINCT p.PersonID

    FROM FamilyMembers fm

    INNER JOIN Families f ON fm.FamilyID = f.FamilyID

    INNER JOIN Cases c ON f.FamilyID = c.FamilyID

    INNER JOIN Departments d ON c.DepartmentID = d.ImmutableID

    LEFT OUTER JOIN People p ON fm.PersonID = p.PersonID

    WHERE p.FirstName NOT LIKE '%?%' AND p.LastName NOT LIKE '%?%'

    AND p.FirstName NOT LIKE '%Unknown%' AND p.LastName NOT LIKE '%Unknown%'

    AND p.FirstName NOT LIKE '%Not Indicated%' AND p.LastName NOT LIKE '%Not Indicated%'

    AND p.FirstName NOT LIKE '%No first%'

    AND p.FirstName NOT LIKE '%Anony%' AND p.LastName NOT LIKE '%Anony%'

    AND d.DepartmentName NOT LIKE 'CFT%' AND d.DepartmentName NOT LIKE 'CMH%' AND d.DepartmentName NOT LIKE 'GROUP%' AND d.DepartmentName NOT LIKE 'ISW%' AND d.DepartmentName NOT LIKE 'ISC%' AND d.DepartmentName NOT LIKE 'AYM' AND d.DepartmentName NOT LIKE 'G.A.P.' AND p.PersonID IS NOT NULL

    Insert INTO #temp

    SELECT DISTINCT 'Participant_Alternate_ID||' AS 'Entity Name||',

    'SMCY||' AS 'CAS Site Identifier||',

    'Matrix||' AS 'CAS System Identifier||',

    'SMCY-Alternate ID-' + CAST(tp.PersonID AS varchar) + '||' AS 'Legacy Alternate ID||',

    'SMCY-Alternate ID-' + CAST(tp.PersonID AS varchar) + '||' AS 'Alternate ID||',

    '||SMCY' + CAST (tp.PersonID AS varchar) + '||' AS 'Participant ID||',

    --CASE WHEN p.ChildFileNumber IS NULL OR p.ChildFileNumber = '' THEN '||' ELSE p.ChildFileNumber + '||' END AS 'Document Number||',

    'MC_CA024' AS 'Type CD||',

    CONVERT (VARCHAR,getdate(), 103) + ' ' + CONVERT(VARCHAR, DATEPART(hh, getdate())) + ':' + RIGHT(CONVERT(VARCHAR, DATEPART(mi, getdate())), 2) + '||' AS 'Start Date||',

    '||' AS 'End Date||',

    '||' AS 'Comments||',

    '||' AS 'Create Date||',

    '||' AS 'Create User||',

    '||' AS 'Last Update Date||',

    '||' AS 'Last Update User||',

    'Green Shield' + ' ' + p.Greenshield AS 'Document Number'

    FROM #temp_p tp

    LEFT OUTER JOIN People p ON tp.PersonID = p.PersonID

    LEFT OUTER JOIN Families f ON tp.PersonID = f.PersonID

    LEFT OUTER JOIN Cases c ON f.FamilyID = c.FamilyID

    where p.Greenshield > ''

    SELECT IDENTITY(int, 1,1) AS 'RN', #temp.[Participant ID||] AS 'tp' INTO #temp_2

    FROM #temp --There is already an object named '#temp_2' in the database.

    SELECT CAST(t2.RN AS varchar) + '||' AS 'Record Number||',

    t.* FROM #temp_2 t2

    LEFT OUTER JOIN #temp t ON t2.[tp] = t.[Participant ID||]

    --DROP TABLE #temp

    --DROP TABLE #temp_2

    --DROP TABLE #temp_p

    ---------------------------------------

    --PART_160 - OHIP

    -- Mapping this as Other.

    --Temporary Registered Person Table

    Insert INTO #temp_p

    SELECT DISTINCT p.PersonID

    FROM FamilyMembers fm

    INNER JOIN Families f ON fm.FamilyID = f.FamilyID

    INNER JOIN Cases c ON f.FamilyID = c.FamilyID

    INNER JOIN Departments d ON c.DepartmentID = d.ImmutableID

    LEFT OUTER JOIN People p ON fm.PersonID = p.PersonID

    WHERE p.FirstName NOT LIKE '%?%' AND p.LastName NOT LIKE '%?%'

    AND p.FirstName NOT LIKE '%Unknown%' AND p.LastName NOT LIKE '%Unknown%'

    AND p.FirstName NOT LIKE '%Not Indicated%' AND p.LastName NOT LIKE '%Not Indicated%'

    AND p.FirstName NOT LIKE '%No first%'

    AND p.FirstName NOT LIKE '%Anony%' AND p.LastName NOT LIKE '%Anony%'

    AND d.DepartmentName NOT LIKE 'CFT%' AND d.DepartmentName NOT LIKE 'CMH%' AND d.DepartmentName NOT LIKE 'GROUP%' AND d.DepartmentName NOT LIKE 'ISW%' AND d.DepartmentName NOT LIKE 'ISC%' AND d.DepartmentName NOT LIKE 'AYM' AND d.DepartmentName NOT LIKE 'G.A.P.' AND p.PersonID IS NOT NULL

    Insert INTO #temp

    SELECT DISTINCT 'Participant_Alternate_ID||' AS 'Entity Name||',

    'SMCY||' AS 'CAS Site Identifier||',

    'Matrix||' AS 'CAS System Identifier||',

    'SMCY-Alternate ID-' + CAST(tp.PersonID AS varchar) + '||' AS 'Legacy Alternate ID||',

    'SMCY-Alternate ID-' + CAST(tp.PersonID AS varchar) + '||' AS 'Alternate ID||',

    '||SMCY' + CAST (tp.PersonID AS varchar) + '||' AS 'Participant ID||',

    --CASE WHEN p.ChildFileNumber IS NULL OR p.ChildFileNumber = '' THEN '||' ELSE p.ChildFileNumber + '||' END AS 'Document Number||',

    'MC_CA001' AS 'Type CD||',

    CONVERT (VARCHAR,getdate(), 103) + ' ' + CONVERT(VARCHAR, DATEPART(hh, getdate())) + ':' + RIGHT(CONVERT(VARCHAR, DATEPART(mi, getdate())), 2) + '||' AS 'Start Date||',

    '||' AS 'End Date||',

    'OHIP Number' AS 'Comments||',

    '||' AS 'Create Date||',

    '||' AS 'Create User||',

    '||' AS 'Last Update Date||',

    '||' AS 'Last Update User||',

    p.OHIP AS 'Document Number'

    FROM #temp_p tp

    LEFT OUTER JOIN People p ON tp.PersonID = p.PersonID

    LEFT OUTER JOIN Families f ON tp.PersonID = f.PersonID

    LEFT OUTER JOIN Cases c ON f.FamilyID = c.FamilyID

    where p.OHIP > ''

    SELECT IDENTITY(int, 1,1) AS 'RN', #temp.[Participant ID||] AS 'tp' INTO #temp_2

    FROM #temp -- There is already an object named '#temp_2' in the database.

    SELECT CAST(t2.RN AS varchar) + '||' AS 'Record Number||',

    t.* FROM #temp_2 t2

    LEFT OUTER JOIN #temp t ON t2.[tp] = t.[Participant ID||]

    DROP TABLE #temp

    DROP TABLE #temp_2

    DROP TABLE #temp_p

  • You're getting the errors because the temp tables already exist. I can see the lines further up where they are supposed to be dropped, but they are remarked, so the temp tables are never getting dropped. The SELECT.... INTO syntax will always try to create a new table. If instead you want to ADD rows to those already-existing temp tables, then in the 2nd and 3rd steps, you'll have to use the INSERT INTO command instead.

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • You are creating the #temp_2 table in the same script without dropping..

    Uncomment the code of DROP TABLE #temp_2 and run the query.It will work buddy 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Or, instead of dropping temp2, change the second "INTO temp2" to an "INSERT INTO temp2 (column list)" like you did with temp_p.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 4 posts - 1 through 4 (of 4 total)

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