Insert query in SQL Server 2005

Question

My SQL Server table structure is:

BranchName
BranchManagerName
Address
Zip Info
PhoneNo
FaxNo
Email
Status
CreatedOn

Now I try to pass the value for Zip Info column like State=XXXX, city=YYYY, Zip=123 as three values.

And I need these three values (State,City,Zip) stored in the Zip Info column in this format state,city-Zip (xxxx,yyyy-123) in sql table.

How to write insert query in SQL Server 2005?

what have you tried? and why do you want to store the zip info in one field? — Taryn, Apr 18 '12 at 12:59
Just to support KM's answer: **DON'T DO THAT !!!** don't even think about it.... These are **three very distinct** pieces of information - **DON'T** concatenate these together to store them - sooner or later - most likely sooner - you'll have to parse them apart again! If you have *three distinct** pieces of information - use **three separate columns** for this! Have a `State` column, a `City` column and a `ZipCode` column. If you need that "compound" representation somewhere - build it from those three separate pieces! — marc_s, Apr 18 '12 at 13:49

score 7 · Answer 1 · edited May 23 '17 at 12:29

7

NO NO NO NOOOOOOO!!!

don't do it!! save them as individual columns. Never store multiple fields within a single column!

You'll have problems trying to select all rows for State='NY', where you'll need to constantly do string manipulations and have slow non-index searches.

store them as separate columns and concatenate them when you display them.

SELECT
    ISNULL(State,'')+ISNULL(', '+city,'')+ISNULL('-'+Zip,'') AS ZipInfo
    FROM YourTable

you can always concatenate strings in an insert:

INSERT INTO YourTable
        (col1, col2, col3, Col123)
    VALUES
        (@col1, @col2, @col3, @col1+', '+@col2+'-'+@col3)

your "null protection" will vary as necessary, in the SELECT, I assume you could have NULL values, in the INSERT I assume there will be no NULL values.

Your would be much better off if you store them as 3 columns and create a view or computed column if you really want them as a single field. You are embarking on a common bad design issue by trying to store the values together.

look here for fun splitting strings in SQL Server!

Also, don't put spaces in your column names, unless [you like] having [braces] all [over the] place.

edited May 23 '17 at 12:29

Community

1
1

answered Apr 18 '12 at 13:01

KM.

101,727
34
178
212

1

@user Of course; just replace the ZipInfo column with three new columns: City, State and Zip. There's a great book that describes a lot of SQL anti-patterns and solutions that I recommend: http://pragprog.com/book/bksqla/sql-antipatterns – Cristian Lupascu Apr 18 '12 at 13:04
@w0lf Is possible three values stored into a single column? – user Apr 18 '12 at 13:06
@user: **NO!!** - do **NOT** ever store more than one value in a column! Use **THREE COLUMNS** - – marc_s Apr 18 '12 at 13:52
User, if you will have more than one value in a single column, your database won't even be in 1NF. – Lajos Arpad Apr 18 '12 at 14:26

score 1 · Answer 2 · answered Apr 18 '12 at 13:06

I agree with @KM. that you should not store this data in the same field. You will have significant problems searching for your data if you store the Zip Info data in the same field so you should NOT do it this way.

But if you must then you would have an insert similar to this:

INSERT INTO yourTable
(
    BranchName
    , BranchManagerName
    , Address
    ,[Zip Info]
    ,PhoneNo
    , FaxNo
    , Email
    ,Status
    , CreatedOn
)
SELECT  BranchName
    , BranchManagerName
    , Address
    ,State + ', ' + City + '-' + Zip
    ,PhoneNo
    , FaxNo
    , Email
    ,Status
    , CreatedOn

score 0 · Answer 3 · answered Apr 18 '12 at 13:23

Bro, if you want to add value in format state,city-Zip for the field zip_info and the values for the state,city and zip are as follows XXX,YYYY,123 You just need to put all three values in the below format on the place of Zip_Info in the insert statement

'XXX,YYYY-123'

Insert query in SQL Server 2005

3 Answers3