SQL 2000 to SQL 2008

Question

I have a query in SQL 2000 DB and I need to migrate it to SQL 2008 DB. It works fine in SQL2000 and I j’t need to revamp it into SQL2008. Below is the query in SQL2000. Please guide me how can we overload *= , =* clause in ON clause.

SELECT tblacc. *
FROM   tblacc,
       tblst,
       tblreceipt,
       tblrtemp,
       tblitem
WHERE  tblacc.rkey = tblreceipt.rkey
       AND tblacc.stkey = tblst.stkey
       AND tblacc.stkey *= tblrtemp.stkey
       AND tblacc.stkey *= tblitem.stkey
       AND tblacc.itkey *= tblitem.itkey
       AND tblrtemp.rkey =* tblreceipt.rkey

score 2 · Answer 1 · answered Apr 25 '11 at 21:27

2

*= is a left Join

=* is a right Join

Have you tried bringing it up in the SQL Editor in SQL Server Management Studio? It might convert it for you.

answered Apr 25 '11 at 21:27

Brian

1,845
1
22
37

Good suggestion I just tried `select * from master..spt_values v1, master..spt_values v2 WHERE v1.number *= v2.number` clicked "Design Query in Editor" and it did rewrite it. Apparently there are cases where [the old style syntax is ambiguous](http://www.sqlservercentral.com/blogs/brian_kelley/archive/2007/11/01/3087.aspx) so not sure how it handles those. – Martin Smith Apr 25 '11 at 21:32
It doesn't seem to like the OP's query though. – Martin Smith Apr 25 '11 at 21:38

score 1 · Answer 2 · edited May 23 '17 at 12:13

It's not quite clear what you mean by "overload *=, =* clause in ON clause", unfortunately. I can see one problem, however: you are using the old-style syntax for outer joins. You should replace this syntax by the new "ANSI SQL" syntax. This uses keywords rather than *= and =*, and moves the join condition into the FROM clause:

WHERE a.x *= b.y becomes FROM a LEFT OUTER JOIN b ON a.x = b.y
WHERE a.x =* b.y becomes FROM a RIGHT OUTER JOIN b ON a.x = b.y
There is also FROM a FULL OUTER JOIN b ON a.x = b.y, which pads un-matched tuples from either table with NULLs.

The old syntax has been deprecated since SQL Server 2005 because it was non-standard and prone to introducing ambiguity. It is not available on databases running in SQL Server 2005 or later compatibility mode, which is likely the source of your problem.

score 1 · Accepted Answer · answered Apr 25 '11 at 21:35

SELECT tblacc.*
FROM   tblacc
  INNER JOIN tblreceipt ON tblacc.rkey = tblreceipt.rkey
  INNER JOIN tblst      ON tblacc.stkey = tblst.stkey
  LEFT JOIN  tblitem    ON tblacc.stkey = tblitem.stkey
                       AND tblacc.itkey = tblitem.itkey
  LEFT JOIN  tblrtemp   ON tblacc.stkey = tblrtemp.stkey
                       AND tblrtemp.rkey = tblreceipt.rkey

score 0 · Answer 4 · answered Apr 25 '11 at 21:32

I believe the query should be something like below, though I don't know if you want to do an INNER JOIN and a RIGHT JOIN with the table tblreceipt.

SELECT tblacc.*
FROM   tblacc
JOIN   tblreceipt
    ON tblacc.rkey = tblreceipt.rkey
JOIN   tblst
    ON tblacc.stkey = tblst.stkey
LEFT JOIN tblrtemp
    ON tblacc.stkey = tblrtemp.stkey
LEFT JOIN tblitem
    ON tblacc.stkey = tblitem.stkey AND tblacc.itkey = tblitem.itkey
RIGHT JOIN tblreceipt
    ON tblrtemp.rkey = tblreceipt.rkey

SQL 2000 to SQL 2008

4 Answers4