Insert into a single temporary table values from multiple queries

Question

0.00/5 (No votes)

See more:

--------------- SQL ---------------

CREATE PROCEDURE [dbo].[HRsp_Data_Correction]
-- Add the parameters for the stored procedure here
@userid VARCHAR(10) = NULL,
@grp1 VARCHAR(MAX) = NULL,
@grp2 VARCHAR(MAX) = NULL,
@grp3 VARCHAR(MAX) = NULL,
@region_filter VARCHAR(MAX) = NULL,
@site_filter VARCHAR(MAX) = NULL,
@sect_filter VARCHAR(MAX) = NULL,
@post_filter VARCHAR(MAX) = NULL,
@nat_filter VARCHAR(MAX) = NULL,
@reli_filter VARCHAR(MAX) = NULL,
@cat_filter VARCHAR(MAX) = NULL,
@agent_filter VARCHAR(MAX) = NULL,
@prof_filter VARCHAR(MAX) = NULL,
@sectmain_filter VARCHAR(MAX) = NULL,
@sectsub_filter VARCHAR(MAX) = NULL,
@sectteam_filter VARCHAR(MAX) = NULL,
@spcl_filter VARCHAR(MAX) = NULL,
@extra_filter VARCHAR(MAX) = NULL,
@report_id INT = 1,
@with_result BIT = 0,
@upto VARCHAR(20) = NULL

AS

-- Do not continue if no userid parameter
IF @userid IS NULL or @userid = '' RETURN

-- 02-03-2009::Set value to Current Date if @Upto is not specified
IF @upto IS NULL SET @upto=CONVERT(VARCHAR(10),CONVERT(DATETIME,GETDATE(),103),103);

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

SET NOCOUNT ON;
SET DATEFORMAT DMY;

-- define and initialize local variables
DECLARE @sqlcmd VARCHAR(MAX),
@sqlSubquery_Personal VARCHAR(MAX),
@sqlSubquery_Engage VARCHAR(MAX),
@sqlSumquery VARCHAR(MAX),
@sqlFields VARCHAR(MAX),
@sqlGroups VARCHAR(MAX),
@sqlTables VARCHAR(MAX),
@sqlTablesadd VARCHAR(MAX),
@sqlWhere VARCHAR(MAX),
@sqlOrder VARCHAR(MAX),
@forPaginate VARCHAR(MAX)

-- DEFAULTS for the SQL STATEMENT ----------------------------------------------
--------------------------------------------------------------------------------

-- set the default report grouping to by Country and company site
-- if grouping is not specified

IF @grp1 IS NULL OR @grp1 = '' SET @grp1 = 'region'
IF @grp2 IS NULL SET @grp2 = ''
IF @grp3 IS NULL SET @grp3 = ''

SET @forPaginate = ''

IF (@with_result = 1)
BEGIN
-- check if global temporary table exists
IF OBJECT_ID('tempdb..##tmp_manpower','U') IS NOT NULL DROP TABLE ##tmp_manpower
SET @forPaginate = N' INTO ##tmp_manpower '
END

/* Main Fields to Select */
SET @sqlGroups = @grp1
IF @grp2 <> '' SET @sqlGroups = @sqlGroups + ',' + @grp2
IF @grp3 <> '' SET @sqlGroups = @sqlGroups + ',' + @grp3
SET @sqlGroups = @sqlGroups + ', mcat'

/* Main Fields to Select */
SET @sqlFields = @grp1 + ' AS group1 '
IF @grp2 <> '' SET @sqlFields = @sqlFields + ',' + @grp2 + ' AS group2 '
ELSE SET @sqlFields = @sqlFields + ','' '' AS group2 '
IF @grp3 <> '' SET @sqlFields = @sqlFields + ',' + @grp3 + ' AS group3 '
ELSE SET @sqlFields = @sqlFields + ','' '' AS group3 '
SET @sqlFields = @sqlFields +', mcat, '
SET @sqlFields = @sqlFields + 'COUNT(DISTINCT empno) AS cnt, SUM(age) as sum_age, SUM(total_group_years) AS sum_yrs, '+
'AVG(age) AS age, AVG(total_group_years) AS yrs '

/* Tables used */
SET @sqlTables = N'fn_AllEmployeeForDataCorretion('''+@upto+''','''+@userid+''') AS e '
SET @sqlTablesadd = N'fn_AllEmployeeForDataCorretionAddress('''+@upto+''','''+@userid+''') AS f '

/* Order of Records */
SET @sqlOrder = N'1,2,3,4'

/* Records Accessed filter */
SET @sqlWhere = ' (region IN (SELECT code FROM dbo.fn_CheckAccess('''+@userid+''',''R'')) OR ' +
' conssite IN (SELECT code FROM dbo.fn_CheckAccess('''+@userid+''',''S''))) '

-- Start ==> Added: 27-12-08
-- Check Sections Permission
DECLARE @sections VARCHAR(5);
DECLARE @employees VARCHAR(5);
SELECT @sections = (SELECT TOP 1 code FROM dbo.fn_CheckAccess(@userid,'N'))
SELECT @employees = (SELECT TOP 1 code FROM dbo.fn_CheckAccess(@userid,'E'))
IF @sections IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND sect IN (SELECT code FROM dbo.fn_CheckAccess('''+@userid+''',''N'')) '
-- Check Employees Sections
IF @employees IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND empno IN (SELECT code FROM dbo.fn_CheckAccess('''+@userid+''',''E'')) '
-- End ==> Added: 27-12-08

-- Add the filters, if ANY
IF @region_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND region IN (' + @region_filter + ')'
IF @site_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND conssite IN (' + @site_filter + ')'
IF @sect_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND sect IN (' + @sect_filter + ')'
IF @post_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND post IN (' + @post_filter + ')'
IF @prof_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND prof IN (' + @prof_filter + ')'
IF @nat_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND nat IN (' + @nat_filter + ')'
IF @reli_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND reli IN (' + @reli_filter + ')'
IF @cat_filter IS NOT NULL SET @sqlWhere = @sqlWhere + ' AND mcat IN (' + @cat_filter + ')'
IF @extra_filter IS NOT NULL SET @sqlWhere = @sqlWhere + @extra_filter

-- END of DEFAULTS for the SQL STATEMENT ----------------------------------------------
--------------------------------------------------------------------------------

/* Make the SQL Statements to get the records from the masterfile ang group them accordingly */
SET @sqlSubquery_Personal = ' SELECT ' + @sqlFields +
' FROM ' + @sqlTables +
' WHERE ' + @sqlWhere +
' And nat = '' '' or nat = NULL or birthdate ='' '' or birthdate = NULL
or dest_code = '' '' or dest_code = NULL or family_name = '' '' or
family_name = NULL or first_name = '' '' or first_name = NULL or reli = '' ''
or reli = NULL' +
' GROUP BY ' + @sqlGroups

SET @sqlSubquery_Engage = ' SELECT ' + @sqlFields +
' FROM ' + @sqlTables +
' WHERE ' + @sqlWhere +
' And init_date = '' '' or init_date = NULL or reeg_date ='' '' or reeg_date = NULL
or contract_date = '' '' or contract_date = NULL ' +
' GROUP BY ' + @sqlGroups

/* STORE the group records */
CREATE TABLE #resultset (
group1 VARCHAR(MAX),
group2 VARCHAR(MAX),
group3 VARCHAR(MAX),
totals_cnt INT ,
totals_age INT ,
totals_yrs DECIMAL(8,2) ,
Pers_exp_cnt INT,
Pers_exp_age INT,
Pers_exp_yrs DECIMAL(6,2),
Pers_exo_cnt INT,
Pers_exo_age INT,
Pers_exo_yrs DECIMAL(6,2),
Pers_tcn_cnt INT,
Pers_tcn_age INT,
Pers_tcn_yrs DECIMAL(6,2),
Engt_exp_cnt INT,
Engt_exp_age INT,
Engt_exp_yrs DECIMAL(6,2),
Engt_exo_cnt INT,
Engt_exo_age INT,
Engt_exo_yrs DECIMAL(6,2),
Engt_tcn_cnt INT,
Engt_tcn_age INT,
Engt_tcn_yrs DECIMAL(6,2)
--loc_cnt INT,
--loc_age INT,
--loc_yrs DECIMAL(6,2),
--lor_cnt INT,
--lor_age INT,
--lor_yrs DECIMAL(6,2)
)

INSERT INTO #resultset
EXEC(' (SELECT group1,group2,group3,
SUM(cnt) as totals_cnt, SUM(sum_age)/SUM(cnt) as totals_age, SUM(sum_yrs)/SUM(cnt) as totals_yrs,
SUM(CASE WHEN mcat=''3'' THEN cnt ELSE 0 END) as Pers_exp_cnt,
SUM(CASE WHEN mcat=''3'' THEN age ELSE 0 END) as Pers_exp_age,
SUM(CASE WHEN mcat=''3'' THEN yrs ELSE 0 END) as Pers_exp_yrs,
SUM(CASE WHEN mcat=''4'' THEN cnt ELSE 0 END) as Pers_exo_cnt,
SUM(CASE WHEN mcat=''4'' THEN age ELSE 0 END) as Pers_exo_age,
SUM(CASE WHEN mcat=''4'' THEN yrs ELSE 0 END) as Pers_exo_yrs,
SUM(CASE WHEN mcat=''2'' THEN cnt ELSE 0 END) as Pers_tcn_cnt,
SUM(CASE WHEN mcat=''2'' THEN age ELSE 0 END) as Pers_tcn_age,
SUM(CASE WHEN mcat=''2'' THEN yrs ELSE 0 END) as Pers_tcn_yrs '+
' FROM (' + @sqlSubquery_Personal + ' ) AS M GROUP BY group1,group2,group3 )'+
'(SELECT group1,group2,group3,
SUM(cnt) as totals_cnt, SUM(sum_age)/SUM(cnt) as totals_age, SUM(sum_yrs)/SUM(cnt) as totals_yrs,
SUM(CASE WHEN mcat=''3'' THEN cnt ELSE 0 END) as Engt_exp_cnt,
SUM(CASE WHEN mcat=''3'' THEN age ELSE 0 END) as Engt_exp_age,
SUM(CASE WHEN mcat=''3'' THEN yrs ELSE 0 END) as Engt_exp_yrs,
SUM(CASE WHEN mcat=''4'' THEN cnt ELSE 0 END) as Engt_exo_cnt,
SUM(CASE WHEN mcat=''4'' THEN age ELSE 0 END) as Engt_exo_age,
SUM(CASE WHEN mcat=''4'' THEN yrs ELSE 0 END) as Engt_exo_yrs,
SUM(CASE WHEN mcat=''2'' THEN cnt ELSE 0 END) as Engt_tcn_cnt,
SUM(CASE WHEN mcat=''2'' THEN age ELSE 0 END) as Engt_tcn_age,
SUM(CASE WHEN mcat=''2'' THEN yrs ELSE 0 END) as Engt_tcn_yrs ' +
' FROM (' + @sqlSubquery_Engage + ' ) AS M GROUP BY group1,group2,group3 )')
print @sqlSubQuery_Personal;
print @sqlSubquery_Engage;
/* NOW, return the result with the descriptions */
SELECT @sqlcmd = N'SELECT R.*, '+
N' RT.tot_totals,RT.Pers_tot_exp,RT.Pers_tot_exo, RT.Pers_tot_tcn,RT.Engt_tot_exp,RT.Engt_tot_exo, RT.Engt_tot_tcn,' +
N' r1.code_desc AS group1_desc '
IF @grp2 <> '' SET @sqlcmd = @sqlcmd + ', r2.code_desc AS group2_desc ' + @forPaginate
IF @grp3 <> '' SET @sqlcmd = @sqlcmd + ', r3.code_desc AS group3_desc '
SET @sqlcmd = @sqlcmd + ' FROM #resultset AS R ' +
N' LEFT JOIN dbo.fn_SelRef('''+@userid+''','''+@grp1+''') AS r1 ON R.group1 = r1.code '
IF @grp2 <> '' SET @sqlcmd = @sqlcmd + ' LEFT JOIN dbo.fn_SelRef('''+@userid+''','''+@grp2+''') AS r2 ON R.group2 = r2.code '
IF @grp3 <> '' SET @sqlcmd = @sqlcmd + ' LEFT JOIN dbo.fn_SelRef('''+@userid+''','''+@grp3+''') AS r3 ON R.group3 = r3.code '
SET @sqlcmd = @sqlcmd + ', (SELECT SUM(totals_cnt) as tot_totals,SUM(Pers_exp_cnt) as Pers_tot_exp,
SUM(Pers_exo_cnt) AS Pers_tot_exo,
SUM(Pers_tcn_cnt) AS Pers_tot_tcn,SUM(Engt_exp_cnt) as Engt_tot_exp,
SUM(Engt_exo_cnt) AS Engt_tot_exo,
SUM(Engt_tcn_cnt) AS Engt_tot_tcn
FROM #resultset) AS RT ' +
N' ORDER BY R.group1, R.group2, R.group3 '
/* LAST, Return the bloody result */
EXEC(@sqlcmd)

RETURN

Here is the procedure i have created and the point of concern for me is that i am creating a temporary table as #resultset which should have the columns specified as the variable names but the dat for these columns depends upon different queries.. Now how to insert data from multiple queries into a single created temporary table..
Thanks and i hope some genious will sought a solution for me..

Khan
SA

Posted 8-Dec-09 19:32pm

khanishfaq82

Add a Solution

9 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

mkarthikcse · Answer 1 · 2009-12-08T19:58:00

Hi,

create proc sp_sample
as
begin
create table #temp
(
slno varchar(50),
name varchar(50)
)
insert into #temp(slno,name)
select table1.slno,table2.name from table1,table2
where table1.slno=table2.slno
end
this s sample store procedure..if u want more help.pls reply..

Thanks & regrads
karthikeyan

AspDotNetDev · Answer 2 · 2009-12-08T19:50:00

I'm not going to look at your code, because frankly you have posted too much. When you post code, simplify it by posting a small sample that demonstrates your problem. Now, on to your question...

Just insert as you would with any other table. Do several insert statements in a row. For example:

SQL

--Insert some records from one table.
INSERT INTO #MyTempTable(Col1, Col2) SELECT TableCol1, TableCol2 FROM SomeTable

-- Insert some records from another table.
INSERT INTO #MyTempTable(Col1, Col2) SELECT OtherCol1, OtherCol2 FROM SomeOtherTable

Nothing much to it.

khanishfaq82 · Answer 3 · 2009-12-08T20:04:00

Dear Friend aspdotnetdev.
Yes i have posted the code coz many times people complain of not having the code present..
Newa coming to the point friend,
As you have specified,
INSERT INTO #MyTempTable(Col1, Col2) SELECT TableCol1, TableCol2 FROM SomeTable
INSERT INTO #MyTempTable(Col1, Col2) SELECT OtherCol1, OtherCol2 FROM SomeOtherTable
..

I want something like this,
INSERT INTO #MyTempTable(Col1, Col2,Col3,Col4) (SELECT TableCol1, TableCol2 FROM SomeTable),(SELECT OtherCol1, OtherCol2 FROM SomeOtherTable)..

I have tried UNION ALL even but that aint work..
I hope u got my point

khanishfaq82 · Answer 4 · 2009-12-08T20:25:00

Dear Karthekeyan,
create table #temp
(
slno varchar(50),
name varchar(50)
)
insert into #temp(slno,name)
select table1.slno,table2.name from table1,table2
where table1.slno=table2.slno
end

See how it goes for me..
e.g:
QUERY1:Set @q1 = Select count(*) from employee where First_name = ' ' or first_name= NULL
QUERY2:Set @q2 = Select count(*) from employee where Last_name = ' ' or first_name= NULL

now what i want is the data retrieved from these queries to be inserted into the table and the outlook should be like

First_Name Last_Name
10 12

AspDotNetDev · Answer 5 · 2009-12-08T20:49:00

If the data between the two tables is related, use a join. If the data is unrelated, but you still want to combine the data into different rows, it sounds like you are just trying to consolidate the data into a single result set. You don't really have to do that. You can just return 2 temporary tables from your stored procedure and .Net can read in both result sets. However, if you really want to combine the data into 1 temporary table and the data is unrelated, here is one way to do it...

Create a temporary table with an identity column, so that row can be uniquely identified by the number in that column (we'll call this table #results). Insert the results from query 1 into #TempTable1 (this will have an identity column too). Insert the results from query 2 into #TempTable2 (this will have an identity column too). See which of the temp tables is bigger... insert that many records into your #results table... you can do this with a while loop (each iteration you would insert 1 row). Doesn't matter what data is inserted... what's important is that the identity column creates the unique number for each row. Now, update #results with the values from #TempTable1. You can do this by joining the identity column in #results to the identity column in #TempTable1. Next, update #results with the values from #TempTable2... again, join on the identity columns.

mkarthikcse · Answer 6 · 2009-12-08T22:56:00

Hi,

so u should create single temp table,in that u specify all the fields u want
like
create table #temp
(
col1 varchar(50) null,
col2 varchar(50) null,
col3 varchar(50) null,
col4 varchar(50) null,
)

first u insert first table values whatever it've,
like
insert into #temp(col1,col2,col3,col4)
select val1,val2,null,null from firsttable
where some condition
then null values will be added in col3 and col4
then afterwards
update #temp set col3=val3,col4=val4
from secondtable
where some condition...
is it ok...

khanishfaq82 · Answer 7 · 2009-12-09T21:14:00

Hey Guys Found The Answer..
Thanks All..

Here Is It:
***************
SELECT mcat_order, cs,
engagement_cnt = SUM(engagement_cnt),
personal_cnt = SUM(personal_cnt),
documents_cnt = SUM(documents_cnt)
FROM (
SELECT mcat_order = CASE contcatg_code
WHEN '3' THEN 1
WHEN '4' THEN 2
ELSE 3 END,
cs = CASE WHEN contcatg_code='3' THEN 'EXPA'
WHEN contcatg_code='4' THEN 'EXPO'
ELSE cs END,
engagement_cnt = CASE WHEN (group_initial_joined = '' or group_joined = '' or contract_date ='')
THEN 1 ELSE 0 END,
personal_cnt = CASE WHEN (family_name='' or first_name='' or birthdate='' or nationality_code='' or religion_code=''
or ISNULL(ea.address,'') = '')
THEN 1 ELSE 0 END,
documents_cnt = CASE WHEN (ISNULL(d1.reference_no,'')='') THEN 1 ELSE 0 END
FROM HR_employee e LEFT JOIN
(SELECT ea.empno, address FROM HR_eaddress ea WHERE addresstype='P') AS ea
ON e.empno = ea.empno
LEFT JOIN
(SELECT empno, doccode, reference_no FROM HR_edocuments ed WHERE doccode='PAS') AS d1
ON e.empno = d1.empno
WHERE contcatg_code <> '1' AND contcatg_code <> '5' ) e
GROUP BY mcat_order, cs
ORDER BY 1,2

SELECT * FROM (
SELECT mcat_order = CASE contcatg_code
WHEN '3' THEN 1
WHEN '4' THEN 2
ELSE 3 END,
cs = CASE WHEN contcatg_code='3' THEN 'EXPA'
WHEN contcatg_code='4' THEN 'EXPO'
ELSE cs END,
engagement_cnt = CASE WHEN (group_initial_joined = '' or group_joined = '' or contract_date ='')
THEN 1 ELSE 0 END,
personal_cnt = CASE WHEN (family_name='' or first_name='' or birthdate='' or nationality_code='' or religion_code=''
or ISNULL(ea.address,'') = '')
THEN 1 ELSE 0 END,
documents_cnt = CASE WHEN (ISNULL(d1.reference_no,'')='') THEN 1 ELSE 0 END,
e.empno, family_name, first_name,group_initial_joined,group_joined, contract_date,
birthdate, nationality_code, religion_code, address = ea.address,
d1.doccode, d1.reference_no
FROM HR_employee e LEFT JOIN
(SELECT ea.empno, address FROM HR_eaddress ea WHERE addresstype='P') AS ea
ON e.empno = ea.empno
LEFT JOIN
(SELECT empno, doccode, reference_no FROM HR_edocuments ed WHERE doccode='PAS') AS d1
ON e.empno = d1.empno
WHERE contcatg_code <> '1' AND contcatg_code <> '5') AS emps
WHERE engagement_cnt+personal_cnt > 0
ORDER BY 1,2

khanishfaq82 · Answer 8 · 2009-12-08T21:20:00

Dear Karthekeyan.
I have created the two sets now but it is an obligation for me to run them separately like i am using EXEC(@sqlcmd) fro the ist reultset and EXEC(@sqlcmde) for the second one..
Now i want to execute both off them simultaneouly..
Have a look at this code..

***********************************************************
[Part I]
SELECT @sqlcmd = N'SELECT R.*, '+
N' RT.tot_totals,RT.Pers_tot_exp,RT.Pers_tot_exo, RT.Pers_tot_tcn,' +
N' r1.code_desc AS group1_desc '
IF @grp2 <> '' SET @sqlcmd = @sqlcmd + ', r2.code_desc AS group2_desc ' + @forPaginate
IF @grp3 <> '' SET @sqlcmd = @sqlcmd + ', r3.code_desc AS group3_desc '
SET @sqlcmd = @sqlcmd + ' FROM #resultset AS R ' +
N' LEFT JOIN dbo.fn_SelRef('''+@userid+''','''+@grp1+''') AS r1 ON R.group1 = r1.code '
IF @grp2 <> '' SET @sqlcmd = @sqlcmd + ' LEFT JOIN dbo.fn_SelRef('''+@userid+''','''+@grp2+''') AS r2 ON R.group2 = r2.code '
IF @grp3 <> '' SET @sqlcmd = @sqlcmd + ' LEFT JOIN dbo.fn_SelRef('''+@userid+''','''+@grp3+''') AS r3 ON R.group3 = r3.code '
SET @sqlcmd = @sqlcmd + ', (SELECT SUM(totals_cnt) as tot_totals,SUM(Pers_exp_cnt) as Pers_tot_exp,
SUM(Pers_exo_cnt) AS Pers_tot_exo,
SUM(Pers_tcn_cnt) AS Pers_tot_tcn
FROM #resultset) AS RT ' +
N' ORDER BY R.group1, R.group2, R.group3 '

-----------------------------------------------------------------
[Part II]
SELECT @sqlcmde = N'SELECT R.*, '+
N' RE.tot_totals,RE.Engt_tot_exp,RE.Engt_tot_exo, RE.Engt_tot_tcn,' +
N' r1.code_desc AS group1_desc '
IF @grp2 <> '' SET @sqlcmde = @sqlcmde + ', r2.code_desc AS group2_desc ' + @forPaginate
IF @grp3 <> '' SET @sqlcmde = @sqlcmde + ', r3.code_desc AS group3_desc '
SET @sqlcmde = @sqlcmde + ' FROM #resultsetengage AS R ' +
N' LEFT JOIN dbo.fn_SelRef('''+@userid+''','''+@grp1+''') AS r1 ON R.group1 = r1.code '
IF @grp2 <> '' SET @sqlcmde = @sqlcmde + ' LEFT JOIN dbo.fn_SelRef('''+@userid+''','''+@grp2+''') AS r2 ON R.group2 = r2.code '
IF @grp3 <> '' SET @sqlcmde = @sqlcmde + ' LEFT JOIN dbo.fn_SelRef('''+@userid+''','''+@grp3+''') AS r3 ON R.group3 = r3.code '
SET @sqlcmde = @sqlcmde + ', (SELECT SUM(totals_cnt) as tot_totals,SUM(Engt_exp_cnt) as Engt_tot_exp,
SUM(Engt_exo_cnt) AS Engt_tot_exo,
SUM(Engt_tcn_cnt) AS Engt_tot_tcn
FROM #resultsetengage) AS RE ' +
N' ORDER BY R.group1, R.group2, R.group3 '
/* LAST, Return the bloody result */
EXEC(@sqlcmd)
EXEC(@sqlcmde)
RETURN
***************************************************************

mkarthikcse · Answer 9 · 2009-12-08T23:04:00

Hi,

so u should create single temp table,in that u specify all the fields u want
like
create table #temp
(
col1 varchar(50) null,
col2 varchar(50) null,
col3 varchar(50) null,
col4 varchar(50) null,
)

first u insert first table values whatever it've,
like
insert into #temp(col1,col2,col3,col4)
select val1,val2,null,null from firsttable
where some condition
then null values will be added in col3 and col4
then afterwards
update #temp set col3=val3,col4=val4
from secondtable
where some condition...
is it ok...

Insert into a single temporary table values from multiple queries

9 solutions

Solution 2

Solution 1

Solution 3

Solution 4

Solution 5

Solution 7

Solution 9

Solution 6

Solution 8

Add your solution here

Preview 0