Insert columns into table based on condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert columns into table based on condition

Author

Topic

srinathb42
Starting Member

4 Posts

Posted - 2015-01-30 : 10:49:12

My requirementis below.Please give your inputs on enhancing the T- sql query as I was told not to use SSIS.

Insert data from Table A to Table B with conditions:
1. Truncate gender to one character if necessary.
2. Convert character fields to uppercase as necessary.
3. For systems that supply both residential and mailing addresses, use the residential address if available (both street_address and zip fields have value), mailing address otherwise.

In SSIS I took conditional split with 'ISNULL(res_street_address) == TRUE || ISNULL(res_zip) == TRUE '

default outputname :Consider Res Address; Outputname:Consider mail address.

and mapped as:
(Table A) mail_street_address---street address(Table B)

(Table A) mail_city----------------City(Table B)

(Table A) mail_Zip----------------Zip(Table B)

(Table A) mail_state-------------state(Table B)

(Table A) res_street_address--street address(Table B)

(Table A) res_city---------------City(Table B)

(Table A) res_Zip----------------Zip(Table B)

(Table A) res_state--------------state(Table B)

I want to do the same with T-sql code too:

I came up with below T-SQl but unable to pick(street,city,state,zip columns as I have take combination of street and zip from Table A not individual columns as I wrote in below query) based on above condition(3):

Insert into TABLE B
SELECT
Stats_ID
,UPPER(first_name) first_name
,UPPER(middle_name )middle_name
,UPPER(last_name) last_name
,UPPER(name_suffix) name_suffix
,UPPER(LEFT(LTRIM(gender),1))gender
,UPPER(mother_first_name)mother_first_name
,UPPER(mother_last_name)mother_last_name
,UPPER(mother_maiden_name)mother_maiden_name
,UPPER(CASE WHEN res_street_address IS NOT NULL THEN res_street_address ELSE mail_street_address END) Street_address
,UPPER(CASE WHEN res_city IS NOT NULL THEN res_city ELSE mail_city END ) city
,UPPER(CASE WHEN res_state IS NOT NULL THEN res_state ELSE mail_state END) state
,UPPER(CASE WHEN res_zip IS NOT NULL THEN res_zip ELSE mail_zip END)zip
,phone FROM TABLE A

Table A:

[statsid] [int] NOT NULL,
[first_name] [varchar](250) NULL,
[middle_name] [varchar](250) NULL,
[last_name] [varchar](250) NULL,
[name_suffix] [varchar](10) NULL,
[gender] [varchar](1) NULL,
[mother_first_name] [varchar](250) NULL,
[mother_last_name] [varchar](250) NULL,
[mother_maiden_name] [varchar](100) NULL,
[res_street_address] [varchar](72) NULL,
[res_city] [varchar](40) NULL,
[res_state] [varchar](10) NULL,
[res_zip] [varchar](9) NULL,
[mail_street_address] [varchar](72) NULL,
[mail_city] [varchar](40) NULL,
[mail_state] [varchar](50) NULL,
[mail_zip] [varchar](9) NULL,
[phone] [varchar](15) NULL

Table B:
[stats_id] [varchar](50) NOT NULL,
[first_name] [varchar](100) NULL,
[middle_name] [varchar](100) NULL,
[last_name] [varchar](100) NULL,
[name_suffix] [varchar](100) NULL,
[gender] [varchar](1) NULL,
[mother_first_name] [varchar](100) NULL,
[mother_last_name] [varchar](100) NULL,
[mother_maiden_name] [varchar](100) NULL,
[street_address] [varchar](100) NULL,
[city] [varchar](100) NULL,
[state] [varchar](2) NULL,
[zip] [varchar](10) NULL,
[phone] [varchar](50) NULL

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-01-30 : 22:18:56

[code]INSERT INTO [TABLE B]
SELECT Stats_ID
,UPPER(first_name) first_name
,UPPER(middle_name) middle_name
,UPPER(last_name) last_name
,UPPER(name_suffix) name_suffix
,UPPER(LEFT(LTRIM(gender),1)) gender
,UPPER(mother_first_name) mother_first_name
,UPPER(mother_last_name) mother_last_name
,UPPER(mother_maiden_name) mother_maiden_name
,UPPER(CASE WHEN res_street_address IS NULL
AND res_city IS NULL
AND res_state IS NULL
AND res_zip IS NULL
THEN mail_street_address
ELSE res_street_address
END) Street_address
,UPPER(CASE WHEN res_street_address IS NULL
AND res_city IS NULL
AND res_state IS NULL
AND res_zip IS NULL
THEN mail_city
ELSE res_city
END) city
,UPPER(CASE WHEN res_street_address IS NULL
AND res_city IS NULL
AND res_state IS NULL
AND res_zip IS NULL
THEN mail_state
ELSE res_state
END) state
,UPPER(CASE WHEN res_street_address IS NULL
AND res_city IS NULL
AND res_state IS NULL
AND res_zip IS NULL
THEN mail_zip
ELSE res_zip
END) zip
,phone
FROM [TABLE A][/code]

srinathb42
Starting Member

4 Posts

Posted - 2015-02-03 : 12:15:45

Thank you for your reply

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources