November 6, 2019 at 9:57 pm
Is there any better way to do this?
create table #student
(StudentName varchar(20) null,
StdId int not null,
Stdgrade varchar(10) null)
insert into #student
(StudentName ,
StdId ,
Stdgrade )
select std.Name
,std.StdID
,dp.stdgrade
from dbo.student std
join dbo.department dp
on std.stdid = dp.id
where dp.isactive = 1
insert into #student
(StudentName ,
StdId ,
Stdgrade )
select std.Name
,std.StdID
,dp.stdgrade
from dbo.studentbook std
join dbo.departmentstore dp
on std.stdid = dp.id
where dp.isactive = 2 and sdt.isactive = -1
insert into #student
(StudentName ,
StdId ,
Stdgrade )
select std.Name
,std.StdID
,dp.stdgrade
from dbo.studentarchive std
join dbo.departmenthistory dp
on std.stdid = dp.id
where dp.isactive = 3 and sdt.isactive = -2
----Inserrt all these temp table data into main
Insert into dbo.StdActiveTable
(StudentName ,
StdId ,
Stdgrade )
select
StudentName ,
StdId ,
Stdgrade
from #student
problem is when i call the procedure in SSIS package, i am getting error
The metadata could not be determined because statement uses a temp table..
November 6, 2019 at 10:00 pm
Insert into dbo.StdActiveTable
(StudentName ,
StdId ,
Stdgrade )
select std.Name
,std.StdID
,dp.stdgrade
from dbo.student std
join dbo.department dp
on std.stdid = dp.id
where (dp.isactive = 1) or
(dp.isactive = 2 and sdt.isactive = -1) or
(dp.isactive = 3 and sdt.isactive = -2)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 6, 2019 at 10:09 pm
Reformatting the original query - I was able to reduce your individual steps to this:
Select std.Name
, std.StdID
, dp.stdgrade
From dbo.student std
Join dbo.department dp On std.stdid = dp.id
Where dp.isactive = 1
Or (
sdt.isactive = -2
And dp.isactive In (2, 3)
);
Since this can be done in a single query - there is no reason to actually insert this data into a table for later processing. With that said - if the goal is to have a process that rebuilds the 'main' table on a schedule and outputs that data to another source - then you can add the insert to this...however, I would recommend that you put all of that in a try/catch with an explicit transaction so it can be rolled back in case of failure - that way, if it fails it will roll back to the previous state and the table will not be empty.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 6, 2019 at 10:16 pm
problem is each select statement is coming from different tables. first select statement is form student table, second one is form studentbook and 3rd is from studentarchive.
all tables has different data but columns selecting is same
November 6, 2019 at 10:32 pm
Oops.
You still don't need a temp table. Instead:
INSERT INTO ...
SELECT ...
UNION
SELECT ...
UNION
SELECT ...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 6, 2019 at 10:39 pm
problem is each select statement is coming from different tables. first select statement is form student table, second one is form studentbook and 3rd is from studentarchive.
all tables has different data but columns selecting is same
I did not catch that - but Scott provided the answer for SQL. Since this is for SSIS there are other options depending on what you are doing with the data. For example - you could use a UNION in SSIS to combine the data from 3 separate sources, or a MERGE in SSIS - or separate data flows for each...again, it all depends on the desired end result and where the data is being sent.
Not saying this should all be done in SSIS - but if this is just a sample of the output and process it could be a better option. If this is the actual result you need then the UNION in SQL will be the better option.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 6, 2019 at 10:50 pm
When you use SSIS to call procs which return data from one or more temp tables, you need to add a WITH RESULT SETS definition to your EXEC PROC call, to tell SSIS about the structure of the data which is being returned.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply