Create/Update Table from another

[mattmontalto]

This one is probably a no-brainer for the sql guru's here, but I am a bit of a novice and do know how to do this....

I need to create a query that will pull fields from another db's table but also take 3 fields and combine them into one.. for example, I need to take 4 fields: "address", "City", "state", and "zip" and combine them into one field and insert a comma between address and city and between city and state. and a space between state and zip.

can anybody tell me how I can go about this?

I need the query to either create a table from this or update a table from this.... thank you in advance for the help..

 

[simian336]

Select [address] + ', ' + [City] + ', ' + [state] + ', ' + [zip] AS address
into Newtable
from oldtable

This should work as long as Zip is a varchar, otherwise you might have to cast it.

Simi

 

[mattmontalto]

wow... thank you so much for your fast reply...can I also select the other old table's fields... just separately (as they are)... and is there a way to run this as updating the existing newtable... (rather than re-creating it each time?)

 

[gmmastros]

something like this:

Insert 
Into   NewTable(IdColumn, Name, Address)
Select Id,
       Name,
       [address] + ', ' + [City] + ', ' + [state] + ', ' + [zip] AS address
From   OldTable

You may also want to be careful about nulls and empty strings. If and one of address, city, state, or ZIP is NULL, then you will get null in your new table. If you have empty strings, you'll end up seeing things like ", , , "


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mattmontalto]

Thanks....yes I noticed that.. it there a simple way to tell it to ignore any entry where the "address" field is null?

 

[gmmastros]

Ignore how? Do you mean that you don't want the data copied if there is a null in any of those columns?

Insert 
Into   NewTable(IdColumn, Name, Address)
Select Id,
       Name,
       [address] + ', ' + [City] + ', ' + [state] + ', ' + [zip] AS address
From   OldTable 
Where  [address] + ', ' + [City] + ', ' + [state] + ', ' + [zip] Is Not NULL

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk53]

or just where [address] is not null
or where isnunll([address],'')<>''

 

[mattmontalto]

right... I only really want the data if the address, city and state fields have data. Thanks