Inserting into Temp tables and Union Queries

[tunna991]

Hi there,

I have a few queries that I need to join because they have the same structure. As well I have three temp table in my query that I want to use across the different queries. I tried the 'insert into' commend in the temp tables but that did not work. I think it would not be effective programming if I were to use different temp tables for each query. Below is the sample query which are two queries in one.






-- First Query
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 'Phone_Number||' AS 'Entity Name||',
'SMCY||' AS 'CAS Site Identifier||',
'Matrix||' AS 'CAS System Identifier||',
'SMCY-PHONE-' + CAST(tp.PersonID AS varchar) + '||' AS 'Legacy Phone Number ID ||',
'SMCY-PHONE-' + CAST(tp.PersonID AS varchar) + '||' AS 'Phone Number Id||',
'||SMCY' + CAST (tp.PersonID AS varchar) + '||' AS 'Associated Parent Id||',
'RP||' AS 'Associated Record Type CD||',
'||' AS 'Phone Country Code||',
'|XXX|' AS 'Phone Area Code||',
'||' AS 'Extension||',
CASE WHEN c.HomePhone IS NULL OR c.HomePhone = '' THEN '||' ELSE c.HomePhone + '||' END AS 'Phone Number||',
'|MC_PH7|' AS 'Type CD||',
c.createdate AS 'Start Date||',
'||' AS 'End Date||',
'||' AS 'Comments||',
'||' AS 'Create Date||',
'||' AS 'Create User||',
'||' AS 'Last Update Date||',
' ' AS 'Last Update User'

INTO #temp

FROM #temp_p tp

LEFT OUTER JOIN People p ON tp.PersonID = p.PersonID
LEFT OUTER JOIN AddressMembers f ON tp.PersonID = f.PersonID
LEFT OUTER JOIN Addresses c ON f.AddressID = c.AddressID
WHERE f.IsPrimary = 1 and c.createdate is not null and c.homephone > ''


SELECT IDENTITY(int, 1,1) AS 'RN', #temp.[Associated Parent 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.[Associated Parent Id||]

DROP TABLE #temp
DROP TABLE #temp_2
DROP TABLE #temp_p

Union
-- Second Query

SELECT DISTINCT p.PersonID
Insert 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 'Phone_Number||' AS 'Entity Name||',
'SMCY||' AS 'CAS Site Identifier||',
'Matrix||' AS 'CAS System Identifier||',
'SMCY-PHONE-' + CAST(tp.PersonID AS varchar) + '||' AS 'Legacy Phone Number ID ||',
'SMCY-PHONE-' + CAST(tp.PersonID AS varchar) + '||' AS 'Phone Number Id||',
'||SMCY' + CAST (tp.PersonID AS varchar) + '||' AS 'Associated Parent Id||',
'RP||' AS 'Associated Record Type CD||',
'||' AS 'Phone Country Code||',
'|XXX|' AS 'Phone Area Code||',
'||' AS 'Extension||',
CASE WHEN c.cellphone IS NULL OR c.cellphone = '' THEN '||' ELSE c.cellphone + '||' END AS 'Phone Number||',
'|PH3|' AS 'Type CD||',
c.createdate AS 'Start Date||',
'||' AS 'End Date||',
'||' AS 'Comments||',
'||' AS 'Create Date||',
'||' AS 'Create User||',
'||' AS 'Last Update Date||',
' ' AS 'Last Update User'

Insert INTO #temp

FROM #temp_p tp

LEFT OUTER JOIN People p ON tp.PersonID = p.PersonID
LEFT OUTER JOIN AddressMembers f ON tp.PersonID = f.PersonID
LEFT OUTER JOIN Addresses c ON f.AddressID = c.AddressID
WHERE f.IsPrimary = 1 and c.createdate is not null and c.cellphone > ''


SELECT IDENTITY(int, 1,1) AS 'RN', #temp.[Associated Parent Id||] AS 'tp' Insert 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.[Associated Parent Id||]

DROP TABLE #temp
DROP TABLE #temp_2
DROP TABLE #temp_p


Msg 156, Level 15, State 1, Line 58
Incorrect syntax near the keyword 'Union'.
Msg 156, Level 15, State 1, Line 63
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 97
Incorrect syntax near the keyword 'FROM'.
Msg 177, Level 15, State 1, Line 105
The IDENTITY function can only be used when the SELECT statement has an INTO clause.
Msg 156, Level 15, State 1, Line 106
Incorrect syntax near the keyword 'FROM'.

 

[gmmastros]

Your insert into command did not work because you have the syntax wrong. You have this...

[!]SELECT DISTINCT p.PersonID
Insert 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

With insert into, the insert part comes first, and then the select part, like this:

[!]
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

Basically, it's this....

"Select into" only works if the destination table does not exist because it will create the table for you.

"Insert into" only works if the destination table already exists because it cannot create the table.

There are other problems with the code you posted, but I suspect that if you get the insert into working, you can probably handle the rest.

BTW, My code from 10 years ago looked a lot like this. Since then, I have learned how to use derived tables and common table table expressions. I haven't read every line you posted, but I suspect that this entire process could be accomplished without any temp tables. If so, this would likely make the code easier to read (and therefore easier to maintain), and also perform better.

Let me know if you have any other specific questions about this code, or if you would like additional pointers on improving it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[philhege]

You have DROP statements right before the UNION statement. There are a number of syntax errors here.

Can you explain what you're trying to accomplish? Why is there a need for the two separate processes?

Can you provide a set of sample data from the underlying source tables, and a sample result, showing your intended outcome?

-----------
With business clients like mine, you'd be better off herding cats.

 

[tunna991]

First of all, thanks for all the help so far. Basically this query 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(SIN #, Health Card Number, and File #) fields 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.

After doing what was suggested below is what I got. I have made in bold the error columns and pasted the error code below for easier reference.

--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||]

Union All
--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 -- Incorrect syntax near the keyword 'Insert'
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

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'


INTO #temp
FROM #temp_p tp --There is already an object named '#temp' in the database.
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||]

Union All
--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 --Incorrect syntax near the keyword 'Insert
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

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'


INTO #temp
FROM #temp_p tp --Incorrect syntax near the keyword 'Insert
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||]

Union All
--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 -- Incorrect syntax near the keyword 'Insert'.
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

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'


INTO #temp
FROM #temp_p tp --There is already an object named '#temp' in the database
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

 

[gmmastros]

Uh.... that thing I mentioned earlier about "Insert Into".... well, I only showed you how to fix one of them. There are more. And, if you are going to use the temp table method, then you should remove all of the "Union All" statements.

Honestly, I have no motivation to fix all of your code, but I certainly don't mind pointing out the problems so that you learn how to fix the problems yourself.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tunna991]

Thanks for your pointers. I think I am closer to figuring this out.

Now in the Identity SQL statement which will create an incrementing record number, I have tried the insert into statement like this


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

But the error message I get is 'There is already an object named '#temp_2' in the database'

I also tried it this way putting the Insert Into statement at the top of the query but still does not work.

 

[gmmastros]

I noticed the you have a couple of queries in your code that is like this. It seems like the purpose of this is to get an incrementing counter for your data. The simplest way to do this is to manually create the table at the beginning of your code, and then do insert into's instead of select into's.

Ex:

Manually create the table like this:

Create Table #temp_2 (RN int identity(1,1), tp varchar(100))

Then, insert into it like this:

Insert
into   #temp_2(tp)
SELECT #temp.[Participant ID||]
FROM  #temp

Notice that the temp_2 table has an identity column for RN. Also notice that since this is an identity, you do not include it in the insert into query. It will automatically get an incrementing value, exactly the way you want it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom