Hey

I used mySQL over the last 10 Years. - Now I have to Switch over to MsSql. And I am struggeling with the SQL Statments.

Maybe there is somebody who could translate the following MySQL Statement into a working MsSql Statement?

(Please don't comment the SQL itself, I know, that in this case there are some duplications, but in fact, it is a composed Statement from my Software!) - THX

DROP TEMPORARY TABLE IF EXISTS tmpGlaeser;
DROP TEMPORARY TABLE IF EXISTS tmpSchicht;
CREATE TEMPORARY TABLE tmpGlaeser (PRIMARY KEY (id)) ENGINE = MEMORY 
SELECT Distinct iprolenstype.* FROM iprolensrange iprolensrange INNER JOIN iprolenstype iprolenstype ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas WHERE (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) AND iprolenstype.hst_code_grundglas In (Select hst_code_grundglas From iprolensrange where (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) )  AND (CurDate() BETWEEN IFNull(iprolenstype.lieferbar_ab, CurDate()) AND IFNull(iprolenstype.lieferbar_bis, CurDate()))  And (0 <= iprolensrange.prisma_bis)  And (0 <= iprolensrange.prisma_bis)ORDER BY  iprolenstype.standard_marke, iprolensrange.hst_code_grundglas, iprolenstype.ein_mehrstaerken;
CREATE TEMPORARY TABLE tmpSchicht ENGINE=MEMORY 
SELECT grundglas, photo, schicht FROM (SELECT DISTINCT i1.hst_code_grundglas AS grundglas, iprooptions.phototrop AS photo, iprooptions.hst_code_schicht AS schicht FROM iprooptions LEFT JOIN iprocombination i1 ON (iprooptions.hst_code_schicht = i1.hst_code_schicht1 AND i1.hst_code_grundglas IN (SELECT Distinct iprolenstype.hst_code_grundglas FROM iprolensrange iprolensrange INNER JOIN iprolenstype iprolenstype ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas WHERE (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) AND iprolenstype.hst_code_grundglas In (Select hst_code_grundglas From iprolensrange where (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) )  AND (CurDate() BETWEEN IFNull(iprolenstype.lieferbar_ab, CurDate()) AND IFNull(iprolenstype.lieferbar_bis, CurDate()))  And (0 <= iprolensrange.prisma_bis)  And (0 <= iprolensrange.prisma_bis)ORDER BY  iprolenstype.standard_marke, iprolensrange.hst_code_grundglas, iprolenstype.ein_mehrstaerken)AND i1.hst_code_schicht1 <> '******' AND i1.lieferbarkeit = 2 AND iprooptions.manufacturer_code=i1.manufacturer_code) WHERE iprooptions.farbe>0
Union All
SELECT DISTINCT i2.hst_code_grundglas AS grundglas, iprooptions.phototrop AS photo, iprooptions.hst_code_schicht AS schicht FROM iprooptions LEFT JOIN iprocombination i2 ON (iprooptions.hst_code_schicht = i2.hst_code_schicht2 AND i2.hst_code_grundglas IN (SELECT Distinct iprolenstype.hst_code_grundglas FROM iprolensrange iprolensrange INNER JOIN iprolenstype iprolenstype ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas WHERE (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) AND iprolenstype.hst_code_grundglas In (Select hst_code_grundglas From iprolensrange where (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) )  AND (CurDate() BETWEEN IFNull(iprolenstype.lieferbar_ab, CurDate()) AND IFNull(iprolenstype.lieferbar_bis, CurDate()))  And (0 <= iprolensrange.prisma_bis)  And (0 <= iprolensrange.prisma_bis)ORDER BY  iprolenstype.standard_marke, iprolensrange.hst_code_grundglas, iprolenstype.ein_mehrstaerken)AND i2.hst_code_schicht1 <> '******' AND i2.lieferbarkeit = 2 AND iprooptions.manufacturer_code=i2.manufacturer_code) WHERE iprooptions.farbe>0
Union All
SELECT DISTINCT i3.hst_code_grundglas AS grundglas, iprooptions.phototrop AS photo, iprooptions.hst_code_schicht AS schicht FROM iprooptions LEFT JOIN iprocombination i3 ON (iprooptions.hst_code_schicht = i3.hst_code_schicht1 AND i3.hst_code_grundglas IN (SELECT Distinct iprolenstype.hst_code_grundglas FROM iprolensrange iprolensrange INNER JOIN iprolenstype iprolenstype ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas WHERE (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) AND iprolenstype.hst_code_grundglas In (Select hst_code_grundglas From iprolensrange where (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) )  AND (CurDate() BETWEEN IFNull(iprolenstype.lieferbar_ab, CurDate()) AND IFNull(iprolenstype.lieferbar_bis, CurDate()))  And (0 <= iprolensrange.prisma_bis)  And (0 <= iprolensrange.prisma_bis)ORDER BY  iprolenstype.standard_marke, iprolensrange.hst_code_grundglas, iprolenstype.ein_mehrstaerken)AND i3.hst_code_schicht1 <> '******' AND i3.lieferbarkeit = 2 AND iprooptions.manufacturer_code=i3.manufacturer_code) WHERE iprooptions.farbe>0
) AS newTable 
WHERE grundglas IS NOT NULL;
UPDATE tmpGlaeser g, tmpSchicht s SET g.phototrop=s.photo WHERE g.hst_code_grundglas=s.grundglas and g.phototrop<s.photo;
Select * from tmpGlaeser;

Thx for any help in advance

In SQL Server you can create tempory tables by naming it with a #, like

CREATE TABLE #test (id int, value varchar(30));

physically they are stored in TempDB. The rest of your script is plain Ansi SQL, should work in SQL Server the same way.

Hello - Please see below:

-- SQL Server does not understand TEMPORARY KeyWord or Engine = Memory
CREATE TEMPORARY TABLE tmpGlaeser (PRIMARY KEY (id)) ENGINE = MEMORY 
-- Instead Use this
CREATE TABLE #tmpGlaeser (PRIMARY KEY (id) ) 

-- This is fine
SELECT Distinct iprolenstype.* FROM 
iprolensrange iprolensrange INNER JOIN iprolenstype iprolenstype 
ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas 
WHERE (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) 
AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) 
AND iprolenstype.hst_code_grundglas In (
Select hst_code_grundglas From iprolensrange where (1 BETWEEN iprolensrange.shs_von / 100 
AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) )  AND (CurDate() BETWEEN IFNull(iprolenstype.lieferbar_ab, CurDate()) AND IFNull(iprolenstype.lieferbar_bis, CurDate()))  And (0 <= iprolensrange.prisma_bis)  And (0 <= iprolensrange.prisma_bis)ORDER BY  iprolenstype.standard_marke, iprolensrange.hst_code_grundglas, iprolenstype.ein_mehrstaerken;

-- SQL Server does not understand TEMPORARY KeyWord or Engine = Memory
CREATE TEMPORARY TABLE tmpSchicht ENGINE=MEMORY 
-- Instead use this
CREATE TABLE #tmpSchicht (PRIMARY KEY (id) ) 

-- This is fine
SELECT grundglas, photo, schicht FROM (
SELECT DISTINCT i1.hst_code_grundglas AS grundglas, iprooptions.phototrop AS photo, iprooptions.hst_code_schicht AS schicht FROM iprooptions LEFT JOIN iprocombination i1 ON (iprooptions.hst_code_schicht = i1.hst_code_schicht1 AND i1.hst_code_grundglas IN (SELECT Distinct iprolenstype.hst_code_grundglas FROM iprolensrange iprolensrange INNER JOIN iprolenstype iprolenstype ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas WHERE (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) AND iprolenstype.hst_code_grundglas In (Select hst_code_grundglas From iprolensrange where (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) )  AND (CurDate() BETWEEN IFNull(iprolenstype.lieferbar_ab, CurDate()) AND IFNull(iprolenstype.lieferbar_bis, CurDate()))  And (0 <= iprolensrange.prisma_bis)  And (0 <= iprolensrange.prisma_bis)ORDER BY  iprolenstype.standard_marke, iprolensrange.hst_code_grundglas, iprolenstype.ein_mehrstaerken)AND i1.hst_code_schicht1 <> '******' AND i1.lieferbarkeit = 2 AND iprooptions.manufacturer_code=i1.manufacturer_code) 
WHERE iprooptions.farbe>0
Union All
SELECT DISTINCT i2.hst_code_grundglas AS grundglas, iprooptions.phototrop AS photo, iprooptions.hst_code_schicht AS schicht FROM iprooptions LEFT JOIN iprocombination i2 ON (iprooptions.hst_code_schicht = i2.hst_code_schicht2 AND i2.hst_code_grundglas IN (SELECT Distinct iprolenstype.hst_code_grundglas FROM iprolensrange iprolensrange INNER JOIN iprolenstype iprolenstype ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas WHERE (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) AND iprolenstype.hst_code_grundglas In (Select hst_code_grundglas From iprolensrange where (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) )  AND (CurDate() BETWEEN IFNull(iprolenstype.lieferbar_ab, CurDate()) AND IFNull(iprolenstype.lieferbar_bis, CurDate()))  And (0 <= iprolensrange.prisma_bis)  And (0 <= iprolensrange.prisma_bis)ORDER BY  iprolenstype.standard_marke, iprolensrange.hst_code_grundglas, iprolenstype.ein_mehrstaerken)AND i2.hst_code_schicht1 <> '******' AND i2.lieferbarkeit = 2 AND iprooptions.manufacturer_code=i2.manufacturer_code) WHERE iprooptions.farbe>0
Union All
SELECT DISTINCT i3.hst_code_grundglas AS grundglas, iprooptions.phototrop AS photo, iprooptions.hst_code_schicht AS schicht FROM iprooptions LEFT JOIN iprocombination i3 ON (iprooptions.hst_code_schicht = i3.hst_code_schicht1 AND i3.hst_code_grundglas IN (SELECT Distinct iprolenstype.hst_code_grundglas FROM iprolensrange iprolensrange INNER JOIN iprolenstype iprolenstype ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas WHERE (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) AND iprolenstype.hst_code_grundglas In (Select hst_code_grundglas From iprolensrange where (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) )  AND (CurDate() BETWEEN IFNull(iprolenstype.lieferbar_ab, CurDate()) AND IFNull(iprolenstype.lieferbar_bis, CurDate()))  And (0 <= iprolensrange.prisma_bis)  And (0 <= iprolensrange.prisma_bis)ORDER BY  iprolenstype.standard_marke, iprolensrange.hst_code_grundglas, iprolenstype.ein_mehrstaerken)AND i3.hst_code_schicht1 <> '******' AND i3.lieferbarkeit = 2 AND iprooptions.manufacturer_code=i3.manufacturer_code) WHERE iprooptions.farbe>0
) AS newTable 
WHERE grundglas IS NOT NULL

-- Replace this:
UPDATE tmpGlaeser g, tmpSchicht s SET g.phototrop=s.photo WHERE g.hst_code_grundglas=s.grundglas and g.phototrop<s.photo;
Select * from tmpGlaeser;
-- With this 
UPDATE tmpGlaeser
SET g.phototrop=s.photo 
from tmpGlaeser g, tmpSchicht s
WHERE g.hst_code_grundglas=s.grundglas and g.phototrop<s.photo;
Select * from tmpGlaeser;

Note: Statements with suffix -- are inline comments to help you understand

Hope this helps

Wow  First Of all THX for the very quick answers.

I tried, but stille have some Troubles.

I have changed some Syntax as well, which is not recognized by MsSQL (CurDate, IfNull)

and now, I get the following error:

The ORDER BY clause is invalid in  views, inline functions, derived  tables, subqueries, and common table  expressions, unless TOP or FOR XML is  also specified.

I get this error 3 times within this Statement:

-- This is fine
SELECT grundglas, photo, schicht FROM (SELECT DISTINCT i1.hst_code_grundglas AS grundglas, iprooptions.phototrop AS photo, iprooptions.hst_code_schicht AS schicht FROM iprooptions LEFT JOIN iprocombination i1 ON (iprooptions.hst_code_schicht = i1.hst_code_schicht1 AND i1.hst_code_grundglas IN (SELECT Distinct iprolenstype.hst_code_grundglas FROM iprolensrange iprolensrange INNER JOIN iprolenstype iprolenstype ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas WHERE (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) AND iprolenstype.hst_code_grundglas In (Select hst_code_grundglas From iprolensrange where (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) )  AND (CurrentDate BETWEEN IsNull(iprolenstype.lieferbar_ab, CurrentDate) AND IsNull(iprolenstype.lieferbar_bis, CurrentDate))  And (0 <= iprolensrange.prisma_bis)  And (0 <= iprolensrange.prisma_bis)ORDER BY  iprolenstype.standard_marke, iprolensrange.hst_code_grundglas, iprolenstype.ein_mehrstaerken)AND i1.hst_code_schicht1 <> '******' AND i1.lieferbarkeit = 2 AND iprooptions.manufacturer_code=i1.manufacturer_code) WHERE iprooptions.farbe>0
Union All
SELECT DISTINCT i2.hst_code_grundglas AS grundglas, iprooptions.phototrop AS photo, iprooptions.hst_code_schicht AS schicht FROM iprooptions LEFT JOIN iprocombination i2 ON (iprooptions.hst_code_schicht = i2.hst_code_schicht2 AND i2.hst_code_grundglas IN (SELECT Distinct iprolenstype.hst_code_grundglas FROM iprolensrange iprolensrange INNER JOIN iprolenstype iprolenstype ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas WHERE (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) AND iprolenstype.hst_code_grundglas In (Select hst_code_grundglas From iprolensrange where (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) )  AND (CurrentDate BETWEEN IsNull(iprolenstype.lieferbar_ab, CurrentDate) AND IsNull(iprolenstype.lieferbar_bis, CurrentDate))  And (0 <= iprolensrange.prisma_bis)  And (0 <= iprolensrange.prisma_bis)ORDER BY  iprolenstype.standard_marke, iprolensrange.hst_code_grundglas, iprolenstype.ein_mehrstaerken)AND i2.hst_code_schicht1 <> '******' AND i2.lieferbarkeit = 2 AND iprooptions.manufacturer_code=i2.manufacturer_code) WHERE iprooptions.farbe>0 Union All
SELECT DISTINCT i3.hst_code_grundglas AS grundglas, iprooptions.phototrop AS photo, iprooptions.hst_code_schicht AS schicht FROM iprooptions LEFT JOIN iprocombination i3 ON (iprooptions.hst_code_schicht = i3.hst_code_schicht1 AND i3.hst_code_grundglas IN (SELECT Distinct iprolenstype.hst_code_grundglas FROM iprolensrange iprolensrange INNER JOIN iprolenstype iprolenstype ON iprolensrange.hst_code_grundglas = iprolenstype.hst_code_grundglas WHERE (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) AND iprolenstype.hst_code_grundglas In (Select hst_code_grundglas From iprolensrange where (1 BETWEEN iprolensrange.shs_von / 100 AND iprolensrange.shs_bis / 100) AND (0 BETWEEN iprolensrange.cyl_von / 100 AND iprolensrange.cyl_bis / 100) )  AND (CurrentDate BETWEEN IsNull(iprolenstype.lieferbar_ab, CurrentDate) AND IsNull(iprolenstype.lieferbar_bis, CurrentDate))  And (0 <= iprolensrange.prisma_bis)  And (0 <= iprolensrange.prisma_bis)ORDER BY  iprolenstype.standard_marke, iprolensrange.hst_code_grundglas, iprolenstype.ein_mehrstaerken)AND i3.hst_code_schicht1 <> '******' AND i3.lieferbarkeit = 2 AND iprooptions.manufacturer_code=i3.manufacturer_code) WHERE iprooptions.farbe>0) AS newTable 
WHERE grundglas IS NOT NULL

THX again

The error is self-explanatory.  You can't use an ORDER BY in your subqueries.  Just remove it and sort the final query if needed.

David

Hey

OK - One last question (I hope, than everyhting is working)

I try to get the update Statement running:

UPDATE #tmpGlaeser 
SET #tmpGlaeser.phototrop=#tmpSchicht.photo  
WHERE #tmpGlaeser.hst_code_grundglas=#tmpSchicht.grundglas and #tmpGlaeser.phototrop<#tmpSchicht.photo;

Where I get the following error: The multi-part identifier '#tmpSchicht.grundglas ' could not be bound

What does this mean?

Ok, this is also solved - Now it's working

For everyone else:

UPDATE #tmpGlaeser 
SET #tmpGlaeser.phototrop=mySchicht.photo  
  FROM #tmpSchicht as mySchicht
WHERE #tmpGlaeser.hst_code_grundglas= mySchicht.grundglas and #tmpGlaeser.phototrop<mySchicht.photo;
Select * from #tmpGlaeser;

WOW - Crazy, how complicated this is (at the Moment)...