Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


For loop in sql server


For loop in sql server

Author
Message
VRT
VRT
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 603
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.

Thank You
VRT
VRT
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 603
Pduplessis,

Have you checked my Stored Procedure?

Thank You
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44968 Visits: 39862
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... Wink

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
VRT
VRT
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 603
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

Thank You
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44968 Visits: 39862
Wow... lots of RBAR there BigGrin

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pduplessis-723389
pduplessis-723389
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1263 Visits: 400
WHOA!!!! I wanted to reduce font

Eishhhhh, sorry everyone

Blush

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())
   Wink

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
pduplessis-723389
pduplessis-723389
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1263 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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44968 Visits: 39862
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... Tongue

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44968 Visits: 39862
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pduplessis-723389
pduplessis-723389
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1263 Visits: 400
BigGrin

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search