Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

For loop in sql server Expand / Collapse
Author
Message
Posted Tuesday, June 24, 2008 9:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:16 PM
Points: 146, Visits: 459
pduplessis (6/23/2008)
Venki,

If your info is not sensitive, post your code and .dtsx file, and we will see what we can do.
ESPECIALLY, since you have SSIS, cursors should be forbidden

~PD


OK I will send it. I will send the stored procedure in document. There is nothing in the dtsx file. only one ExecuteSQL task. In that I have given a query as EXEC sp_venkiSP.

If it is possible to delete then immediatly delete this.


Thanks a lot,
Venki
Post #522626
Posted Wednesday, June 25, 2008 6:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:16 PM
Points: 146, Visits: 459
Pduplessis,

Have you checked my Stored Procedure?


Thanks a lot,
Venki
Post #523224
Posted Wednesday, June 25, 2008 6:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 35,342, Visits: 31,876
That's what happens when you "send" it to just one person, Venki... why don't you post the procedure here so we can all lend a hand... ;)

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #523230
Posted Wednesday, June 25, 2008 7:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:16 PM
Points: 146, Visits: 459
Hi,

Your suggestions are always welcome. No body responded for this one yesterday that's why I deleted it.

I have kept it for halfen hour and taken back. Now I am keeping again and will not delete it.

In the execute SQL task nothing is there. I have given only
EXEC sp_TransferIH


Thanks a lot,
Venki
Post #523259
Posted Wednesday, June 25, 2008 7:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 35,342, Visits: 31,876
Wow... lots of RBAR there :D

For starters, you can replace the following WHILE loop...

declare @cnt int
declare @UnitCount int
set @cnt=1
set @UnitCount=1
while @cnt<len(@s_Table_PkgNumber)
begin
if(substring(@s_Table_PkgNumber,@cnt,1)=', ')
begin
set @UnitCount=@UnitCount+1
end
set @cnt=@cnt+1
End

... with this...

SET @UnitCount = LEN(@s_Table_PkgNumber)-LEN(REPLACE(@s_Table_PkgNumber,',',''))+1

That one is really important because once you've fixed that, column can be produced just using a simple select which means you can convert the Cursor into a simple SELECT. That means that you can very easily do INSERT/SELECTS instead of using a cursor and things will be very much faster.

The following...
	if @s_Quote_Type=' '
set @s_Quote_Type=Null

... can simply be replace by ...
SET @s_Quote_Type = NULLIF(@s_Quote_Type,' ')

I'm on my way to work and I'll try to take a look at this in more detail tonight. In the mean time... experiment with the code suggestions I've made so far...



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #523298
Posted Wednesday, June 25, 2008 7:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400

WHOA!!!! I wanted to reduce font

Eishhhhh, sorry everyone



Delete Statement in more readable format:

DELETE CSInquiry_Test.dbo.InquiryHeader WHERE Inquiry_Number in (
Select Test_ESS.Inquiry_Number
FROM T5_Reporting_FSUser.Test_ESS Test_ESS Left Join dbo.SiteInfoForm ON Test_ESS.ESS_Site_ID = dbo.SiteInfoForm.Cust_ID
Where Test_ESS.ESS_LNDtTimestamp >= Dateadd(dd,-10,GetDate())
)

Select statement in more readable format:

SELECT
Test_ESS.Inquiry_Number, Test_ESS.ESS_CreationDate, Test_ESS.Probability_Rating,Test_ESS.Originator_Name, dbo.SiteInfoForm.Cust_ID,
NULLIF(Test_ESS.Quote_Type,' ') as Quote_Tpe,Test_ESS.ESS_Site_ID, Test_ESS.Proposal_NeedDate, Test_ESS.Estimated_BookDate,
Test_ESS.Cust_ExWorksDeliveryDt, Test_ESS.Table_PkgNumber, Test_ESS.Previously_Quoted,dbo.SiteInfoForm.Cust_Loc_Type,
dbo.SiteInfoForm.Cust_Elev_Value, dbo.SiteInfoForm.Cust_Elev_Unit, dbo.SiteInfoForm.Cust_Tem_Des, dbo.SiteInfoForm.Cust_Tem_Unit,
dbo.SiteInfoForm.Cust_Tem_Min,dbo.SiteInfoForm.Cust_Tem_Max, dbo.SiteInfoForm.Cust_Comments,Test_ESS.Driver_Change,
Test_ESS.Competion, Test_ESS.Sales_Approach, Test_ESS.Scope_Proposed_Modifications, Test_ESS.Special_Requirements,
Test_ESS.Misc_Comments, Test_ESS.ControlSystem_Electr, Test_ESS.Pneumatic_System,Test_ESS.Fuel_System, Test_ESS.Start_System,
Test_ESS.Engine_Assemblies,Test_ESS.Lube_System, Test_ESS.Vibration_System, Test_ESS.Seal_System,Test_ESS.Enclosure_System,
Test_ESS.Driven_Equipment, Test_ESS.Gear_Drives,Test_ESS.Accessories, Test_ESS.Other, Test_ESS.Engine_UpRate, Test_ESS.ReStage,
Test_ESS.Solonox, Test_ESS.Controls_Retrofit, Test_ESS.Systems_Upgrade,Test_ESS.Factory_Refurb, Test_ESS.Onsite_Refurb,
Test_ESS.Total_Order_Value, Test_ESS.Comments, Test_ESS.ESS_History, Test_ESS.ESS_Country, Test_ESS.ESS_District,
Test_ESS.ESS_Rev, Test_ESS.ESS_Status, Test_ESS.Proposal_Urgency,Test_ESS.Related_Inquiries,Test_ESS.Scope,
SUBSTRING(Test_ESS.Originator_Name,1,CHARINDEX(' ', Test_ESS.Originator_Name)-1)) FirstName
SUBSTRING(Test_ESS.Originator_Name,CHARINDEX(' ', Test_ESS.Originator_Name)+1,len(Test_ESS.Originator_Name)) LastName
LEN(Test_ESS.Table_PkgNumber)-LEN(REPLACE(Test_ESS.Table_PkgNumber,',',''))+1 as UnitCount
FROM T5_Reporting_FSUser.Test_ESS Test_ESS Left Join dbo.SiteInfoForm ON Test_ESS.ESS_Site_ID = dbo.SiteInfoForm.Cust_ID
Where Test_ESS.ESS_LNDtTimestamp >= Dateadd(dd,-10,GetDate())

Humblest apologies, me thought font size 7 would reduce things, not increase them...

~PD


Post #523330
Posted Wednesday, June 25, 2008 7:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
Now....

For the SSIS bit.

1) Create connectionmanager to your data
2) Create a preparation SQL task which basically deletes the data, as per the delete statement
3) Create a dataflow. OLEDB source to OLEDB destination. Use the select statement as your OLEDB source. Use Inquiryheader as your destination.
4) Map source to destination

TADA

~PD
Post #523333
Posted Wednesday, June 25, 2008 9:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 35,342, Visits: 31,876
pduplessis (6/25/2008)
Now....

For the SSIS bit.

1) Create connectionmanager to your data
2) Create a preparation SQL task which basically deletes the data, as per the delete statement
3) Create a dataflow. OLEDB source to OLEDB destination. Use the select statement as your OLEDB source. Use Inquiryheader as your destination.
4) Map source to destination

TADA

~PD


Nah... too much work... :P


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #523833
Posted Wednesday, June 25, 2008 9:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 35,342, Visits: 31,876
venki (6/25/2008)
Hi,

Your suggestions are always welcome. No body responded for this one yesterday that's why I deleted it.

I have kept it for halfen hour and taken back. Now I am keeping again and will not delete it.

In the execute SQL task nothing is there. I have given only
EXEC sp_TransferIH


You don't need any of that DTS or SSIS stuff... it's just gonna slow things down. Here's you monster RBAR query converted to set based. The SELECT includes the "Rosetta Stone" that identifies the column mappings.

Stored Procedure for Transfering Inqyiry Header table


ALTER PROCEDURE [T5_REPORTING_FSUSER].[SP_TransferIH]
-- Add the parameters for the stored procedure here

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

--===== Gather and preprocess all of the information that the cursor used to do
SELECT ess.Inquiry_Number AS Inquirey_Number,
ess.ESS_CreationDate AS Date,
ess.Probability_Rating AS Probability,
SUBSTRING(ess.Originator_Name,1,CHARINDEX(' ', ess.Originator_Name)-1) AS From_F,
SUBSTRING(ess.Originator_Name,CHARINDEX(' ', ess.Originator_Name)+1,LEN(ess.Originator_Name)) AS From_L,
sif.Cust_ID AS Customer,
NULLIF(ess.Quote_Type,' ') AS Budgetary_Firm,
ess.ESS_Site_ID AS Site_Location,
ess.Proposal_NeedDate AS Need_Prop_Date,
ess.Estimated_BookDate AS Booking_Date,
ess.Cust_ExWorksDeliveryDt AS Prop_Deliv_Date,
ess.Previously_Quoted AS Prev_Quoted,
CAST(NULL AS INT) AS Old_Inquiry,
LEN(ess.Table_PkgNumber)-LEN(REPLACE(ess.Table_PkgNumber,',','')) AS Number_Units,
sif.Cust_Loc_Type AS [Application],
sif.Cust_Elev_Value AS Elevation,
sif.Cust_Elev_Unit AS Elevation_Units,
sif.Cust_Tem_Des AS Amb_Temperature,
sif.Cust_Tem_Unit AS TempUnits,
sif.Cust_Tem_Min AS Min_Temp,
sif.Cust_Tem_Max AS Max_Temp,
CAST(NULL TINYINT) AS Relative_Humidity,
sif.Cust_Comments AS Site_Notes,
ess.Driver_Change AS Initiated_By,
ess.Competion AS Competition,
ess.Sales_Approach AS Sales_Approach,
ess.Scope_Proposed_Modifications AS Proposed_Mods,
CAST(NULL AS VARCHAR) AS Preferred_Options,
ess.Special_Requirements AS Special_Requirements,
CAST(NULL AS VARCHAR) AS Expectations,
ess.Misc_Comments AS Notes,
ess.ControlSystem_Electr AS PS_Control,
ess.Pneumatic_System AS PS_Pneumatic,
ess.Fuel_System AS PS_Fuel,ess.Start_System AS PS_Start,
ess.Engine_Assemblies AS PS_EngineAssemblies,
ess.Lube_System AS PS_Lube,
ess.Vibration_System AS PS_Vibration,
ess.Seal_System AS PS_Seal,
ess.Enclosure_System AS PS_Enclosure,
ess.Driven_Equipment AS PS_Driven,
ess.Gear_Drives AS PS_GearDrives,
ess.Accessories AS PS_Accessories,
ess.Other AS PS_Other,
ess.Engine_UpRate AS Engine_Uprate,
ess.ReStage AS Restage,
ess.Solonox AS Solonox,
ess.Controls_Retrofit AS Controls_Retrofit,
ess.Systems_Upgrade AS System_Upgrade,
ess.Factory_Refurb AS Refurbishment,
ess.Onsite_Refurb AS OnSiteRefurb,
'FALSE' AS fFastTrack,
'FALSE' AS bPostBookedFTSolution,
ess.Total_Order_Value AS Total_Order_Value,
ess.Comments AS Comments,
ess.ESS_History AS ESS_History,
ess.ESS_Country AS ESS_Country,
ess.ESS_District AS ESS_District,
ess.ESS_Rev AS ESS_Rev,
ess.ESS_Status AS ESS_Status,
ess.Proposal_Urgency AS Proposal_Urgency,
ess.Related_Inquiries AS Related_Inquiries,
ess.Scope AS Scope
INTO #MyHead
FROM T5_Reporting_FSUser.Test_ESS ess
LEFT JOIN dbo.SiteInfoForm sif ON ess.ESS_Site_ID = sif.Cust_ID
WHERE ess.ESS_LNDtTimestamp >= Dateadd(dd,-10,GetDate())

--===== Delete any rows that match the new information
DELETE CSInquiry_Test.dbo.InquiryHeader
FROM CSInquiry_Test.dbo.InquiryHeader ih
INNER JOIN #MyHead t
ON ih.Inquiry_Number = t.Inquiry_Number

--===== Add all the new information. Some will replace the deleted rows,
-- some will be new rows
INSERT INTO CSInquiry_Test.dbo.Inquiryheader(
Inquiry_Number, Date, Probability, From_F, From_L,
Customer, Budgetary_Firm, Site_Location, Need_Prop_Date, Booking_Date,
Prop_Deliv_Date, Prev_Quoted, Old_Inquiry, Number_Units, [Application], Elevation,
Elevation_Units, Amb_Temperature, Temp_Units, Min_Temp, Max_Temp, Relative_Humidity,
Site_Notes, Initiated_By, Competition, Sales_Approach, Proposed_Mods, Preferred_Options,
Special_Requirements, Expectations, Notes, PS_Control, PS_Pneumatic, PS_Fuel,
PS_Start, PS_EngineAssemblies, PS_Lube, PS_Vibration, PS_Seal, PS_Enclosure,
PS_Driven, PS_GearDrives, PS_Accessories, PS_Other, Engine_Uprate, Restage,
Solonox, Controls_Retrofit, System_Upgrade, Refurbishment, OnSiteRefurb,
fFastTrack, bPostBookedFTSolution, Total_Order_Value, Comments, ESS_History,
ESS_Country, ESS_District, ESS_Rev, ESS_Status, Proposal_Urgency,
Related_Inquiries, Scope)
SELECT Inquiry_Number, Date, Probability, From_F, From_L,
Customer, Budgetary_Firm, Site_Location, Need_Prop_Date, Booking_Date,
Prop_Deliv_Date, Prev_Quoted, Old_Inquiry, Number_Units, [Application], Elevation,
Elevation_Units, Amb_Temperature, Temp_Units, Min_Temp, Max_Temp, Relative_Humidity,
Site_Notes, Initiated_By, Competition, Sales_Approach, Proposed_Mods, Preferred_Options,
Special_Requirements, Expectations, Notes, PS_Control, PS_Pneumatic, PS_Fuel,
PS_Start, PS_EngineAssemblies, PS_Lube, PS_Vibration, PS_Seal, PS_Enclosure,
PS_Driven, PS_GearDrives, PS_Accessories, PS_Other, Engine_Uprate, Restage,
Solonox, Controls_Retrofit, System_Upgrade, Refurbishment, OnSiteRefurb,
fFastTrack, bPostBookedFTSolution, Total_Order_Value, Comments, ESS_History,
ESS_Country, ESS_District, ESS_Rev, ESS_Status, Proposal_Urgency,
Related_Inquiries, Scope
FROM #MyHead



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #523834
Posted Thursday, June 26, 2008 12:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
:D

Awesome Jeff...
As for the SSIS bit, in this case I would have stayed away, no need, but Venki specifically asked how to do this in SSIS (call a SP from his package), which I am trying to get him to avoid. I get the feeling he actually writes the data to test_ESS with SSIS.

Rgs
~PD
Post #523898
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse