VBA to SQL code migration - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

VBA to SQL code migration

Author

Topic

jwmayo
Starting Member

28 Posts

Posted - 2010-08-12 : 15:10:51

Ok, Im new to SQL and havent had much training. I have a looping update program that I've been using in Access for a while. Something created by an intern. I want to put this same type of procedure in
SQL. Simply it updates one table with information from another.
It will be part of a bigger process of updates to a file. Where is the best place for this to go in SQL. T-SQL? And can someone maybe help me with how this needs to be altered to work in SQL. Code is below. Thanks

Dim DB
Dim RS
Dim RSITEM
Set DB = CurrentDb
Set RS = DB.openrecordset("Releaseschedule")
Set RSITEM = DB.openrecordset("ITEMMASTER")

Do Until RSITEM.EOF
pubdate = RSITEM!newdate
releasenew = RSITEM!releasenew
currentitem = RSITEM!ISBN
Do Until RS.EOF
With RS
pubstart = RS!start
Pubend = RS!End
Releasemth = RS!Releasemonth

If currentitem = lastitem Then
If pubdate >= pubstart Then
If pubdate <= Pubend Then
With RSITEM
.Edit
!releasenew = Releasemth
.Update

End With
With RS
.MoveFirst
End With
GoTo lastline
lastitem = currentitem
Else
RS.MoveNext
End If
Else
RS.MoveNext
End If
Else
lastitem = currentitem
'GoTo lastline

End If

End With

Loop

DoEvents
RSITEM.MoveNext
lastline:
lastitem = currentitem
RSITEM.MoveNext
Loop
If lastitem <> "" Then

End If

DB.Close
'lcprogress.Visible = False
MsgBox "Done"
End Sub

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 15:21:26

Can you explain in words, what is happening in this VBA code. That'll help you get a quicker solution.

jwmayo
Starting Member

28 Posts

Posted - 2010-08-12 : 15:23:57

Yes sorry. New to this.
There is a date in the itemmaster file and there is a range of dates in the releasemonth file. It basically takes the date in the itemmaster and cycles through the releaseschedule file to see what range it fits in and then gives it a code based on that range. And then updates the itemmaster file with that code.
Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 15:26:08

quote:
Originally posted by jwmayo

Yes sorry. New to this.
There is a date in the itemmaster file and there is a range of dates in the releasemonth file. It basically takes the date in the itemmaster and cycles through the releaseschedule file to see what range it fits in and then gives it a code based on that range. And then updates the itemmaster file with that code.
Thanks

Much better

.

Now we need some sample data from itemmaster and releasemonth tables. And the expected output.

Wait...did you say itemmaster file and releasemonth file? These are not SQL Server Tables?

jwmayo
Starting Member

28 Posts

Posted - 2010-08-12 : 15:31:08

Itemmaster File
Code: Blank
Item= xxx
date= 1/17/2010

Releaseschedule File
Code Start Date End
CA 1/1/2010 1/10/2010
CB 1/11/2010 1/20/2010

Results
Itemmaster
Code = CB
Item = XXX
Date = 1/17/2010

jwmayo
Starting Member

28 Posts

Posted - 2010-08-12 : 15:40:55

Yes they are SQL tables.. Sorry didnt see that question

jwmayo
Starting Member

28 Posts

Posted - 2010-08-12 : 15:55:15

Did that help or am I still not making things clear

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 15:58:31

--Some Sample data that I built from your example

declare @Itemmaster table (Code varchar(10), Item varchar(10), Date datetime)
insert @Itemmaster
select null,'xxx','1/17/2010'
union all select null,'yyy','1/9/2010'

declare @Releaseschedule table (Code varchar(10), StartDate datetime, EndDate datetime)
insert @Releaseschedule
select 'CA', '1/1/2010', '1/10/2010'
union all select 'CB', '1/11/2010', '1/20/2010'

--Query to update Itemmaster 
update b
set b.Code = a.Code
from @Releaseschedule a 
inner join @Itemmaster b on b.Date between a.StartDate and a.EndDate

--Checking Itemmaster again to see if update worked
select * from @Itemmaster

--Result After Update
Code       Item       Date
---------- ---------- -----------------------
CB         xxx        2010-01-17 00:00:00.000
CA         yyy        2010-01-09 00:00:00.000

jwmayo
Starting Member

28 Posts

Posted - 2010-08-12 : 16:00:16

Wow thanks.. Now where is the best place for this code to reside. T-SQL

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 16:07:52

Yes. What I have provided is T-SQL.

jwmayo
Starting Member

28 Posts

Posted - 2010-08-12 : 16:08:42

Thank you very much!

jwmayo
Starting Member

28 Posts

Posted - 2010-08-12 : 22:52:21

Im confused about the following part:
select null,'xxx','1/17/2010'
union all select null,'yyy','1/9/2010'
I need to cycle through every record in the item master and update each record based onthe release schedule file.
Also confused on this part:
select 'CA', '1/1/2010', '1/10/2010'
union all select 'CB', '1/11/2010', '1/20/2010'

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-13 : 10:02:25

That is just sample data I have built to create sample tables @Itemmaster and @Releaseschedule.

I need a way to test my query as I dont have access to your data. You just need to execute the query alone. (Of course, after replacing the column names with the actual column names, and the table names with the actual table names).

Also, since this is an update, you may want to run it first in a test environment before running it in production.

jwmayo
Starting Member

28 Posts

Posted - 2010-08-13 : 10:53:00

Im using the below:
declare @Itemmaster table (RELMO2 varchar(10), ISBN varchar(10), pubDate datetime)

declare @Releaseschedule table (Releasemonth varchar(10), Start datetime, End datetime)

update b
set b.relmo2 = a.releasemonth
from @Releaseschedule a
inner join @[Item master] b on b.pubDate between a.Start and a.End

Error: Incorrect syntax near the keyword 'End'. [SQLSTATE 42000] (Error 156) Must declare the table variable "@". [SQLSTATE 42000] (Error 1087). The step failed.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-13 : 10:58:17

quote:
Originally posted by jwmayo

Im using the below:
declare @Itemmaster table (RELMO2 varchar(10), ISBN varchar(10), pubDate datetime)

declare @Releaseschedule table (Releasemonth varchar(10), Start datetime, End datetime)

update b
set b.relmo2 = a.releasemonth
from @Releaseschedule a
inner join @[Item master] b on b.pubDate between a.Start and a.End

Error: Incorrect syntax near the keyword 'End'. [SQLSTATE 42000] (Error 156) Must declare the table variable "@". [SQLSTATE 42000] (Error 1087). The step failed.

You don't need to declare those tables (@ - is used to define variables). I have used table variables to test my solution. Since..you already have those tables existing..All you need is this.

update b
set b.RELMO2 = a.Releasemonth 
from Releaseschedule a 
inner join Itemmaster b on b.pubDate between a.Start and a.End

jwmayo
Starting Member

28 Posts

Posted - 2010-08-13 : 11:05:33

Ya got that. Now just running:
update b
set b.relmo2 = a.releasemonth
from @releaseschedule a
inner join @[Item master] b on b.pubDate between a.Start and a.End

Error: Must declare the table variable "@releaseschedule". [SQLSTATE 42000] (Error 1087). The step failed.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-13 : 11:07:09

quote:
Originally posted by jwmayo

Ya got that. Now just running:
update b
set b.relmo2 = a.releasemonth
from @releaseschedule a
inner join @[Item master] b on b.pubDate between a.Start and a.End

Error: Must declare the table variable "@releaseschedule". [SQLSTATE 42000] (Error 1087). The step failed.

Please refer to the query in my previous post. I have removed the '@' symbols from the table names. Like I already said...'@' is used to declare variables. Please remove the '@' and use actual table names.

jwmayo
Starting Member

28 Posts

Posted - 2010-08-13 : 11:08:41

So sorry.. Not sure what I was thinking

jwmayo
Starting Member

28 Posts

Posted - 2010-08-13 : 11:33:27

It worked perfectly. Thank you very much

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-13 : 11:34:37