Creating a Stroed Procedure from an Access SQL Statement 1

[deharris2003]

I have experience with VB and a little SQL but I have very little experience with Stored Procedures. I have a query in MS Access that formats information from one SQL table to another SQL table. Problem is that I am working with about 300,000 records in the original table. I know that if I can convert it to a stored procedure that the process would be faster. Problem is I do not know how to convert it. Can anyone help me, show me where I can get material that will assist me in creating this sp. Time is limited and any help is much appreciated. If anyone is interested here is my SQL statement from Access.

INSERT INTO dbo_media ( Mem_Id, PT_Name, Address, DOB, Sex, Phone, Zip, Source, E_DT, T_DT )
SELECT dbo_Aetna.Member_Number, TrimName(RTrim([dbo_Aetna]![First_Name]) & &quot; &quot; & RTrim([dbo_Aetna]![Mid_Initial]) & &quot; &quot; & RTrim([dbo_Aetna]![Last_Name])) AS PT_Name, RTrim([dbo_Aetna]![Address1]) & &quot; &quot; & RTrim([dbo_Aetna]![Address2]) AS Address, dbo_Aetna.Date_Of_Birth, dbo_Aetna.Sex, IIf([dbo_Aetna]![Phone_Number] Is Null,&quot;&quot;,RTrim([dbo_Aetna]![Area_Code]) & &quot;-&quot; & Left([dbo_Aetna]![Phone_Number],3) & &quot;-&quot; & RTrim(Right([dbo_Aetna]![Phone_Number],4))) AS Phone, Left([dbo_Aetna]![Zip],5) & &quot;-&quot; & Right([dbo_Aetna]![Zip],5) AS Expr1, &quot;184&quot; AS Source, IIf([dbo_Aetna]![Group_Effective_Date]>[dbo_Aetna]![PCP_Effective_Date_Change],[dbo_Aetna]![Group_Effective_Date],[dbo_Aetna]![PCP_Effective_Date_Change]) AS Expr2, IIf([dbo_Aetna]![Group_Termination_Date]<[dbo_Aetna]![PCP_Termination_Date] Or [dbo_Aetna]![PCP_Termination_Date] Is Null,[dbo_Aetna]![Group_Termination_Date],[dbo_Aetna]![Group_Termination_Date]) AS Expr3
FROM dbo_Aetna
GROUP BY dbo_Aetna.Member_Number, TrimName(RTrim([dbo_Aetna]![First_Name]) & &quot; &quot; & RTrim([dbo_Aetna]![Mid_Initial]) & &quot; &quot; & RTrim([dbo_Aetna]![Last_Name])), RTrim([dbo_Aetna]![Address1]) & &quot; &quot; & RTrim([dbo_Aetna]![Address2]), dbo_Aetna.Date_Of_Birth, dbo_Aetna.Sex, IIf([dbo_Aetna]![Phone_Number] Is Null,&quot;&quot;,RTrim([dbo_Aetna]![Area_Code]) & &quot;-&quot; & Left([dbo_Aetna]![Phone_Number],3) & &quot;-&quot; & RTrim(Right([dbo_Aetna]![Phone_Number],4))), Left([dbo_Aetna]![Zip],5) & &quot;-&quot; & Right([dbo_Aetna]![Zip],5), &quot;184&quot;, IIf([dbo_Aetna]![Group_Effective_Date]>[dbo_Aetna]![PCP_Effective_Date_Change],[dbo_Aetna]![Group_Effective_Date],[dbo_Aetna]![PCP_Effective_Date_Change]), IIf([dbo_Aetna]![Group_Termination_Date]<[dbo_Aetna]![PCP_Termination_Date] Or [dbo_Aetna]![PCP_Termination_Date] Is Null,[dbo_Aetna]![Group_Termination_Date],[dbo_Aetna]![Group_Termination_Date]);

 

[SQLSister]

Right off one thing I see is that the concatenation operator in T-Sql is the + sign not the &. Use Case instead of IIF. ANd the structure [dbo_Aetna]![Group_Termination_Date] should be [dbo_Aetna].[Group_Termination_Date] if this is referring to a field in a table. If you are refering to a field on a form, then you need to identify it as an input variable (VAriable names begin with the @ sign) and then pass the value form the form to the input variable at the time you execute the stored procedure from the user interface.