Set Based thinking - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Set Based thinking

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-30 : 11:52:08

So, the all-knowing robvolk gave me this sample:
INSERT INTO yetAnotherTable(col1, col2)
SELECT col1, col2 FROM myTable
WHERE col2=7

How do you deal with a situation where yetAnotherTable.col1 has a value coming from one table and yetAnotherTable.col2 needs to get a value from another table?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-30 : 12:06:06

INSERT INTO yetAnotherTable(col1, col2)
SELECT t1.col1, t2.col2
FROM myTable t1
join mysecondtable t2
on t1.fld = t2.fld
WHERE t1.col2=7

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-30 : 12:21:17

Sorry, perhaps I didn't explain that well enough. The two source tables cant be joined. Here's the sort-of real world example:

OldSectionsTable Columns: ID, Words

NewSectionsTable Columns: NewGUID, Old_ID
WordsTable Columns: Word, SectionID

I've adapted the CsvToInt function to CsvToVarchar. So it returns a record for each word in the comma seperated list.

For each record in OldSectionsTable I want to split the words out
into WordsTable and set WordsTable.SectionID to NewSectionsTable.NewGUID where NewSectionsTable.Old_ID = OldSectionsTable.ID

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-30 : 12:46:56

Unfortunately you can't use your function on all rows in a table in one statement so if you want to do it that way you will have to loop through the records.
Insert into NewSectionsTable first then for each rec insert into WordsTable joining the tables as you have indicated to get the guid.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-30 : 12:54:22

When you say loop...
do you mean use a cursor???

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-30 : 13:08:07

Did somebody say CURSOR?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-30 : 13:13:43

YES!
How else do you loop!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-30 : 15:38:37

to convert lots of CSV strings into 1 row per value, see this article:

http://www.sqlteam.com/item.asp?ItemID=2652

To convert 1 CSV into a table, use a UDF. To convert sets of values, use a technique like the one described in the article.

And, of course, in the future, always remember to consider actually normalizing your database so you can avoid issues like this. I know normalization is a crazy concept, but it actually works believe it or not!

- Jeff

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-31 : 02:37:20

Easy now. I preach the normalization gospel everywhere I go. I have created a normalized DB and now I am converting the data.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 17:22:31

quote:
Originally posted by coolerbob

YES!
How else do you loop!

With a WHILE loop.

quote:
Originally posted by jsmith8858

To convert 1 CSV into a table, use a UDF.

I prefer the JOIN method posted by Celko and Jay (Page47) in the comment section of that UDF article. And here it is:


INNER JOIN
	(
		select nullif(substring(@sep+@csv+@sep,n,charindex(@sep,@sep+@csv+@sep,n)-n),'') as element      
		from dbo.Numbers      
		where 
			n <= datalength(@sep+@csv+@sep) and
			n-datalength(@sep)>0 and
			substring(@sep+@csv+@sep,n-datalength(@sep),datalength(@sep))=@sep and            
			charindex(@sep,@sep+@csv+@sep,n)-n>0
	) csv
ON dg.GroupId = csv.element

dg.GroupId is the only thing that you need to change. So whatever column is to receive these values, put it there instead of what I have.

Jeff,
Have you tried this one, which is set-based, out and compared it to the UDF, which loops?

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources