January 9, 2011 at 3:24 am
Hello,
I have a simple insert command to copy data from one table to another table.
INSERT INTO Bankgegevens (Boekdatum, Bedrag)
SELECT Boekdatum, Bedrag
FROM Bankgegevens_voorlopig
WHERE Bankgegevens_voorlopig.Boekdatum=Bankgegevens.Boekdatum
But when i wanna run the sql-code i get error : Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "Bankgegevens.Boekdatum" could not be bound.
But i don't understand this, because intellisense recognises the table "Bankgegevens".
How can this be???! Thnx in advanced!!
January 9, 2011 at 5:38 am
It may be in the database, but it's not referenced in the select statement.
The select must be a valid statement on it's own. You can't reference a column in the destination table. Change the select so that it returns the rows you want to insert.
SELECT Boekdatum, Bedrag
FROM Bankgegevens_voorlopig
WHERE Bankgegevens_voorlopig.Boekdatum=Bankgegevens.Boekdatum
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 6:28 am
Hello,
THNX for reply, i found solution in this:
INSERT INTO Bankgegevens (Rekening, Munt, Verwerkingsdatum, DebetCredit, Bedrag, Tegenrekening, Naam, Boekdatum,
Omschrijving1,Omschrijving2,Omschrijving3,Omschrijving4,Onbekend1,Onbekend2,Onbekend3,Code)
SELECT BV.Rekening, BV.Munt, BV.Verwerkingsdatum, BV.DebetCredit, cast(BV.Bedrag AS money), BV.Tegenrekening, BV.Naam, BV.Boekdatum,
BV.Omschrijving1, BV.Omschrijving2, BV.Omschrijving3, BV.Omschrijving4, BV.Onbekend1, BV.Onbekend2, BV.Onbekend3, BV.Code
FROM Bankgegevens_voorlopig BV, Bankgegevens B
WHERE B.Bedrag != BV.Bedrag AND B.Boekdatum !=BV.Boekdatum AND B.Code!=BV.Code AND B.DebetCredit!=BV.DebetCredit
AND B.Naam!=BV.Naam AND B.Omschrijving1!=BV.Omschrijving1 AND B.Omschrijving2!=BV.Omschrijving2 AND B.Omschrijving3!=BV.Omschrijving3
AND B.Rekening!=BV.Rekening AND B.Tegenrekening!=BV.Tegenrekening
Notice the line FROM Bankgegevens_voorlopig BV, Bankgegevens B !!!!
So you have to tell sql where to compare values, so mention both tables and give them both another alias or youre getting ambiguos data.
January 9, 2011 at 7:16 am
That might work, but it's very bad form. Have a look at the NOT EXISTS construct, which is specifically for finding matching (or non-matching) rows
SELECT <Whatever>
FROM <SomeTable>
WHERE NOT EXISTS (SELECT 1 FROM <SomeOtherTable> WHERE <Sometable.SomeColumn> = <SomeOtherTable.SomeColumn>)
You can compare multiple columns in the where clause of the not 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:14 am
Hello, i don't understand it completly.
This is what i have:
INSERT INTO Bankgegevens (Rekening, Munt, Verwerkingsdatum, DebetCredit, Bedrag, Tegenrekening, Naam, Boekdatum,
Omschrijving1,Omschrijving2,Omschrijving3,Omschrijving4,Onbekend1,Onbekend2,Onbekend3,Code)
SELECT BV.Rekening, BV.Munt, BV.Verwerkingsdatum, BV.DebetCredit, cast(BV.Bedrag AS money), BV.Tegenrekening, BV.Naam, BV.Boekdatum,
BV.Omschrijving1, BV.Omschrijving2, BV.Omschrijving3, BV.Omschrijving4, BV.Onbekend1, BV.Onbekend2, BV.Onbekend3, BV.Code
FROM Bankgegevens_voorlopig BV
WHERE NOT EXISTS (SELECT 1 FROM Bankgegevens WHERE Bankgegevens.Bedrag = BV.Bedrag)
What do you men with: SELECT 1 FROM
THNX
January 9, 2011 at 11:36 am
That's not the same as your previous query. The previous one compared on multiple columns.
Question is, does the select (ignore the insert for now) return the rows that you want to insert?
The select 1 is because EXISTS doesn't care about columns, it just checks is there a matching row or not.
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 12:33 pm
The SELECT statement returns the values i wan't to insert, that is not the problem.
I now understand indeed select 1, it is the same as select *, read it on the internet.
Now i'm still trying to get the insert command correct. To be clear, i wanna insert only the rows where
the values in column "bedrag", "boekdatum" and "omschrijving1" DIFFER from the other table.
My code above is i believe in the beginning correct, only problem is it compares each column with the exact other column,
like: comparision between table1.column1<>table2.column1 AND table1.column2<>table2.column2. This should be:
compare column1,2,3 from table1 with column1,2,3 from table2. So column 1 and 2 from each table can be the same at each table, but when column3
differs between the two tables than the row must not be inserted in the second table.
Hope you can help, thnx for youre time!!
January 9, 2011 at 12:51 pm
h.v.neerven (1/9/2011)
I now understand indeed select 1, it is the same as select *, read it on the internet.
No it's not.
SELECT 1 returns the constant value 1 for all rows.
SELECT * returns all columns for all rows.
They are equivalent ONLY within an exists subquery and that's only because exists ignores column names.
Now i'm still trying to get the insert command correct. To be clear, i wanna insert only the rows where
the values in column "bedrag", "boekdatum" and "omschrijving1" DIFFER from the other table.
In that case you don't have the select correct. Take out the 'INSERT INTO' portion for the moment and work just on the select statement. Get it to return just the rows that you want to insert into that table. Once you have that, put the INSERT line back.
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 12:52 pm
What's are the primary key columns of the source table and which are the key columns you must use to make sure you are comparing the correct rows.
Once you figure that out, just add the extra criteria you mentioned.
insert into ...
select ...
FROM Bankgegevens_voorlopig BV
WHERE NOT EXISTS (SELECT 1
FROM Bankgegevens B
WHERE B.rekening?? = BV.rekening -- van bvb welke rekening ???
and B.boekingnr = BV.Boekingnr
and (B.bedrag <> BV.bedrag
or B.omschrijving1 <> BV.omschrijving1
)
)
If you are trying a full set comparisons.
i.e. compare all columns of set one with all columns of set two at row bases, have a look at the EXCEPT clause.
insert into bankgegevens
select ....
from bankgegevens_voorlopig
except
select ....
from 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 9, 2011 at 1:14 pm
Hello Gilamonster,
Youre right about 1 and *.
I believe the select-statement is correct. The WHERE clausule after the select statement determines wich rows get inserted.
My question is how to get the WHERE clausule correct.
January 9, 2011 at 1:24 pm
Hello ALZDBA,
Lets say I have these tables:
TABLE1
C1......C2......C3
R1 a........b.......c
R2 a........d.......e
R3 a........f........g
TABLE2
C1......C2......C3
R1 a........b.......c
R2 a........h.......i
Now i wanna insert in TABLE2 all rows van TABLE1 except the one that only exist in TABLE2:
RESULT:
TABLE2
C1......C2......C3
R1 a........b.......c
R2 a........h........i
R3 a.......d........e
R4 a.......f.........g
THNX
January 9, 2011 at 1:29 pm
What do you define as 'exists'? All columns matching?
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 1:33 pm
I mean, don't insert rows form TABLE1 into TABLE2 which allready exist in TABLE2.
With as exists I mean, the row allready has data with matching data in columns C2,C3.
The tables don't have unique keys, this is because the data is from a bulk-insert.
January 9, 2011 at 1:47 pm
SELECT C1, C2, C3
FROM Table1
WHERE NOT EXISTS (SELECT 1 FROM Table2 WHERE Table1.C2 = Table2.C2 AND Table1.C3 = Table2.C3)
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:22 pm
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)
Above returns no rows.
I believe this is because the select statement in the "where exists" clause compares each column to the other table column.
So, of course the query returns no rows because there are always rows in table "Bankgegevens" where a column has the same value in the table "Bankgegevenstest".
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply