How do I do this in SQL Server please........
-
Ok, I have a module in Access that cycles through a loop 'x' No of times, rewriting and executing a piece of SQL. What I would really like is for this to run on the server in a stored procedure. But alas the correct syntax and structure escapes me.
Here's the VB
'------------------------------------------------
Sub MdlFitterDetails()
Dim IntCertNo As Integer
Dim MyTableClear As String
Dim MySql As String
Dim SqlAudIdx As String
Dim sqlprojName As String
'turn of warnings
DoCmd.SetWarnings False
'clear destination table
MyTableClear = "delete * from dbo_GR_tbl_FitterQuals"
DoCmd.RunSQL (MyTableClear)
IntCertNo = 1
'write fitter details where available
For x = 1 To 11 'covers all ten types with one spare at time of writing
'build statement
MySql = "INSERT INTO dbo_GR_tbl_FitterQuals "
MySql = MySql & "SELECT dbo_GR_tbl_Register.FitterFirstNm, dbo_GR_tbl_Register.FitterLastNm, dbo_GR_tbl_Register.CORGI_RegNo, dbo_GR_tbl_Register.CORGI_cardSNo, dbo_GR_tbl_Register.CORGI_ExpiryDt, dbo_GR_tbl_Register.CertType" & IntCertNo & " AS Certificate, dbo_GR_tbl_Register.Expiry" & IntCertNo & " AS Expiry FROM dbo_GR_tbl_Register WHERE (((dbo_GR_tbl_Register.certtype" & IntCertNo & ") is not null) AND nolongerreq =0);"
'increment counter for next loop
If x < 11 Then
'Debug.Print IntCertNo
IntCertNo = IntCertNo + 1
Else
End If
'execute sql statement
'MsgBox MySql
DoCmd.RunSQL (MySql)
'round we go again
Next
'warnings back on
DoCmd.SetWarnings True
'job done
End Sub
'--------------------------------------------
So it does CertType1 and Expiry1, then CertType2 and Expiry2 ..... etc ....
Im sure that some of you are looking at this and thinking .... yeah its easy you n00b
.. but, please help. This is driving me nuts!
thnx
-
Fisrt things First. You should look into changing such design ( if you can of course) Because it complicates all operations and makes possible a ton of inconsistensies!!
Second. Well, You can use Dynamic sql to accomplish all that. Which is very similar to what you have! It would have been nice to have the definition of the table :dbo_GR_tbl_FitterQuals though
here is a go:
create procedure MdlFitterDetails
as
begin
declare @STR varchar(8000)
set @STR = 'INSERT INTO dbo_GR_tbl_FitterQuals '
select @STR = @STR + 'SELECT dbo_GR_tbl_Register.FitterFirstNm,
dbo_GR_tbl_Register.FitterLastNm,
dbo_GR_tbl_Register.CORGI_RegNo,
dbo_GR_tbl_Register.CORGI_cardSNo,
dbo_GR_tbl_Register.CORGI_ExpiryDt,
dbo_GR_tbl_Register.CertType'+ cast(number as varchar(2)) +' AS Certificate,
dbo_GR_tbl_Register.Expiry'+ cast(number as varchar(2)) +' AS Expiry
FROM dbo_GR_tbl_Register
WHERE (((dbo_GR_tbl_Register.certtype'+ cast(number as varchar(2)) +') is not null)
AND nolongerreq =0);' + char(13)+ Char(10) + (case when number <10 then 'union all ' + char(13)+ Char(10) else '' end)
from master..spt_values where number between 1 and 10 and type = 'P'
-- execute now
delete from dbo_GR_tbl_FitterQuals
exec ( @STR )
end
* Noel -
Here's another...
Create Procedure myProc
AS
Declare @IntCertNo As Int
--first clear table
BEGIN
delete from dbo_GR_tbl_FitterQuals
END
Set @IntCertNo = 1 --initialise
--write fitter details where available
While @IntCertNo < 11 --covers all ten types with one spare at time of writing
BEGIN
--build statement
EXEC ('INSERT INTO dbo_GR_tbl_FitterQuals
SELECT dbo_GR_tbl_Register.FitterFirstNm,
dbo_GR_tbl_Register.FitterLastNm,
dbo_GR_tbl_Register.CORGI_RegNo,
dbo_GR_tbl_Register.CORGI_cardSNo,
dbo_GR_tbl_Register.CORGI_ExpiryDt,
dbo_GR_tbl_Register.CertType' + @IntCertNo + ' AS Certificate,
dbo_GR_tbl_Register.Expiry' + @IntCertNo + ' AS Expiry
FROM
dbo_GR_tbl_Register
WHERE (dbo_GR_tbl_Register.certtype' + @IntCertNo + ' is not null AND nolongerreq =0)')
Set @IntCertNo = @IntCertNo + 1--increment counter
END
**ASCII stupid question, get a stupid ANSI !!!** -
Brilliant !
Thanks for the quick response. I am now back home (could remote access but, contrary to my employers beliefs, I have a life) I will test this soon and post back here when I'm done.
Thank you.
-
Hi Folks,
I went with the second example, and after a few minor changes had it up and running in about 5 mins.
Here is what I ended up with......
------------------------------
CREATE Procedure sp_GR_Quals
AS
Declare @IntCertNo As Int
--first clear table
BEGIN
delete from GR_tbl_FitterQuals
END
Set @IntCertNo = 1 --initialise
--write fitter details where available
While @IntCertNo < 11 --covers all ten types with one spare at time of writing
BEGIN
--build statement
EXEC ('INSERT INTO [Innovations].[dbo].[GR_tbl_FitterQuals]([FitterFirstNm], [FitterLastNm], [CORGI_RegNo], [CORGI_cardSNo], [CORGI_ExpiryDt], [Certificate], [Expiry] )
SELECT GR_tbl_Register.FitterFirstNm,
GR_tbl_Register.FitterLastNm,
GR_tbl_Register.CORGI_RegNo,
GR_tbl_Register.CORGI_cardSNo,
GR_tbl_Register.CORGI_ExpiryDt,
GR_tbl_Register.CertType' + @IntCertNo + ' AS Certificate,
GR_tbl_Register.Expiry' + @IntCertNo + ' AS Expiry
FROM
GR_tbl_Register
WHERE (GR_tbl_Register.certtype' + @IntCertNo + ' is not null AND nolongerreq =0)')
Set @IntCertNo = @IntCertNo + 1 --increment counter
END
GO
------------------------------------
Thanks again for your speedy response, much appreciated.
-
oh good - always nice to get feedback!
as for "speedy response" - I must tell you that on this site people "fight" with each other to get their responses in first...so you picked a good one!
**ASCII stupid question, get a stupid ANSI !!!** -
With Black eye, and scars to prove it
-
Yeah, he picked the slower one
<pun intended> 
* Noel -
Well this is really really gonna upset you then. Because I picked the one i felt had paid the closest attention to the original.
/me hides in the corner and waits fer the fight to start
-
Tony,
I was just joking with sushila! You are free to choose which ever script you like best ..... for whatever reason
* Noel -
Awwww, thats no fun .... where's the flying fists?
Seriously tho, thanks to both of you. Following the posts after my feedback, I was just trying to stir up a bit of fun.
-
OK here you go:
* Noel -
what a shame work kept me from jumping in on the fray...who knows Tony - you might have even seen some blood....
noel - I would NEVER fight with you - am learning too much from you to do that...I also sent you a pm about another soul that needs rescuing from the razor sharp tongue of J.C!
- you have now become the official "knight in shining armour" of this site...
**ASCII stupid question, get a stupid ANSI !!!** -
you have now become the official "knight in shining armour" of this site...
I don't mind telling the truth to anyone that deserve it but "some people"
are just plain deaf ( or play it like that )
* Noel -
TonyDA67RR has a procedure in Access and he find a problem of this, not in T-SQL syntax. So why using the CREATE PROCEDURE statement here ?
We can do these:
- In MySql string value, leave out the ";" in the end
- Let change DoCmd.RunSQL (MySql) with CurrentDB.Execute MySql
- And remove DoCmd.SetWarning's
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply