import data from text file into sql table

Read Data from your text file and get it into SQL Server first. Then try to manipulate it with all the features avilable in t-sql.

I would do something like ....

Query

DECLARE @FileTable TABLE (FileData NVARCHAR(MAX))

INSERT INTO @FileTable
SELECT BulkColumn 
FROM OPENROWSET(BULK N'C:\Path_to_Text_File\Test_Text_FIle.txt', SINGLE_CLOB) AS Contents

;WITH CTE AS(
 SELECT REPLACE(
         REPLACE(
           REPLACE(
              REPLACE(FileData, '),(', '|')
               , '(', '') 
             , ')', '')
            , '''', '')   AS TextData
              FROM @FileTable
 ),Column_Split AS
 (
 SELECT CONVERT(XML,'<Columns><Column>'  
   + REPLACE(val,',', '</Column><Column>') + '</Column></Columns>') [Columns]
 FROM CTE C CROSS APPLY dbo.split(c.TextData, '|')
)
SELECT      
   [Columns].value('/Columns[1]/Column[1]','varchar(100)') AS Column1,    
   [Columns].value('/Columns[1]/Column[2]','varchar(100)') AS Column2,
   [Columns].value('/Columns[1]/Column[3]','varchar(100)') AS Column3,
   [Columns].value('/Columns[1]/Column[4]','varchar(100)') AS Column4
 FROM Column_Split

Result

╔═════════╦═════════╦═════════╦═════════════════════╗
║ Column1 ║ Column2 ║ Column3 ║       Column4       ║
╠═════════╬═════════╬═════════╬═════════════════════╣
║     699 ║     429 ║       1 ║ 2000-11-30 04:34:16 ║
║     699 ║    2170 ║       2 ║ 2000-11-30 04:07:23 ║
║     699 ║    2171 ║       2 ║ 2000-11-30 04:21:44 ║
║     699 ║     919 ║       4 ║ 2001-04-05 03:21:50 ║
║     699 ║    3160 ║       5 ║ 2000-11-30 04:16:29 ║
║     699 ║    1250 ║       3 ║ 2001-04-05 03:18:20 ║
╚═════════╩═════════╩═════════╩═════════════════════╝

Split Function Definition

CREATE FUNCTION [dbo].[split](  
  @delimited NVARCHAR(MAX),  
  @delimiter NVARCHAR(100)  
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))  
AS  
BEGIN  
  DECLARE @xml XML  
  SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'  

  INSERT INTO @t(val)  
  SELECT  r.value('.','varchar(MAX)') as item  
  FROM  @xml.nodes('/t') as records(r)  
  RETURN  
END