January 9, 2011 at 2:42 pm
The script that I gave you will do exactly what you asked. If that's not what you want, then you need to refine the requirements. The exists checks the combination of columns, not one.
CREATE TABLE Table1 (
C1 CHAR(1),
C2 CHAR(1),
C3 CHAR(1)
)
CREATE TABLE Table2 (
C1 CHAR(1),
C2 CHAR(1),
C3 CHAR(1)
)
INSERT INTO Table1 VALUES ('a','b','c')
INSERT INTO Table1 VALUES ('a','d','e')
INSERT INTO Table1 VALUES ('a','f','g')
INSERT INTO Table2 VALUES ('a','b','c')
INSERT INTO Table2 VALUES ('a','h','i')
GO
SELECT C1, C2, C3
FROM Table1
WHERE NOT EXISTS (SELECT 1 FROM Table2 WHERE Table1.C2 = Table2.C2 AND Table1.C3 = Table2.C3)
Returns:
C1 C2 C3
---- ---- ----
a d e
a f g
that is, the two rows in Table1 that don't exist in Table2
If that's not what you want, please clarify.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2011 at 2:45 pm
Can you post the full DDL of your tables ? (object explorer \ rightclick on the table name and script table as create to ...)
This way we can see key definitions and help you out with your query.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 9, 2011 at 3:01 pm
USE [aspnetdb]
GO
/****** Object: Table [dbo].[Bankgegevens_voorlopig] Script Date: 01/09/2011 22:58:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Bankgegevens_voorlopig](
[Rekening] [nvarchar](20) NULL,
[Munt] [nvarchar](10) NULL,
[Verwerkingsdatum] [date] NULL,
[DebetCredit] [nvarchar](50) NULL,
[Bedrag] [nvarchar](50) NULL,
[Tegenrekening] [nvarchar](20) NULL,
[Naam] [nvarchar](50) NULL,
[Boekdatum] [date] NULL,
[Code] [nvarchar](10) NULL,
[Onbekend1] [nvarchar](10) NULL,
[Omschrijving1] [nvarchar](50) NULL,
[Omschrijving2] [nvarchar](50) NULL,
[Omschrijving3] [nvarchar](50) NULL,
[Omschrijving4] [nvarchar](50) NULL,
[Onbekend2] [nvarchar](50) NULL,
[Onbekend3] [nvarchar](50) NULL
) ON [PRIMARY]
GO
AND
USE [aspnetdb]
GO
/****** Object: Table [dbo].[Bankgegevens] Script Date: 01/09/2011 22:59:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Bankgegevens](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Rekening] [nvarchar](20) NOT NULL,
[Munt] [nvarchar](10) NOT NULL,
[Verwerkingsdatum] [date] NOT NULL,
[DebetCredit] [nvarchar](10) NOT NULL,
[Bedrag] [decimal](18, 2) NOT NULL,
[Tegenrekening] [nvarchar](20) NOT NULL,
[Naam] [nvarchar](50) NOT NULL,
[Boekdatum] [date] NOT NULL,
[Code] [nvarchar](10) NOT NULL,
[Onbekend1] [nvarchar](10) NULL,
[Omschrijving1] [nvarchar](50) NOT NULL,
[Omschrijving2] [nvarchar](50) NULL,
[Omschrijving3] [nvarchar](50) NULL,
[Omschrijving4] [nvarchar](50) NULL,
[Onbekend2] [nvarchar](50) NULL,
[Onbekend3] [nvarchar](50) NULL,
[Rubriek_Hoofd] [nvarchar](50) NULL,
[Rubriek_Sub1] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Table "Bankgegevens" is the final table, table "Bankgegevens_voorlopig" is the premature table.
"Bankgegevens" has unique row_id, otherone not!
"Bedrag" in "Bankgegevens" is datatype "DEC", in other table VARCHAR !!
THNX
January 9, 2011 at 3:08 pm
Hello GilaMonster,
Youre script is indeed correct, but this does affect 0 rows:
INSERT INTO Table2 (C1,C2,C3)
SELECT C1, C2, C3
FROM Table1
WHERE NOT EXISTS (SELECT 1 FROM Table2 WHERE Table1.C2 = Table2.C2 AND Table1.C3 = Table2.C3)
WHY???
January 9, 2011 at 3:17 pm
h.v.neerven (1/9/2011)
Hello GilaMonster,Youre script is indeed correct, but this does affect 0 rows:
INSERT INTO Table2 (C1,C2,C3)
SELECT C1, C2, C3
FROM Table1
WHERE NOT EXISTS (SELECT 1 FROM Table2 WHERE Table1.C2 = Table2.C2 AND Table1.C3 = Table2.C3)
WHY???
No it doesn't, not with the sample data that I posted.
Using the sample tables and data I posted a little earlier:
INSERT INTO Table2 (C1,C2,C3)
SELECT C1, C2, C3
FROM Table1
WHERE NOT EXISTS (SELECT 1 FROM Table2 WHERE Table1.C2 = Table2.C2 AND Table1.C3 = Table2.C3)
Result:
(2 row(s) affected)
It'll return 0 if it's run a second time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2011 at 3:20 pm
So given the table schema that you posted, which columns are used to determine if a row already exists?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2011 at 11:32 pm
GilaMonster (1/9/2011)
So given the table schema that you posted, which columns are used to determine if a row already exists?
I tested youre script. Now it adds 2 rows indeed. Do it is correct what youre saying!!
In my table schema i wanna use the columns "Boekdatum", "Bedrag", "omschrijving1" to determine if rows exist.
THNX again for youre time..
January 10, 2011 at 1:17 am
Keep in mind there may be non-related rows that have the same
Bedrag, Boekdatum and Omschrijving1, but belong to a different account.
SELECT Rekening
, Munt
, Verwerkingsdatum
, DebetCredit
, Bedrag
, Tegenrekening
, Naam
, Boekdatum
, Omschrijving1
, Omschrijving2
, Omschrijving3
, Omschrijving4
, Onbekend1
, Onbekend2
, Onbekend3
, Code
FROM Bankgegevenstest
WHERE NOT EXISTS ( SELECT 1
FROM Bankgegevens
WHERE Bankgegevenstest.Bedrag = Bankgegevens.Bedrag
AND Bankgegevenstest.Boekdatum = Bankgegevens.Boekdatum
AND Bankgegevenstest.Omschrijving1 = Bankgegevens.Omschrijving1
/* IMO these are also key data to determine if you are comparing rows for the same account */
and Bankgegevenstest.Rekening = Bankgegevens.Rekening
and Bankgegevenstest.Munt = Bankgegevens.Munt
and Bankgegevenstest.Verwerkingsdatum = Bankgegevens.Verwerkingsdatum
)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 10, 2011 at 4:49 am
Hello, many thnx!!
I know indeed that there may be rows with same "Boekdatum", "Omschrijving1" and "Naam" but that the rows are from a different account.
I think i'm gonna compare with as many rows as possible.
The database is for classifying bankacccount-transactions.
So i know the situation can occur that a person withdrawls the same money on the same day twice. With this situation i'm inserting the row twice, this would be wrong.
However i can't fix this.
I get the data from a bank, the bank doesn't include a unique row-id. The data is a textfile, i bulk-insert it in my database.
January 10, 2011 at 5:34 am
Based on your last info, I would say to fall back to full set comparisson:
This way you compare all columns of the rows of the set.
Keep in mind columns are compared on position basis, not on name basis!
SELECT Rekening
, Munt
, Verwerkingsdatum
, DebetCredit
, Bedrag
, Tegenrekening
, Naam
, Boekdatum
, Omschrijving1
, Omschrijving2
, Omschrijving3
, Omschrijving4
, Onbekend1
, Onbekend2
, Onbekend3
, Code
FROM Bankgegevenstest /* your staging table */
except
SELECT Rekening
, Munt
, Verwerkingsdatum
, DebetCredit
, Bedrag
, Tegenrekening
, Naam
, Boekdatum
, Omschrijving1
, Omschrijving2
, Omschrijving3
, Omschrijving4
, Onbekend1
, Onbekend2
, Onbekend3
, Code
FROM Bankgegevens
where Verwerkingsdatum = ... /* check if this is only a date or a datetime (with time part active)
/* create an index for Verwerkingsdatum on table Bankgegevens */
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 10, 2011 at 12:11 pm
Both ALZDBA and GilaMonster, thanks for youre help and time, i learned a lot these 2 days.
I can now go further with coding.
Happy coding............
Viewing 11 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply