question

sunil2711 avatar image
sunil2711 asked

Insert Statement

insert two phone no's into one column separated by comma with single insert statement CREATE table phone ( name varchar(20), contact varchar(30) ) Output: name Contact SSC 123-45-6789,987-65-4321'
sql-server-2008sql-server-2005insertdml
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Although the queries that @JohnM posted will insert the comma delimited numbers, I would strongly urge you to reconsider storing multiple delimited phone numbers in a single row/column. When a person with multiple phone numbers changes one (or more) of its numbers, how do you plan to update it? What if you want to find all persons that share a single number? Search by phone number? Delete one number? You're in for a maintenance nightmare if you take this approach. Just add a phone number table that has a foreign key referencing the person table and all of these uncomfortable maintenance problems go away. You will even be able to flag the phone numbers by type - LandLine, cellular, primary, secondary, etc.
2 Likes 2 ·
I'm not sure that I understand. You want to insert two different rows into the table at once? Or you do you just want a single insert statement to insert a single row with that data?
0 Likes 0 ·
I also HIGHLY concur with @KenJ.
0 Likes 0 ·

1 Answer

· Write an Answer
JohnM avatar image
JohnM answered
Would this work? Given that I'm not 100% clear on what you are looking for but I thought that I'd just put something together. CREATE table #phone ( name varchar(20), contact varchar(30) ) GO -- Option #1 INSERT #phone (name, contact) VALUES ('SSC', '123-45-6789,987-65-4321') GO -- Option #2 INSERT #phone VALUES ('SSC', '123-45-6789,987-65-4321') GO -- Option #3 INSERT #phone (name, contact) SELECT 'SSC', '123-45-6789' UNION SELECT 'SSC', '987-65-4321' GO
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.