Auto Insertion from varchar - 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 2005 Forums

Transact-SQL (2005)

Auto Insertion from varchar

Author

Topic

ranalk
Starting Member

49 Posts

Posted - 2012-02-20 : 04:03:41

Hi,

I would like to know how can devide and insert records automatically into temp table.
For example, here is the input:

Declare @Names varchar(max)
set @Names=Jenny,linda,Russel,peter

The requested insertion will be parsing each name into single row records:
#temp
1|Jenny
2|linda
3|Russel
4|peter

Any idea?

Thanks!

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-02-20 : 04:19:53

You have to do some kind of looping...

Declare @Names varchar(max)
Declare @var varchar(250)
set @Names='Jenny,linda,Russel,peter'

set @Names =@Names+','
while Charindex(',',@Names)>0
Begin
set @var=left(@Names,Charindex(',',@Names)-1)

-- Insert statement
Select @var

Set @Names=substring(@Names,Charindex(',',@Names)+1,len(@Names))
End

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources