Looping, testing and inserting - hopefully not a noob question
-
Hello all,
I would like to loop through a table, test a specified value from each record against another table, and then insert a record into a third table with the result of the test.
My first thought was to do this:
SELECT FileID, TheFileName, dbo.fnc_CheckIt(TheFileName) FROM tbl_Files
fnc_CheckIt Takes the passed in value, and does a SELECT against another table.
If it doesn't find a match, I then want to do an INSERT INTO tbl_TestResults, but I'm told that I can't use INSERT in a function ('invalid use of INSERT...').
So, I wrapped the INSERT statement into a storedproc, to be told that functions can't call storedprocs if they are non-deterministic...
So, the question is - how can I do this?
(I normally would do this in another language like ColdFusion, where I can loop thru a recordset with ease, but the volume of records is too high (800K) to do this efficiently in this case.)
Does anyone have any ideas or suggestions?
Any help greatly appreciated.
Antony
-
You don't need to loop through your rows. Just JOIN both tables on a common column and then you can compare almost whatever you want and insert this result into a third table.
To get more specific suggestion, it might be better to post your table structures, sample data and desired output.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for your suggestion.
However, I have a 800K row table and a 680K row table, and I'm working with an under-specced mssql2k server. It takes ~10 secs just to do a count...
If I can get things to work the way I want, I will have a much smaller table of exceptions to analyse.
Re table structure. Both tables are very simple: an int ID and some varchar columns.
Re sample data. Both tables have a column in common with 8.3 dosnames in it.
Re expected output. A new table with a list of exceptions - ie dosnames in one table but not the other. So a join probably wouldn't help anyway, as I am seeking unmatched values.
Thanks for considering my problem - i hope the above helps.
Antony
-
Re expected output. A new table with a list of exceptions - ie dosnames in one table but not the other. So a join probably wouldn't help anyway, as I am seeking unmatched values.
Sure, you can do a LEFT JOIN to test this. Basically I think you have three different alternatives available here:
USE pubs
GO
SELECT DISTINCT city
FROM authors
WHERE NOT EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)
SELECT DISTINCT authors.city
FROM authors
LEFT JOIN publishers
ON authors.city = publishers.city
WHERE publishers.city IS NULL
SELECT DISTINCT city
FROM authors
WHERE city NOT IN
(SELECT city
FROM publishers)
This example is taken from BOL. I've only added the LEFT JOIN statement. All three will find the DIFFERENCE between both sets. Which one in your particular case performs better, is hard to say.
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
There is one more alternative though
Use FULL OUTER JOIN between the two table and check pk nullability to determine source
hth
* Noel -
Here's what I went with:
insert into tbl_NoProfile ([FileName], SourceZIP, FilePath, PKZIPID)
select JustTheName, SourceZIPFile, [FileName], [ID]
from tbl_PKZIPAnalysis
WHERE NOT EXISTS
(SELECT [ID]
FROM tbl_Profiles
WHERE tbl_PKZIPAnalysis.JustTheName = tbl_Profiles.[FileName])
(77503 row(s) affected) - 58 seconds!!
Thanks for your help - exactly what I needed.
(Mental note: bookmark this page ASAP)
-
Glad it helped. You're welcome!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply