February 22, 2006 at 8:24 am
Hi --
I have found myself suddenly the database administror and responsible for All Things SQL, so I am in the very painful first few lessons of SQL scripting.
What I need to do is take a table and insert a new record for each result that matches a select statement. I'm not certain of the best way to do this.
I've been reading the various loop constructs and thought that WHILE EXISTS (SELECT) would work, so I tried this as a test:
DECLARE @MyCounter INT
SET @MyCounter = 1
WHILE EXISTS (SELECT * FROM ContactMoreInfo WHERE Category = 'Gift 2005')
BEGIN
Print 'Found one!'
PRINT @MyCounter
SET @MyCounter = @MyCounter + 1
END
However, my script doesn't seem to terminate. I know that there are about 1,600 rows that match the SELECT statement and the script will keep running until I stop it. I've seen it as high as 17,000 rows.
Is WHILE EXISTS the wrong construct for this? Why does the script above loop eternally?
Many thanks -
February 22, 2006 at 8:44 am
You are going to have a very difficult time as DBA if you can't drop procedural language based looping concepts and approach SQL via SET based operations.
For your example above:
DECLARE @MyCounter INT
SELECT @MyCounter = COUNT(*)
FROM ContactMoreInfo
WHERE Category = 'Gift 2005'
February 22, 2006 at 8:53 am
Can you please clarify?
From my understanding, that seems like it's just giving a row count, which is not what I need to do at all.
Thanks.
February 22, 2006 at 9:00 am
>> that seems like it's just giving a row count, which is not what I need to do at all.
Your sample code did this:
>>SET @MyCounter = @MyCounter + 1
Which is counting rows where a certain condition exists. If you don't need to count existing rows, that's probably a bit of a misleading chunk of sample code.
What exactly do you need to do ?
February 22, 2006 at 9:07 am
"What I need to do is take a table and insert a new record for each result that matches a select statement. I'm not certain of the best way to do this.
I've been reading the various loop constructs and thought that WHILE EXISTS (SELECT) would work, so I tried this as a test
<script>"
I was counting in the script just to see if I could isolate the correct lines to update. The counter proved that the look was excuting many more times than I would have expected it to. The SELECT statement returns ~1600 rows, while the script continued to execute until I manually stopped it. My question was both why this happened and if this was the correct construct to accomplish my purpose.
I have continued reading, so it looks like cursors may be the correct way to do this?
February 22, 2006 at 9:11 am
>>Is WHILE EXISTS the wrong construct for this? Why does the script above loop eternally?
If a loop is based on a certain condition (EXISTence of something) and you do nothing inside the loop to change that condition, then yes, you have an infinite loop. True in any language, not just T-SQL.
The correct construct depends on the requirements which are too vague to arrive at a solution:
>>take a table and insert a new record for each result that matches a select statement.
Which "table" ?
What is in the "new record" ? A count ? A set of columns from the "table" ?
What is the "result" ? A count ? A set of rows ?
What is a "match" ?
February 22, 2006 at 9:19 am
My table has two columns of importance - ContactID and Category. The table's name is ContactMoreInfo.
I want to find every ClientID that has a Category entry of 'Gift 2005'. If they have that entry, I then want to add two more categories for the same ClientID. Something like:
INSERT INTO ContactMoreInfo (ContactID, Category) VALUES ('@ContactID', 'Gift2006')
INSERT INTO ContactMoreInfo (ContactID, Category) VALUES ('@ContactID', 'Marketing Mailing List')
Thanks for the help!
February 22, 2006 at 9:36 am
Set based solution, single SQL statement with no looping:
INSERT INTO ContactMoreInfo (ContactID, Category)
SELECT ContactID, 'Gift2006'
FROM ContactMoreInfo
WHERE Category = 'Gift 2005'
UNION ALL
SELECT ContactID, 'Marketing Mailing List'
FROM ContactMoreInfo
WHERE Category = 'Gift 2005'
February 22, 2006 at 12:48 pm
Thanks - that makes a lot of sense.
I'm having a problem with applying it and I've determined that the problem exists when the script encounters an existing condition that matches what it is trying to do (i.e., there is already an entry for Marketing Mailing List for a ContactID that also has Gift2005). The entire script terminates without checking additional rows.
I'm not sure which way to go for sanity checking, other than using a IF, THEN construct, but am not sure how to incorporate it in the SQL statement above?
February 22, 2006 at 12:59 pm
Again, you need to get out of the one-row-at-a-time procedural mindset, and lose the IF ... THEN.
Set based approach, using WHERE EXISTS to determine if the key already exists prior to inserting:
INSERT INTO ContactMoreInfo (ContactID, Category)
SELECT ContactID, Category
FROM
(
SELECT ContactID, 'Gift2006' As Category
FROM ContactMoreInfo
WHERE Category = 'Gift 2005'
UNION ALL
SELECT ContactID, 'Marketing Mailing List'
FROM ContactMoreInfo
WHERE Category = 'Gift 2005'
) dt
WHERE NOT EXISTS (
SELECT *
FROM ContactMoreInfo As cm
WHERE cm.ContactId = dt.ContactID
AND cm.Category = dt.Category
)
February 22, 2006 at 1:19 pm
Thanks a bunch - that not only works, but makes a lot of sense!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply