January 14, 2003 at 10:58 am
Hi
I've to load some files into sql server database and update the master using sql server DTS. I would appreciate if anyone can help me on this.
My scenario is i get a excel file which i have to transfer to sql server(i have successfully completed this step) database called TempContactList which is to be compared with data in the MasterContactlist and the rows with different data(each column of the row is to be compared) should be inserted into another table.
Since the number of columns are two many(around 26) i'm not attaching .
I would appreciate any help on this matter
January 14, 2003 at 11:59 am
What you want is a Execute SQL task with a sql statement something like:
INSERT INTO table2(col1, col2, col3...col26)
SELECT col1, col2, col3...col26 FROM TempContactList WHERE NOT EXISTS
(SELECT NULL FROM TempContactList t
JOIN
MasterContactList m
ON (t.col1 = m.col1) AND (t.col2 = m.col2)
AND (t.col3 = m.col3)...AND (t.col26 = m.col26))
hth,
Michael
Michael Weiss
Michael Weiss
January 14, 2003 at 12:21 pm
Hi
Thanks for the reply. i tried executing the query, but i got no result:
SELECT No, Fname, Lname,Age FROM Tempname WHERE NOT EXISTS
(SELECT NULL FROM Tempname t JOIN Mastername m
ON (t.no = m.no) AND (t.Fname = m.Fname)
AND (t.Lname = m.Lname)AND (t.Age = m.Age))
whereas some columns in master and tempname tables are different.
once again thanks for replying
January 14, 2003 at 1:38 pm
Obviously a flaw in my query logic! Maybbe instead of select null you could try select 1? I will try to find an example of the query I had in mind...if I do I will post it here.
Michael
Michael Weiss
Michael Weiss
January 15, 2003 at 4:30 am
Try
INSERT INTO table2(col1, col2, col3...col26)
SELECT t.col1, t.col2, t.col3...t.col26
FROM TempContactList t
LEFT OUTER JOIN MasterContactList m
ON m.col1 = t.col1 AND m.col2 = t.col2 AND m.col3 = t.col3...AND m.col26 = t.col26
WHERE m.col1 IS NULL
I only check NULL on col1 to reduce the number of checks but if col1 can be null in either of the tables then you will have to use a col that is set NOT NULL or check all 26!
Far away is close at hand in the images of elsewhere.
Anon.
January 15, 2003 at 10:18 am
Thanks for the help, was able to solve my problem.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply