For loop in sql server

Question

Post reply

For loop in sql server

Viewing 15 posts - 16 through 30 (of 40 total)

You must be logged in to reply to this topic. Login to reply

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 1

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

)

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 SSChampion Points: 10599 More actions · Answer 2

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 SSC Guru Points: 1003869 More actions · Answer 3

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... 😛

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

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,