March 6, 2009 at 7:25 am
I have a comma delimited text file with over 5,000 phone numbers that i have to insert into a phnum field in a table with 2 fields(phID and PhoneNum).
I tried
insert into PhoneTable(PhoneNumField)
values
(phnum1,phnum2,phnum3 etc) but got errors.
How do I insert these phone numbers in the same field?
March 6, 2009 at 7:43 am
Post the structure of the table and some sample data and how do you want to see your data !
Hope that we will help you faster!
Dugi
March 6, 2009 at 7:49 am
Table Structure:
Field1 - phID
Field2 - PhoneNum
textfile contents are 10 digit phone numbers and comma delimited.
So I have to insert all these phone numbers in the PhoneNum field.
March 6, 2009 at 11:26 am
Hello Franco
I still don't really understand the requirement. Cann you please post two or three sample rows from your file (sure pseudo phone numbers 😉 ) and how they shall be stored within the destination table. Also the DDL script of the table should be helpfull.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 6, 2009 at 11:31 am
The table has 2 fields: phID and PhNum.
the text file looks like this (10 digit phone numbers):
(7777777777,8888888888,5454545454,5555555555)
I have to insert these phone numbers from the text file into the
Phnum field of the table. phID is an identity column.
March 6, 2009 at 11:40 am
Hello Franco
That's the needed information 🙂
A little sample:
[font="Courier New"]IF (OBJECT_ID('tempdb..#dest') IS NOT NULL)
DROP TABLE #dest
CREATE TABLE #dest (id INT IDENTITY, phone VARCHAR(100))
DECLARE @content VARCHAR(MAX)
SELECT @content = BulkColumn
FROM OPENROWSET(BULK N'C:\Users\Flo\Temp\Test\test.txt', SINGLE_CLOB) AS t
DECLARE @i4Pos INT
SET @i4Pos = PATINDEX('%,%', @content)
WHILE (@i4Pos != 0)
BEGIN
INSERT INTO #dest VALUES (LEFT(@content, @i4Pos - 1))
PRINT LEFT(@content, @i4Pos - 1)
SET @content = RIGHT(@content, LEN(@content) - @i4Pos)
SET @i4Pos = PATINDEX('%,%', @content)
END
INSERT INTO #dest VALUES (@content)
SELECT * FROM #dest
[/font]
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply