tsql to JOIN the results of 2 temp tables (1 Viewer)

[Minty]

Rather than having to specify the columns for 200 fields I would create the SP like this

Use dbname
GO

CREATE PROC [dbo].[usp_ViewData]
AS


BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

---1.drop temp tables if it exist

IF OBJECT_ID('tempdb.dbo.#myTempTable', 'U') IS NOT NULL
DROP TABLE #MyTempTable;

IF OBJECT_ID('tempdb.dbo.#myTempTable2', 'U') IS NOT NULL
DROP TABLE #MyTempTable2;


IF OBJECT_ID('tempdb.dbo.#ScopeTemptable', 'U') IS NOT NULL
DROP TABLE #ScopeTemptable;

--2-- Creates temp table by selecting into temp table

SELECT * INTO #MyTempTable FROM [dbo].[vw1 Data pt1]

SELECT * INTO #MyTempTable2 FROM [dbo].[vw2 Data pt2]

SELECT * INTO #ScopeTemptable FROM [dbo].[vw3 Scope]

------ Run the query for to join the results from the 3 views

SELECT * FROM #MyTempTable
INNER JOIN #MyTempTable2
ON #MyTempTable.ProjReturnID = #MyTempTable2.ProjReturnID
LEFT JOIN #ScopeTemptable ON #MyTempTable.ProjReturnID = #ScopeTemptable.ProjectReturnID

----------------------------------------------------------------------------------------------
--- 3. SELECT required data from the 3 #temptables


--********************************************************
-- -- -- Temp code to run once to create the output table

SELECT *
INTO YourNewTableName
FROM
--********************************************************
-- -- -- Normal Code to populate the table

-- TRUNCATE TABLE YourNewTableName
-- INSERT INTO YourNewTableName

SELECT
t1.IDQ,
t1.[ID Number],
t1.ProjReturnID,
t2.[Benefits - Net Provided],
SC.CountOfProjScopeChngNo,
t2.[Start Date - Variance (Days)],
t2.[EndDate - Variance (Days)],
t2.[End Date - Variance (%)]
FROM #MyTempTable t1
INNER JOIN #MyTempTable2 t2
ON t1.ProjReturnID = t2.ProjReturnID
LEFT JOIN ScopeTemtable Sc ON t1.ProjReturnID = Sc.ProjectReturnID



---4. Drop temp tables

DROP TABLE #MyTempTable;
DROP TABLE #ScopeTemptable;
DROP TABLE #MyTempTable2


END

Run it once with the SELECT INTO section as is. It will create the table for you.
Then comment it out and uncomment the normal Truncate and insert into code. Job done.

Edit - re-reading this are you creating the table from the last section or the select before section 3 ?
Either way the same process will work.

 

[Mittle]

Rather than having to specify the columns for 200 fields I would create the SP like this

Use dbname
GO

CREATE PROC [dbo].[usp_ViewData]
AS


BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

---1.drop temp tables if it exist

IF OBJECT_ID('tempdb.dbo.#myTempTable', 'U') IS NOT NULL
DROP TABLE #MyTempTable;

IF OBJECT_ID('tempdb.dbo.#myTempTable2', 'U') IS NOT NULL
DROP TABLE #MyTempTable2;


IF OBJECT_ID('tempdb.dbo.#ScopeTemptable', 'U') IS NOT NULL
DROP TABLE #ScopeTemptable;

--2-- Creates temp table by selecting into temp table

SELECT * INTO #MyTempTable FROM [dbo].[vw1 Data pt1]

SELECT * INTO #MyTempTable2 FROM [dbo].[vw2 Data pt2]

SELECT * INTO #ScopeTemptable FROM [dbo].[vw3 Scope]

------ Run the query for to join the results from the 3 views

SELECT * FROM #MyTempTable
INNER JOIN #MyTempTable2
ON #MyTempTable.ProjReturnID = #MyTempTable2.ProjReturnID
LEFT JOIN #ScopeTemptable ON #MyTempTable.ProjReturnID = #ScopeTemptable.ProjectReturnID

----------------------------------------------------------------------------------------------
--- 3. SELECT required data from the 3 #temptables


--********************************************************
-- -- -- Temp code to run once to create the output table

SELECT *
INTO YourNewTableName
FROM
--********************************************************
-- -- -- Normal Code to populate the table

-- TRUNCATE TABLE YourNewTableName
-- INSERT INTO YourNewTableName

SELECT
t1.IDQ,
t1.[ID Number],
t1.ProjReturnID,
t2.[Benefits - Net Provided],
SC.CountOfProjScopeChngNo,
t2.[Start Date - Variance (Days)],
t2.[EndDate - Variance (Days)],
t2.[End Date - Variance (%)]
FROM #MyTempTable t1
INNER JOIN #MyTempTable2 t2
ON t1.ProjReturnID = t2.ProjReturnID
LEFT JOIN ScopeTemtable Sc ON t1.ProjReturnID = Sc.ProjectReturnID



---4. Drop temp tables

DROP TABLE #MyTempTable;
DROP TABLE #ScopeTemptable;
DROP TABLE #MyTempTable2


END

Run it once with the SELECT INTO section as is. It will create the table for you.
Then comment it out and uncomment the normal Truncate and insert into code. Job done.

Edit - re-reading this are you creating the table from the last section or the select before section 3 ?
Either way the same process will work.

Thanks a lot but am not clear about this bit



SELECT *
INTO YourNewTableName
FROM..........???

not sure what to put in there .I understand the syntax of select into new table from old table but OLD table doesnt exist . the table am trying to create is meant to be from the results of the stored proc

 

[Mittle]

Rather than having to specify the columns for 200 fields I would create the SP like this

Use dbname
GO

CREATE PROC [dbo].[usp_ViewData]
AS


BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

---1.drop temp tables if it exist

IF OBJECT_ID('tempdb.dbo.#myTempTable', 'U') IS NOT NULL
DROP TABLE #MyTempTable;

IF OBJECT_ID('tempdb.dbo.#myTempTable2', 'U') IS NOT NULL
DROP TABLE #MyTempTable2;


IF OBJECT_ID('tempdb.dbo.#ScopeTemptable', 'U') IS NOT NULL
DROP TABLE #ScopeTemptable;

--2-- Creates temp table by selecting into temp table

SELECT * INTO #MyTempTable FROM [dbo].[vw1 Data pt1]

SELECT * INTO #MyTempTable2 FROM [dbo].[vw2 Data pt2]

SELECT * INTO #ScopeTemptable FROM [dbo].[vw3 Scope]

--********************************************************
-- -- -- Temp code to run once to create the output table

SELECT *
INTO YourNewTableName
FROM
--********************************************************
-- -- -- Normal Code to populate the table

-- TRUNCATE TABLE YourNewTableName
-- INSERT INTO YourNewTableName
[/QUOTE]



[QUOTE="Minty, post: 1742117, member: 122497"]

--------- 3. Run the query to output required columns by joining the 3 temp tables 
SELECT
t1.IDQ,
t1.[ID Number],
t1.ProjReturnID,
t2.[Benefits - Net Provided],
SC.CountOfProjScopeChngNo,
t2.[Start Date - Variance (Days)],
t2.[EndDate - Variance (Days)],
t2.[End Date - Variance (%)]
FROM #MyTempTable t1
INNER JOIN #MyTempTable2 t2
ON t1.ProjReturnID = t2.ProjReturnID
LEFT JOIN ScopeTemtable Sc ON t1.ProjReturnID = Sc.ProjectReturnID



---4. Drop temp tables

DROP TABLE #MyTempTable;
DROP TABLE #ScopeTemptable;
DROP TABLE #MyTempTable2


END

Run it once with the SELECT INTO section as is. It will create the table for you.
Then comment it out and uncomment the normal Truncate and insert into code. Job done.

Edit - re-reading this are you creating the table from the last section or the select before section 3 ?
Either way the same process will work.

ive now put the 3rd step in the correct section in the sp .( it was not correct before)so am running the query as above but getting incorrect syntax near keyword select

 

[Minty]

Old Table doesn't exist is correct, you are using your select statement as the source for the NewTable

Sorry my syntax was a little off it should have been;

SELECT *
INTO YourNewTableName
FROM
     (
     SELECT a,b,c from #tempstuff
     ) as x

Would create a table with three fields a b and c from #tempstuff

 

[Mittle]

Old Table doesn't exist is correct, you are using your select statement as the source for the NewTable

Sorry my syntax was a little off it should have been;

SELECT *
INTO YourNewTableName
FROM
     (
     SELECT a,b,c from #tempstuff
     ) as x

Would create a table with three fields a b and c from #tempstuff

Thanks I should know that myself. Fantastic this is great. ignorance is really bad . I was going to do long winded and start creating 200 columns with data types etc . Phew .
I will now do insert the other bits - truncate etc and let you know .

 

[Minty]

Good luck with the rest of it. It gets easier with practice