Suppose we have created
OrderHistory table in current database:
CREATE TABLE OrderHistory(
OrderId BIGINT PRIMARY KEY,
OrderKey UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
)
If will execute
following sql query in sql server:
WITH cteOrder
AS(
SELECT
OrderId,
LEFT(OrderKey,1) AS Initials
FROM OrderHistory
), WITH cteSubOrder
AS(
SELECT TOP
(2) * FROM cteOrder ORDER BY Initials
)SELECT * FROM cteSubOrder
Or
WITH
XMLNAMESPACES ('sql' AS Prefix)
WITH cteOrder
AS(
SELECT
OrderId,
LEFT(OrderKey,1) AS Initials
FROM OrderHistory
)
SELECT
OrderId AS 'Prefix:OrderId',
Initials AS 'Prefix:OrderKey'
FROM cteOrder FOR XML AUTO
Or
Or
SELECT *
INTO #Temp WITH(ROWLOCK)
INTO #Temp WITH(ROWLOCK)
FROM OrderHistory
We will get error
message :
Incorrect
syntax near the keyword 'with'. If this statement is a common table expression,
an xmlnamespaces clause or a change tracking context clause, the previous
statement must be terminated with a semicolon.
Cause: It is due to syntax error in
both sql queries.
Solution:
Correct syntax
are:
WITH cteOrder
AS(
SELECT
OrderId,
LEFT(OrderKey,1) AS Initials
FROM OrderHistory
),
cteSubOrder
AS(
SELECT TOP
(2) * FROM cteOrder ORDER BY Initials
)SELECT * FROM cteSubOrder
Or
WITH
XMLNAMESPACES ('sql' AS Prefix),
cteOrder
AS(
SELECT
OrderId,
LEFT(OrderKey,1) AS Initials
FROM OrderHistory
)
SELECT
OrderId AS 'Prefix:OrderId',
Initials AS 'Prefix:OrderKey'
FROM cteOrder FOR XML AUTO
Or
Or
SELECT *
INTO #Temp
INTO #Temp
FROM OrderHistory
Note: We cannot use table hints in INTO clause.
Note: We cannot use table hints in INTO clause.
4 comments:
It’s amazing in support of me to truly have a web site that is valuable meant for my knowledge.
The client remains to implement the planning, UX agency project management. Maintained meticulous billing practices and remained within budget
I'm also visiting this site regularly, this web site is really nice and the users are genuinely sharing good thoughts.
have a peek at these guys
Further, the keywords they have identified are ranking well and niche edit services. What stood out the most is their flexibility and communication throughout the project.
Post a Comment