Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

Lots of questions come up in the SQL Team forums about conversions between Access and T-SQL and some of the differences between the two SQL dialects.  Here's a few handy things to help you out with converting your projects.  Check in now and then as this short list will eventually grow as more things come up.

Converting NULL values


Access:        NZ(Value, ValueToReturnIfNull)
T-SQL:         COALESCE(Value, ValueToReturnIfNull) -- or --  ISNULL(Value, ValueToReturnIfNull)

Checking for NULLs

Access:      WHERE Value IS NULL   -- or --  WHERE ISNULL(Value)   (note the difference from T-SQL's ISNULL)
T-SQL:       WHERE Value IS NULL
       
String Segments

Access:       MID(StringVal, StartPos, [length] )   (length is optional)
T-SQL:        SUBSTRING(StringVal, StartPos, length )    (length is required!)

Finding a String within a String

Access:     SELECT INSTR(start, StringToSearch, StringToFind)
T-SQL:       SELECT CHARINDEX(start, StringToSearch, StringToFind)

Reverse a String

Access:     SELECT STRREVERSE(StringVal)
T-SQL:       SELECT REVERSE(StringVal)

Convert a String to Uppercase or Lowercase

Access:      SELECT UCASE(StringVal),  LCASE(StringVal)
T-SQL:       SELECT UPPER(StringVal), LOWER(StringVal)

Formatting Dates, Booleans, Numerics as Strings

Access:     SELECT Format(Value, FormatSpecification)  (note: this always returns a string value)
T-SQL:      Do not do this in T-SQL; format data at your front-end application or report

String Literals

Access:      SELECT "This is a string"
T-SQL:       SELECT 'This is a string'

LIKE pattern matching

matching multiple characters:

Access:      WHERE Column LIKE "*string*" 
T-SQL:       WHERE Column LIKE '%string%'

matching a single character:

Access:     WHERE Column LIKE "?string?"
T-SQL:       WHERE Column LIKE '_string_'

not matching a character or range:

Access:   WHERE Column LIKE "[!a-z]"
T-SQL:     WHERE Column LIKE '[^a-z]'

Triming White Space

Access:       TRIM(val)
T-SQL:        RTRIM(LTRIM(val))

Converting DataTypes

Access:       CINT(value),  CDBL(value), CDEC(value),  CSTR(value), CDATE(value), CBOOL(value)
T-SQL:        CONVERT(DATATYPE, value) -- or -- CAST(value AS datatype)

Conditional Expressions

Access:       IIF(Condition, ReturnIfTrue, ReturnIfValue)
T-SQL:        CASE WHEN Condition THEN ReturnIfTrue ELSE ReturnIfFalse END

Working with Date Literals

Access:      WHERE SomeDate = #1/1/2005#
T-SQL:       WHERE SomeDate = '1/1/2005'    (this is an implicit conversion from a string to a date)

Creating new Dates

Access:     DATESERIAL(year,month,date)
T-SQL:       Use the Date() function  here  -- there is no quick easy way to do this in T-SQL

Creating new Times

Access:     TIMESERIAL(Hour, minute, second)
T-SQL:       Use the Time() function here  -- there is no quick easy way to do this in T-SQL

Getting Today's Date and Time

Access:     SELECT now()
T-SQL:      SELECT getdate()

Getting Today's Date only (i.e., at midnight)

Access:     SELECT date()
T-SQL:      Use the DateOnly() function here  :  SELECT dbo.DateOnly(getdate())

Getting Today's Time Only (at the "base" date, or date with a numeric value of 0)

Access:    SELECT Time()    (this returns the time at 12/30/1899)
T-SQL:      Use the TimeOnly() function here  :  SELECT dbo.TimeOnly(getdate())  (returns the time at 1/1/1900)

Boolean (True/False) Values

Access:      WHERE Active = True  -- and --  WHERE Active = False
                    (Active is a Boolean datatype)

T-SQL:       WHERE Active=1   -- and --   WHERE Active=0
                    (Active is a Bit datatype)

Returning or Setting Boolean Values

Access:    SELECT BooleanExpression
T-SQL:      CAST(CASE WHEN BooleanExpression THEN 1 ELSE 0 END) AS BIT

FULL OUTER JOINS

(Note: try to avoid these as a general practice)

Access:      SELECT ... FROM tableA LEFT OUTER JOIN tableB ON ...
                     UNION ALL
                    SELECT ... FROM tableB LEFT OUTER JOIN tableA ON ... WHERE tableA .PK IS NULL

T-SQL:       SELECT ... FROM tableA FULL OUTER JOIN tableB ON ....

RIGHT OUTER JOINS

Because we all know that using the query designer in Access sometimes results in these, but we should never use them in manually written and maintained SQL:

Access:      SELECT ... FROM tableA RIGHT OUTER JOIN tableB ON ....
T-SQL:        SELECT ... FROM tableB LEFT OUTER JOIN tableA ON ....

Parameters

Access:      SELECT [Any column name not defined] 
T-SQL:       SELECT @ParamName

Modulo Operator

Access:     SELECT value1 MOD value2
T-SQL:      SELECT value1 % value2

Dividing Integers to calculate a Percentage or other result with decimal places

Access:    SELECT Int1 / Int2    (this returns a Double value implicitly)
T-SQL:      SELECT Int1 * 1.0 / Int2   (the multiplication by 1.0 results in a numeric(8,6) being returned)

String Concatenation Operator

Access:      Val1 & Val2  (both will be implicitly converted to strings if they are not already)
T-SQL:       Val1 + Val2 ( note that explicit conversion to a "string" datatypes is necessary in T-SQL)

Referencing an Expression in a SELECT

Here, we define A+B as a new column X, and we want to reference X in the SELECT:

Access:      SELECT A+B as X, X+C as D FROM ...
T-SQL:        SELECT X, X+C as D FROM (SELECT A+B as X, C FROM ... ) tmp

Getting a Character from an ASCII code

Access:      SELECT CHR(AsciiCode)
T-SQL:        SELECT CHAR(AsciiCode)

Getting an ASCII code from a Character

Access:      SELECT ASC(Character)
T-SQL:        SELECT ASCII(Character)

Date Part Indicators (DateAdd, DateDiff, DatePart)

MS Access and SQL Server both use the same basic date functions (DateAdd, DateDiff, DatePart) but the way you indicate which "date part" you are after differs between the two.

MS Access uses a string expression to indicate the "dart part" in DateAdd, DatePart and DateDiff expressions; SQL Server uses symbols.  Thus, you need to put quotes around the part name in MS Access since it is just a string expression, but you should NOT use quotes in SQL Server -- just enter the value directly.

The Date Part indicators are listed below:

Date Part SQL Server MS Access
Year  year, yy, yyyy "yyyy"
Quarter  quarter, qq, q "q"
Month  month, mm, m "m"
Day of Year dayofyear, dy, y "y"
Day  day, dd, d "d"
Week  week, wk, ww "ww"
Day of Week weekday, dw "w"
Hour  hour, hh "h"
Minute  minute, mi, n "n"
Second  second, ss, s "s"
Millisecond  millisecond, ms -

Finally, note that both Access and T-SQL support the Year(), Month() and Day() functions.

Print | posted on Friday, March 30, 2007 3:02 PM | Filed Under [ T-SQL MS Access ]

Feedback

Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

Good information - thanks for posting.
4/2/2007 3:44 PM | Tim Mitchell
Gravatar

# SQL Server versus Access, un petit tableau résumant les différences de syntaxes

Ayant déjà eu l'occasion de passer d'un Access à un SQL Server, réécrire les requête n'est pas simple.
4/3/2007 8:14 AM | SQL Server - Christian Robert
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

One major feature of Jet SQL lacking in T-SQL is PIVOT (crosstab) queries.
I think I saw somewhere a PIVOT clause in T-SQL but the resulting column values had to be declared explicitly, thus nullifying a big part of the interest of PIVOT queries.
Otherwise, this can be emulated with a procstock and dynamic SQL.
4/3/2007 9:01 AM | Jem
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

Hi Jem --

that's a good point, I will add something about PIVOT tables when I think of the best way to incorporate it. SQL Server 2005 does include a PIVOT operator, but as you mention, you must explicitly list the columns out. Which is actually a good thing -- your database layer should only produce resultsets with known column names and datatypes. Dynamically creating columns at your database layer in general isn't a good idea. Even in Access, the dynamic columns that are generated with a PIVOT operator are very difficult to work with, since you cannot bind reports or forms to those column names that are changing.
4/3/2007 9:35 AM | Jeff
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

Well, I'm not a big fan of hardwiring reports (especially with data-dependent columns) to datasets in general.

For example, I had to deal with an application managing server stocks.
So I had a "Platform" table containing values such as "AS 400","I386","SUN".

One typical need was to provide the spreadsheet of servers allocated by customer (rows) and by platform (columns).

So if the user was to add a new platform (and the application permitted him to do it via the client), I would have had to modify code and/or reports to hardwire the new value, compile and deploy the application.

I guess this kind of scenario can be addressed by Reporting Services, but it would have been a bit overkill for a mere two or three dynamic reports.

Another scenario where you cannot (and do not wish to) know at design time the columns returned by a query is the user defined query, designed via an Access-like interface.
4/4/2007 3:33 AM | Jem
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Conversion Guide

sir

can you please help to to select a field name, which ends with a particular word... i.e.i have to find field name ending with my predefined charater search.
4/9/2007 3:01 AM | Ashvin
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Conversion Guide

explaining the above thread

what i mean is that, i would like to have the names of all employees whose name ends with 'kumar'...how should i design the query for the above criteria
4/9/2007 4:26 AM | Ashvin
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Conversion Guide

Go To wwww.sqlteam.com/forums , sign up for an account, and ask your question there under the appropriate forums (Access or SQL Server).
4/9/2007 8:07 AM | Jeff
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Conversion Guide

I have need Microsoft Access some Query
follows the query style;

Trim
Proper
Upper
Count
Update
sum of
Criteria
4/20/2007 5:52 AM | Stephen
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Conversion Guide

Stepen -- this is just a quick reference guide. If you don't know how to program in either T-SQL or MS Access, then this is not the place to be. You should start with some good books and/or tutorials designed for beginners.

On a side note, I did just add the functions to convert lower/uppercase since they are different in T-SQL and JET SQL, so thanks for bringing that up.
4/20/2007 10:16 AM | Jeff
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Conversion Guide

I need more information about T-SQL and jet-SQL. Thanks for this nice help.
My best regards with you
4/27/2007 1:20 AM | Himanshu Kumar Pant
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Conversion Guide

Thank you!
5/3/2007 12:13 AM | Kim
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Conversion Guide

So regarding the difference in the datepart used in dateadd, datediff etc.

Is there anyway to get around this? I mean datepart is a string in access / vb so I have used a variable in this place rather than explicitly defining which datepart. so just changing to a symbol does not help.

Anyone know a nice way to do this. (short of having to make a .net / managed stored procedure instead of a straight t-sql one?

Thanks
5/11/2007 9:16 AM | Dean
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Conversion Guide

you just use case. Something like this:


case when datepart="m" then datepart(month, somedate)
when datepart="y" then datepart(year, somedate)
when datepart="d" then datepart(day, somedate) end

5/11/2007 9:20 AM | Jeff
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Conversion Guide

Thanks Jeff, I know. I was using it as a condition for a while loop. like;

WHILE DateDiff(@Interval, @IndexDate, @EndDate) >=0

Its just going to make the whole thing a bit messy. boo hoo for me ;-)
5/12/2007 6:13 AM | Dean
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Conversion Guide

Hi,
I have an Access Query that I am trying to convert to SQL. I will past the Access Query and then what I am trying in SQL. Obviously it is not parsing correctly, so any help would be appreciated.

Access query:
SELECT Trim(Left(CLIENTS.NAME,InStr(1,CLIENTS.NAME,',')-1 )) AS LASTNAME,
Trim(Mid(CLIENTS.NAME,InStr(1,CLIENTS.NAME,',')+1)) AS FIRSTNAME,
CLIENTS.NAME, CLIENTS.REFNUM AS [NUMBER],
CLIENTS.BADDR2,
CLIENTS.BADDR3,
CLIENTS.BADDR4,
CLIENTS.BADDR5,
CLIENTS.PHONE1, "" AS EXT1,
CLIENTS.PHONE2, "" AS EXT2,
CLIENTS.FAXNUM, "" AS EXT3,
CLIENTS.EMAIL,
CLIENTS.TERMS,
CLIENTS.TAXABLE
INTO AlmostFinishedClientTable
FROM CLIENTS;

SQL query:
SELECT RTrim(LTrim(CLIENTS.NAME,CharIndex(',',Client.Name,1)-1)) AS LASTNAME,
RTrim(LTrim(SubString(CLIENTS.NAME,CharIndex(',',Clients.Name,1)+1))) AS FIRSTNAME
CLIENTS.NAME, CLIENTS.REFNUM AS [NUMBER],
CLIENTS.BADDR2,
CLIENTS.BADDR3,
CLIENTS.BADDR4,
CLIENTS.BADDR5,
CLIENTS.PHONE1, '' AS EXT1,
CLIENTS.PHONE2, '' AS EXT2,
CLIENTS.FAXNUM, '' AS EXT3,
CLIENTS.EMAIL,
CLIENTS.TERMS,
CLIENTS.TAXABLE
INTO AlmostFinishedClientTable
FROM CLIENTS;

Thanks,
Harold
6/26/2007 3:46 PM | Harold Moorhead
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Conversion Guide

How can I get the time and date from the server instead of the workstation
7/23/2007 11:25 AM | Jim Giles
Gravatar

# re: Quick MS Access (JET SQL) to SQL Server (T-SQL) Conversion Guide

Hi,
I'm new to T-SQL. I usually work in Access. When I did an Access query for a particular set of data, I created a large nested IIF statement to return a "Yes" or "No" based upon two conditions, a certain code in one column and the new calculated column with a number of days. Here is the JETSQL:

SELECT dbo_qw_TUSD_StuDemogDistNoSched.stustatc, dbo_qw_TUSD_StuDemogDistNoSched.schoolc, dbo_qw_TUSD_StuDemogDistNoSched.graden, dbo_qw_TUSD_StuDemogDistNoSched.ident, dbo_qw_TUSD_StuDemogDistNoSched.lastname, dbo_qw_TUSD_StuDemogDistNoSched.firstname, dbo_qw_TUSD_StuDemogDistNoSched.middlename, dbo_zengprof.descript, dbo_status.statusvalue, dbo_status.edate, dbo_status.xdate, DateDiff("d",dbo_status.edate,Now()) AS [Time], IIf(dbo_status.statusvalue="11" And [Time]>365,"No",IIf(dbo_status.statusvalue="12" And [Time]>365,"No",IIf(dbo_status.statusvalue="15" And [Time]>365,"No",IIf(dbo_status.statusvalue="13" And [Time]>730,"No",IIf(dbo_status.statusvalue="14" And [Time]>730,"No","Yes"))))) AS Comply
FROM (dbo_qw_TUSD_StuDemogDistNoSched INNER JOIN dbo_status ON dbo_qw_TUSD_StuDemogDistNoSched.suniq = dbo_status.auniq) INNER JOIN dbo_zengprof ON dbo_status.statusvalue = dbo_zengprof.engprofc
WHERE (((dbo_qw_TUSD_StuDemogDistNoSched.stustatc)="A" Or (dbo_qw_TUSD_StuDemogDistNoSched.stustatc)="M") AND ((dbo_status.statusvalue)="11" Or (dbo_status.statusvalue)="12" Or (dbo_status.statusvalue)="13" Or (dbo_status.statusvalue)="14" Or (dbo_status.statusvalue)="15") AND ((dbo_status.xdate) Is Null))
ORDER BY dbo_qw_TUSD_StuDemogDistNoSched.schoolc, dbo_qw_TUSD_StuDemogDistNoSched.graden, dbo_qw_TUSD_StuDemogDistNoSched.lastname;

Then I went to the 2005 SQL server and tried to replicate this query as view. I managed to create the calculated field of the number of days, but could not replicate the IIF column. Below is the T-SQL:

SELECT TOP (100) PERCENT dbo.qw_TUSD_StuDemogDistNoSched.schoolc, dbo.qw_TUSD_StuDemogDistNoSched.graden,
dbo.qw_TUSD_StuDemogDistNoSched.ident, dbo.qw_TUSD_StuDemogDistNoSched.lastname, dbo.qw_TUSD_StuDemogDistNoSched.firstname,
dbo.qw_TUSD_StuDemogDistNoSched.middlename, dbo.qw_TUSD_StuDemogDistNoSched.primlangc, dbo.qw_TUSD_StuDemogDistNoSched.ethnicc,
dbo.qw_TUSD_StuDemogDistNoSched.homeaddr1, dbo.qw_TUSD_StuDemogDistNoSched.homeaddr2,
dbo.qw_TUSD_StuDemogDistNoSched.homecity, dbo.qw_TUSD_StuDemogDistNoSched.homestate, dbo.qw_TUSD_StuDemogDistNoSched.homezip,
dbo.qw_TUSD_StuDemogDistNoSched.SPCodes, dbo.status.statusvalue, dbo.status.edate, dbo.status.xdate, dbo.zengprof.descript AS EngProf,
dbo.qw_TUSD_StuDemogDistNoSched.stustatc, DATEDIFF(d, dbo.status.edate, GETDATE()) AS TimeElapsed
FROM dbo.qw_TUSD_StuDemogDistNoSched INNER JOIN
dbo.status ON dbo.qw_TUSD_StuDemogDistNoSched.suniq = dbo.status.auniq INNER JOIN
dbo.zengprof ON dbo.status.statusvalue = dbo.zengprof.engprofc
WHERE (dbo.status.statusvalue = '11' OR
dbo.status.statusvalue = '12' OR
dbo.status.statusvalue = '13' OR
dbo.status.statusvalue = '14' OR
dbo.status.statusvalue = '15') AND (dbo.qw_TUSD_StuDemogDistNoSched.stustatc = 'A' OR
dbo.qw_TUSD_StuDemogDistNoSched.stustatc = 'M') AND (dbo.status.xdate IS NULL)
ORDER BY dbo.qw_TUSD_StuDemogDistNoSched.schoolc, dbo.qw_TUSD_StuDemogDistNoSched.graden, dbo.qw_TUSD_StuDemogDistNoSched.lastname

I tried the CASE, but when I needed two conditions, it would not work. It kept telling me the "AND" was not valid. Any suggestions? It would really be a great help.
8/23/2007 6:09 PM | Kay
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

Hey,
i didn't see anything regarding the MSAccess 'Frist' command. I haven't been able to find an alternative in SQL
11/6/2007 5:06 PM | bob
Gravatar

# two where condition in one table usin ms-access

hello friends
if some one solve this problem it will be a great challenge to them

i have a table ex:a and data as follows

Item Name Sale Qty Status
a 1 PASSED
b 1 PASSED
c 1 PASSED
a 1 PASSED
b 1 PASSED
c 1 PASSED
a 1 CANCELLD
b 1 CANCELLED
c 1 CANCELLED

Now the problem is:
write a single query to retrive the
i.Item name
ii. No of qty passed
iii. No Of Qty CANCELLED

in one statement.

If Any one can solve this problem and mail to :shri_nelamegham@rediffmail.com
2/5/2008 12:03 AM | Shridhar
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

SELECT ItemName, SUM(IIF(Status='Passed', SaleQty, 0)) as Passed, SUM(IIF(Status='Cancelled', SaleQty, 0)) as Cancelled
FROM a
GROUP BY ItemName
3/5/2008 9:08 AM | Serum
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

HI,

I Tried to wirte a query to take day and month from a date field in MS Access. This is very help full me to string and date functions.



3/6/2008 5:43 AM | kranthi
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

Thanks for providing an excellent reference
6/26/2008 7:30 AM | Sharad
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

Hi

If someone could be of assistance I'm trying to convert the following JETSQL SELECT statement to T-SQL:

SELECT [Chronos data extract (Pt1)].SPECIALTY_NATNL_CODE AS Spec, [Chronos data extract (Pt1)].Name, [Chronos data extract (Pt1)].DateOfBirth, [Chronos data extract (Pt1)].Age, dbo_PATIENT_VIEW.Sex, [Chronos data extract (Pt1)].SpokenLanguage, [Chronos data extract (Pt1)].HospitalNumber, [Chronos data extract (Pt1)].AttendanceIdentifier, dbo_PATIENT_VIEW.HomePhone, dbo_PATIENT_VIEW.WorkPhone, "-" AS ContactDescription, "-" AS MobilePhone, "-" AS EmailAddress, "-" AS FaxNumber, IIf(dbo_PATIENT_VIEW.Address1 Is Null,[Address2] & ", " & [Address3] & ", " & [Address4],dbo_PATIENT_VIEW.Address1 & ", " & [Address2] & ", " & [Address3] & ", " & [Address4]) AS Address, dbo_PATIENT_VIEW.Postcode, "-" AS Type, "-" AS TimeOfDay, [Chronos data extract (Pt1)].Appointment, [Chronos data extract (Pt1)].Consultant, [Chronos data extract (Pt1)].[Clinic Code] INTO [Chronos Data Extract]
FROM dbo_PATIENT_VIEW INNER JOIN [Chronos data extract (Pt1)] ON dbo_PATIENT_VIEW.PATNT_REFNO = [Chronos data extract (Pt1)].PATNT_REFNO;

On Parsing this statement I get the following error...

Incorrect syntax near the keyword 'IS'.



7/15/2008 6:19 AM | Mike Downes
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

Thanks

This was very useful just ried to figure out something about getting a particular Section of a string. The CHARINDEX Function Helped

Cheers

SHAJ
10/9/2008 10:04 AM | SHAJ
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

please i don't convert :
val(dbo.Reparation.Date_rep)
to sql server
thinks
12/12/2008 9:12 AM | elabbassi
Gravatar

# Convert Microsoft Access to SQL Server (T-SQL)

UPDATE Jobs, Jobs AS Jobs_1 SET Jobs.jobClientRef = [Jobs_1].[jobClientRef], Jobs.jobDispatchFrom = [Jobs_1].[jobDispatchFrom], Jobs.jobDispatchTo = [Jobs_1].[jobDispatchTo], Jobs.jobPaymentBy = [Jobs_1].[jobPaymentBy], Jobs.jobCost = [Jobs_1].[jobCost], Jobs.jobDriver = [Jobs_1].[jobDriver], Jobs.jobType = [Jobs_1].[jobType], Jobs.jobNotes = [Jobs_1].[jobNotes], Jobs.jobRep = [Jobs_1].[jobRep], Jobs.jobSusRID = [Jobs_1].[jobSusRID], Jobs.jobZonRID = [Jobs_1].[jobZonRID], Jobs.jobGroup = [Jobs_1].[jobGroup], Jobs.jobText1 = [Jobs_1].[jobText1], Jobs.jobText2 = [Jobs_1].[jobText2], Jobs.jobText3 = [Jobs_1].[jobText3], Jobs.jobText4 = [Jobs_1].[jobText4], Jobs.jobText5 = [Jobs_1].[jobText5], Jobs.jobText6 = [Jobs_1].[jobText6], Jobs.jobText7 = [Jobs_1].[jobText7], Jobs.jobText8 = [Jobs_1].[jobText8], Jobs.jobText9 = [Jobs_1].[jobText9], Jobs.jobYesNo1 = [Jobs_1].[jobYesNo1], Jobs.jobYesNo2 = [Jobs_1].[jobYesNo2], Jobs.jobYesNo3 = [Jobs_1].[jobYesNo3], Jobs.jobYesNo4 = [Jobs_1].[jobYesNo4], Jobs.jobYesNo5 = [Jobs_1].[jobYesNo5], Jobs.jobYesNo6 = [Jobs_1].[jobYesNo6], Jobs.jobNumericLong1 = [Jobs_1].[jobNumericLong1], Jobs.jobNumericLong2 = [Jobs_1].[jobNumericLong2], Jobs.jobNumericLong3 = [Jobs_1].[jobNumericLong3], Jobs.jobNumericLong4 = [Jobs_1].[jobNumericLong4], Jobs.jobNumericLong5 = [Jobs_1].[jobNumericLong5], Jobs.jobNumericLong6 = [Jobs_1].[jobNumericLong6], Jobs.jobNumericLong7 = [Jobs_1].[jobNumericLong7], Jobs.jobNumericLong8 = [Jobs_1].[jobNumericLong8], Jobs.jobNumericLong9 = [Jobs_1].[jobNumericLong9], Jobs.jobNumericSng1 = [Jobs_1].[jobNumericSng1], Jobs.jobNumericSng2 = [Jobs_1].[jobNumericSng2], Jobs.jobNumericSng3 = [Jobs_1].[jobNumericSng3], Jobs.jobNumericSng4 = [Jobs_1].[jobNumericSng4], Jobs.jobNumericSng5 = [Jobs_1].[jobNumericSng5], Jobs.jobNumericSng6 = [Jobs_1].[jobNumericSng6], Jobs.jobNumericSng7 = [Jobs_1].[jobNumericSng7], Jobs.jobNumericSng8 = [Jobs_1].[jobNumericSng8], Jobs.jobNumericSng9 = [Jobs_1].[jobNumericSng9], Jobs.jobCurrency1 = [Jobs_1].[jobCurrency1], Jobs.jobCurrency2 = [Jobs_1].[jobCurrency2], Jobs.jobCurrency3 = [Jobs_1].[jobCurrency3], Jobs.jobCurrency4 = [Jobs_1].[jobCurrency4], Jobs.jobCurrency5 = [Jobs_1].[jobCurrency5], Jobs.jobCurrency6 = [Jobs_1].[jobCurrency6], Jobs.jobDate1 = [Jobs_1].[jobDate1], Jobs.jobDate2 = [Jobs_1].[jobDate2], Jobs.jobDate3 = [Jobs_1].[jobDate3], Jobs.jobDateTime1 = [Jobs_1].[jobDateTime1], Jobs.jobDateTime2 = [Jobs_1].[jobDateTime2], Jobs.jobDateTime3 = [Jobs_1].[jobDateTime3], Jobs.jobMemo1 = [Jobs_1].[jobMemo1]
WHERE (((Jobs.jobJobRID)=0) AND ((Jobs_1.jobJobRID)=0));
12/29/2008 5:28 PM | Chirag [atel
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

Your this help page is awesome and thanks for your this help

If possible then send me this page in email
this is my id
bhavinpandya84@yahoo.com

Regard
Bhavin
3/18/2009 2:44 AM | Bhavin Pandya
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

This was very good information. Thanks for posting. I have an additional question how would you convert the following from Access 2007 to SQL Server 2005.


[T Shipments].[SH PU Rcvd EDI 0/1] = 1


[T Shipments].[Load Id] = [T Loads].[Load Id]


Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '='.
8/5/2009 5:40 PM | robert
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

Hello,

I'm trying to convert a number string into a text string in Access 2007 is that possible
8/26/2009 9:49 AM | SLM
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

I am finishing up development for a generic Database Upgrade Conversion process involving the automated conversion of Access Table, View and Index Structures in a VPASP Shopping or any other Access System to their SQL Server 2005/2008 equivalents. It runs right from your server without the need for any outside assistance from external staff or consultants.

After creating a clone of your Access tables, indexes and views in SQL Server this data migration routine will selectively migrate all the data from your Access tables into your new SQL Server 2005/2008 tables without having to give out either your actual Access Database or the Table Contents or your passwords to anyone.

Here is the Reverse Engineering part of the process running against a system with almost 200 tables and almost 300 indexes and Views which is being done as a system acceptance test. Still a work in progress, but the core pieces are in place.

http://www.21stcenturyecommerce.com/SQLDDL/ViewDBTables.asp

I do the automated reverse engineering of the Access Table DDLs (Data Definition Language) and convert them into SQL equivalent DDL Statements, because table structures and even extra tables might be slightly different for every VPASP customer and for every version of VP-ASP out there.

I am finishing the actual data conversion routine which would migrate the data from Access to SQL Server after these new SQL Tables have been created including any views or indexes. It is written entirely in ASP, with VB Scripting, the File System Object (FSO), the Dictionary Object, XML, DHTML, JavaScript right now and runs pretty quickly as you will see against a SQL Server 2008 Database just for the sake of an example.

It takes perhaps 15-20 seconds to reverse engineer almost 500 different database objects. There might be a total of over 2,000 columns involved in this example for the 170 tables and 270 indexes involved.

I have even come up with a way for you to run both VPASP systems in parallel using 2 different database connection files on the same server just to be sure that orders entered on the Access System and the SQL Server system produce the same results before actual cutover to production.

On a separate note:

Would there be any interest from any VP-ASP customers in a 100% SQL Server 2008 version of VPASP which used stored procedures instead of in-line SQL as part and parcel of this Access to SQL Server conversion process? Let me know. I have always found something between a 50% to 100% performance boost after converting from Access to SQL Server especially for larger databases for most complex ASP systems.

Access can have some performance issues after reaching the 250 MB to 400 MB levels as some of you may have discovered. Data loss due to Access Table corruption is always a possibility as well. Not quite as bad as MySQL with their data corruption and database disruption issues, but an issue nonetheless.

Are there any other issues in your possible Access to SQL Server migration which you would like addressed? There are other ways to upsize or upgrade Access to SQL Server but this method has always been the most reliable and the best that I have ever seen in actual operation.

John (a/k/a The SQL Dude)
sales@designersyles.biz
(This is a VP-ASP Demo Site)
9/17/2009 12:30 AM | John McLoughlin
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

This is an awesome post - I was going nuts trying to figure out the T-SQL equivalent of an IIF() statement for use in a calculated field. Now I can finally go home and go to bed. Bless you.
9/18/2009 5:53 PM | Jaek
Gravatar

# Discrepancies with JET in a more general ODBC context

Jeff:
I assume everything you state is true for Access (i.e. I do not use Access) but I assumed that it would more generally be true for any ODBC data source and I am finding that what you have listed only applies *partially*. For example, if I query an Excel 2003 spreadsheet via ODBC, the MID() function works and the SUBSTRING() function does not, just as you indicated. But I must still use single quotes for strings (not double quote) and percent (not asterisk) for wildcard. That leads me to a plethora of questions:
--Do all ODBC data sources use the JET engine?
--Do different versions of the JET engine perhaps account for the differences I am seeing?
--How do I determine what JET version applies to what product?
--How do I find out what SQL commands to use for querying Excel through ODBC? Same question for CSV files.
12/11/2009 3:41 PM | msorens
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

Hi msorens -- no, JET has no connection really with ODBC. Two different things. ODBC is a "layer" on top of any datasource, and has it's own standard syntax.
12/11/2009 4:02 PM | Jeff
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

Great info. Thanks very much!!
12/30/2009 2:25 PM | Tom
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

Perhaps you should incorporate a comment or two about SQL92 mode, introduced in Access 2003. So far as I can see in the above, it has an effect only on the wildcards, i.e., SQL92 mode in Access uses the same wildcards as T-SQL.

Also, you might add information on derived tables:

<pre> SELECT A.Field1
FROM (SELECT SomeTable.Field1
FROM SomeTable) As A</pre>

In Jet/ACE SQL, that would be:

<pre> SELECT A.Field1
FROM [SELECT SomeTable.Field1
FROM SomeTable]. As A</pre>

But if you're running in SQL92 mode, you can do the first version, which is standard for most SQL dialects.
3/8/2010 2:41 PM | David W. Fenton
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

SELECT (SELECT date FROM rewardsmet r WHERE customer_id=38 AND m.macro_id=r.macro_id) AS [date], (Not `date` Is Null) AS met, *
FROM macros AS m
WHERE (((m.[type])=1));
5/28/2010 5:52 AM | Atiq
Gravatar

# re: Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet

I'm trying to conver this to T-SQL

Right(nz([Old Description]),Len(nz([Old Description]))+InStr(nz([Old Description])," ")) AS Description

Any Ideas?
7/16/2010 12:14 PM | Chad
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET