Comparing data between two tables in SQL Server

Question

0.00/5 (No votes)

See more:

I have a sp which shd compare two tables together.
Everything looks fine but when i try to exec it, it comes out with
Incorrect syntax near the keyword FROM

Here my sp Statement:

SQL

SET @Dynamictb2 = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM [' + @Table_Name + ']'  +
'EXCEPT
SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM' + @Table_Name

EXECUTE sp_executesql @Dynamictb2

Posted 23-Oct-14 3:02am

mikybrain1

Add a Solution

Comments

CHill60 23-Oct-14 9:04am

If you swap the "EXECUTE sp_executesql @Dunamictb2" for "PRINT @Dynamictb2" what is the resulting display ... that's the SQL that is failing

mikybrain1 23-Oct-14 9:20am

thnx but it isn't working. Still the same message

CHill60 23-Oct-14 9:25am

Did you try my solution and it's still not working??

mikybrain1 23-Oct-14 9:29am

Yes just tried it. I even tought of two arguments like this but it isn't working too:
EXEC spDynamic1 '[dbo].[Per2011]', '[dbo].[Per2012]'

CHill60 23-Oct-14 9:36am

If you just run SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM [dbo].[Per2011]
EXCEPT
SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM [dbo].[Per2012] which line does it report the error on?

mikybrain1 23-Oct-14 9:41am

So thatz my sp:

ALTER PROC spDynamic1
(
@Table_Name sysname
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Dynamictb2 nvarchar(255)

SET @Dynamictb2 = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM [' + @Table_Name + ']' +
' EXCEPT
SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name

EXECUTE sp_executesql @Dynamictb2

END
GO

EXEC spDynamic1 '[dbo].[Per201102]'

IF i run the ALTER sp everything seems to be fine BuT if i exec it that when the ERROR message pop in.
And even if i run only the statement u just posted its fine without a message error

CHill60 23-Oct-14 10:00am

Think I found it - have revised my solution

mikybrain1 23-Oct-14 10:10am

U are the KING
BUT if i exec my
EXEC spDynamic1 '[dbo].[Per201102]' It returns me nothing just the Alias.
My question how can i exec my sp to see the diferance between the table [dbo].[Per201102] AND [dbo].[Per201101]

I tried this but error message too much given argument.
Thnx

CHill60 23-Oct-14 10:18am

If you look at the sql it is executing you are saying "get me everything from that table EXCEPT anything that is in that table" - I think you meant to pass two parameters and "get me everything from table1 except where there is something in table2"

mikybrain1 23-Oct-14 10:35am

Yes u damm right but if i pass a second parameter like @Table_Name2 sysname it doesn't work. Can u please help me on that? I have abt 4 tables (201102, 201103, 201104, 201105) and i want to dynamically change them for the differences.
Would u be nice and Alter my stored procedure :)

CHill60 23-Oct-14 10:40am

I've updated my solution again.

mikybrain1 23-Oct-14 10:43am

Thnx bro. Am now on the road but gonna check it out when i get back.
Thnx for helping me out

mikybrain1 23-Oct-14 14:15pm

I've checked it and it's working great :)

One question are u familiar with c# winForms? Cause i get a question maybe u can help

CHill60 23-Oct-14 16:26pm

Yes, I'm familiar with c# winforms :-) If you have another question thought it might be worth posting it again using the "Ask a Question" link - that way more experts will see your question... I'm good but there are better experts here :-)

mikybrain1 24-Oct-14 3:26am

Hi
Am trying to limit my search with a WHERE clause but the spaces and '' ain't responding well and the EXCEPT is underlined
Thatz my try. What wrong?:

SET @Dynamictbl = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name + ' WHERE [Geschäftsbereich] = PB '
' EXCEPT
SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name2 + ' WHERE [Geschäftsbereich] = PB'

CHill60 24-Oct-14 6:38am

You need extra single quotes when the string itself contains them. It's called "escaping" the special characters. On my phone at moment so can't give you an example but try searching for "sql escape single quote"

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2014-10-23T03:09:00

I ran your sql and swapped the EXECUTE for a PRINT and got this

SELECT DISTINCT [Projektdefinition DB] AS Zugänge FROM [xyz]EXCEPT SELECT DISTINCT [Projektdefinition DB] AS Zugänge FROMxyz

so I think this will fix your problem

SQL

SET @Dynamictb2 = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM [' + @Table_Name + ']'  +
' EXCEPT
SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name

Note the extra spaces before EXCEPT and after FROM on the 3rd and 5th lines respectively

EDIT - from OP comment we now know that this is within an SP called by

EXEC spDynamic1 '[dbo].[Per201102]'

so there are extra square brackets being inserted by the SET

Try this instead:

SQL

SET @Dynamictb2 = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name + 
' EXCEPT SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name

EDIT2 - in response to OP comment - I think the SP needs to change similar to this (note - untested)

SQL

ALTER PROC spDynamic1
 (
 @Table_Name sysname ,@Table_Name2 sysname
 )
 AS
 BEGIN
 SET NOCOUNT ON;
 DECLARE @Dynamictb2 nvarchar(255)

 SET @Dynamictb2 = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
 FROM [' + @Table_Name + ']' +
 ' EXCEPT
 SELECT DISTINCT [Projektdefinition DB] AS Zugänge
 FROM ' + @Table_Name2

 EXECUTE sp_executesql @Dynamictb2

 END
 GO