Inserting into Temp tables

Question

Inserting into Temp tables

adnan.zamir

Grasshopper

Points: 20
More actions
November 13, 2015 at 8:38 pm

#306437

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

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

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

Sigerson Hall of Fame Points: 3697 More actions · Answer 1

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

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/

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 3

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.