INSERTING MULTIPLE RECORDS IN ONE FIELD

Question

Post reply

INSERTING MULTIPLE RECORDS IN ONE FIELD

Franco_1

Say Hey Kid

Points: 663
More actions
March 6, 2009 at 7:25 am

#201038

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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

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

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Franco_1 Say Hey Kid Points: 663 More actions · Answer 2

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.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

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

Franco_1 Say Hey Kid Points: 663 More actions · Answer 4

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.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

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