SQL Server 2008 Beginner Script Question On effecting all rows

I'm useing SQL Server 2008 and I have a small script to split a row of data into several different columns. My question is how do I get the script to effect all the rows in the column? Right now the data row is just a string literal. I'm wondering if there is another way to wright this so I do not have to keep replaceing the DATA ROW EXAMPLE line with each rows data. The script is below.

DECLARE @TEXTINPUT VARCHAR(120)

SET @TEXTINPUT = 'DATA RO开发者_StackOverflow社区W EXAMPLE 12 89564 DATA ROW EXAMPLE'

SELECT @TEXTINPUT AS TEXTINPUT

SELECT SUBSTRING(@TEXTINPUT,1,4) AS UNIT,
  SUBSTRING(@TEXTINPUT,6,3) AS NAME,
  SUBSTRING(@TEXTINPUT,10,7) AS ACCOUNT,
  SUBSTRING(@TEXTINPUT,18,2) AS NOTE


INSERT INTO ExampleTable
(UNIT, NAME, ACCOUNT, NOTE)
VALUES (SUBSTRING(@TEXTINPUT,1,4), SUBSTRING(@TEXTINPUT,6,3), SUBSTRING(@TEXTINPUT,10,7),
SUBSTRING(@TEXTINPUT,18,2))
GO

I assume below the table with source data is called SourceTable and the column you are splitting is called SourceCol

INSERT INTO ExampleTable
(UNIT, NAME, ACCOUNT, NOTE)
SELECT SUBSTRING(SourceCol,1,1) AS UNIT,
  SUBSTRING(SourceCol,3,9) AS NAME,
  SUBSTRING(SourceCol,13,6) AS ACCOUNT,
  SUBSTRING(SourceCol,20,3) AS NOTE
 FROM SourceTable