TEMP TABLE

Question

TEMP TABLE

michael.petrone

SSC Rookie

Points: 26
More actions
September 1, 2014 at 4:00 am

#292816

1. Start of with a blank temp table.
2. Select
3. Check Temp Table - SSN not in temp table send to Report Builder 3.0 ELSE insert into temp table.
4. Loop to #2

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

michael.petrone (9/1/2014)
1. Start of with a blank temp table.
2. Select
3. Check Temp Table - SSN not in temp table send to Report Builder 3.0 ELSE insert into temp table.
4. Loop to #2

Is there a question in here, Michael? Your code looks like a dedupe procedure.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

michael.petrone SSC Rookie Points: 26 More actions · Answer 2

Mainframe programmer, How would you write an SQL stored procedure to achieve this?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

We’ll need better definitions.

What can you tell us about the source table? Can you provide DDL and DML?

“Send to Report Builder” – do you mean populate another table for Report Builder to use as a source? Or perhaps use the output from the stored procedure?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

michael.petrone SSC Rookie Points: 26 More actions · Answer 4

DML

OUTPUT from Stored Procedure

FOUND a copy of the stored procedure I'm working with----- see below----

DECLARE @ItemClient TABLE

(

ClientSSN Varchar (11),

LastName Varchar (35),

FirstName Varchar (15),

ParentBirthDate datetime

)

INSERT INTO @ItemClient

SELECT ClientSSN, LastName, FirstName, ParentBirthDate

FROM ClientDetail

WHERE SysActive = 1

SELECT y.ClientSSN,y.FirstName,y.LastName,y.ParentBirthDate,dt.ClientSSN AS 'OtherSSN', y.RecordEntryDate

FROM [dbo].[ClientDetail] y

INNER JOIN @ItemClient dt

ON y.LastName=dt.LastName and y.FirstName=dt.FirstName and y.ParentBirthDate = dt.ParentBirthDate and y.ClientSSN <> dt.ClientSSN

WHERE y.RecordEntryDate >= '01/01/2013' and y.SysActive = 1

--ORDER BY y.RecordEntryDate DESC

ORDER BY y.LastName DESC

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

michael.petrone (9/1/2014)
DML
OUTPUT from Stored Procedure
FOUND a copy of the stored procedure I'm working with----- see below----
DECLARE @ItemClient TABLE
(
ClientSSN Varchar (11),
LastName Varchar (35),
FirstName Varchar (15),
ParentBirthDate datetime
)
INSERT INTO @ItemClient
SELECT ClientSSN, LastName, FirstName, ParentBirthDate
FROM ClientDetail
WHERE SysActive = 1
SELECT y.ClientSSN,y.FirstName,y.LastName,y.ParentBirthDate,dt.ClientSSN AS 'OtherSSN', y.RecordEntryDate
FROM [dbo].[ClientDetail] y
INNER JOIN @ItemClient dt
ON y.LastName=dt.LastName and y.FirstName=dt.FirstName and y.ParentBirthDate = dt.ParentBirthDate and y.ClientSSN <> dt.ClientSSN
WHERE y.RecordEntryDate >= '01/01/2013' and y.SysActive = 1
--ORDER BY y.RecordEntryDate DESC
ORDER BY y.LastName DESC

Are you okay with this or do you still have a question?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

michael.petrone SSC Rookie Points: 26 More actions · Answer 6

Here is the Question:

I'm fine with declaring the table in the beginning.

I want to check the temp table while selecting - first time through temp table will be empty.

So I want to use the record on the select - But then Insert SSN into Temp Table.

Next Record, I want to check temp table for SSN, if in temp table don't select.

If not in temp table - store in temp table and use record.

Looping

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

michael.petrone (9/1/2014)
Here is the Question:
I'm fine with declaring the table in the beginning.
I want to check the temp table while selecting - first time through temp table will be empty.
So I want to use the record on the select - But then Insert SSN into Temp Table.
Next Record, I want to check temp table for SSN, if in temp table don't select.
If not in temp table - store in temp table and use record.
Looping

No looping.

insert into the temp table, select distinct rows from source.

When done, select from temp table for output.

(or use OUTPUT clause with INSERT)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden