July 21, 2004 at 12:13 pm
Ok, I have three tables - two tables of items and a matching table. I want to create a third table that concatenates all the data in the third into a single column (breaking normalization). I though the easiest way to do that is to insert from table1 to table4, then update table4 from table2-joined-table2. However, I can't seem to get the update to loop.
So here's what I'm trying:
UPDATE test_show -- the table(4) I want to update SET content=test_show.content+( -- I'm trying to append more data to 'conent' SELECT text FROM test_cat WHERE (test_match.cID=test_cat.id) ) FROM test_match JOIN test_show ON (test_show.uID=test_match.uID)
If I just select the two together, I get all the rows I want, but it's not going through in the update.
SELECT * FROM test_show JOIN test_match ON (test_show.uID=test_match.uID)
.. yields 6 rows (correct).
Any thoughts?
July 21, 2004 at 9:08 pm
Why can't you do the concatenation during the initial insert?
INSERT INTO test_show(uID, content)
SELECT tm.uID, tm.content + tc.text
FROM test_match tm
JOIN test_cat tc ON tc.uID = tm.uID
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
July 22, 2004 at 11:35 am
Because I'm trying to break normalization. If I followed your example, the system would try to insert multiple primary keys. For example, if I had a name in table1 (Joe), matching (table2) three colors in table3 (red,blue,green), I want to create a single row in table4 that says |Joe|red,blue,green|, which might be followed by |Mary|green,yellow|. I can easily return the result set I want to build from by doing a cross join, to get
|Joe|red|
|Joe|blue|
|Joe|green|
|Mary|green|
|Mary|yellow|
But, I want to append each to a given column with the name as a primary key.
Hope this is clearer.
July 27, 2004 at 2:06 pm
Any ideas?
July 27, 2004 at 3:11 pm
Hi, I have used this type of thing when trying to list as a string all of the groups that a person may be a member of. I found that for me it was easier to created a function that spins through the results of the first query and then just return the string of concantinated items,
Here is a function that will return a string Authors for a particular title on the pubs database:
| Alter Function fn_TitleAuthorsStringGet(@title_ID id) returns Varchar(500) as Begin Declare @ReturnString Varchar(500) Set @ReturnString='' Declare @name Varchar(150) Declare Scroller Cursor For Select au_fname +' '+au_Lname FROM dbo.titleauthor INNER JOIN dbo.Authors ON dbo.titleauthor.au_id = dbo.Authors.au_id Where title_ID=@title_ID Open Scroller Fetch next from Scroller into @name While @@fetch_Status=0 Begin
Set @ReturnString=@ReturnString +' '+ @name +',' Fetch next from Scroller into @name End Return(@ReturnString) end |
Just a note the function above does not do the commos correctly I did not have time to mess with that.
Now what I would do is reference this function when you want a string to insert Like so:
Insert into NonNorlmalTable (Title,Authors) Select Title,dbo.fn_TitleAuthorsStringGet(title_ID id) from Titles |
I hope that helps. I did some test of speed against this and the difference accross 10000 rows was minor so i was not worried about it.
Tal McMahon
July 27, 2004 at 3:14 pm
Sorry, I had written up a reply last night but I guess it didn't go through. Basically what I would do in this situation is to create a UDF that would do the concatenation for you. Then use the UDF in the insert statement.
IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id('f_GetMatchList'))
BEGIN
PRINT 'DROPPING FUNCTION f_GetMatchList'
DROP FUNCTION f_GetMatchList
END
PRINT 'CREATING FUNCTION f_GetMatchList'
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.f_GetMatchList
(
@uID varchar(255)
, @strDelim varchar(3)
)
RETURNS varchar(4000)
AS
BEGIN
DECLARE @strResult varchar(4000)
SET @strResult = ''
SELECT @strResult = @strResult + Matched + @strDelim
FROM matching
WHERE uID = @uID
SET @strResult = LEFT(@strResult, LEN(@strResult) - LEN(@strDelim))
RETURN @strResult
END
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
INSERT INTO test_show(uID, content)
SELECT tm.uID, dbo.f_GetMatchList(uID,'|')
FROM test_cat tc
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
July 29, 2004 at 11:39 am
Well, this is good. Does any one know how to do it without UDFs? I'm running on SQL 7.
July 29, 2004 at 1:24 pm
For example, here I've implemented a cursor. This is way inefficient - it took two minute to run against 1564 rows.
DECLARE @userList CURSOR,
@uID int,
@content varchar(8000),
@timestamp datetime
SET @userList = CURSOR LOCAL FAST_FORWARD FOR
SELECT userID FROM arial_users
OPEN @userList
FETCH NEXT FROM @userList INTO @uID
SET @timestamp=GetDate()
-- execute as long as there are records
WHILE @@FETCH_STATUS = 0
BEGIN
-- fetch email content
EXEC am_concatenator @uID,null,@strResult=@content OUTPUT
-- update the user's records
UPDATE arial_mailing_test
SET email_text=@content
WHERE (userID=@uID)
-- get next row
PRINT DATEDIFF(ms,@timestamp,GetDate())
SET @timestamp=GetDate()
FETCH NEXT FROM @userList INTO @uID
END
-- clean-up
CLOSE @userList
DEALLOCATE @userList
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply