Condition in query - SQL Server Forums

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

Condition in query

Author

Topic

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2010-02-19 : 10:17:58

i want to insert Farm+'-'+ClientName 'ClientName' only in case the product is A for eg. and DataCenter+'-'+Farm+'-'+ClientName 'ClientName' in case the product is B
How do i do this without using cursor?


INSERT INTO TAB1(ClientName, Status, Product, ModifiedDate, ModifiedTime)
(
	SELECT DataCenter+'-'+Farm+'-'+ClientName 'ClientName', 1 'Status', Product 'Product',
	convert(varchar(10),getdate(),110) 'ModifiedDate',
	convert(varchar(8),getdate(),108) 'ModifiedTime' 
	FROM TAB2
	WHERE ClientName NOT IN(SELECT distinct ClientName FROM TAB1)
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 10:21:58

[code]INSERT INTO TAB1(ClientName, Status, Product, ModifiedDate, ModifiedTime)
SELECT CASE WHEN Product ='B' THEN COALESCE(DataCenter+'-','') ELSE '' END + COALESCE(Farm+'-','')+ClientName , 1 , Product ,
dateadd(dd,datediff(dd,0,getdate()),0),
dateadd(dd,-datediff(dd,0,getdate()),getdate())
FROM TAB2
WHERE ClientName NOT IN(SELECT distinct ClientName FROM TAB1)
[/code]
also see

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 10:23:12

Also make sure you keep datatype of fields ModifiedDate, ModifiedTime as datetime. you can change date/time to format you want using date formatting functions.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 10:24:38

INSERT INTO TAB1(ClientName, Status, Product, ModifiedDate, ModifiedTime)
SELECT case when product='A' then DataCenter+'-'+Farm+'-'+ClientName when product='B' then DataCenter+'-'+Farm+'-'+ClientName ELSE othervalue end, 1 'Status', Product 'Product',
convert(varchar(10),getdate(),110) 'ModifiedDate',
convert(varchar(8),getdate(),108) 'ModifiedTime'
FROM TAB2
WHERE ClientName NOT IN(SELECT distinct ClientName FROM TAB1)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 10:25:25

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 10:27:59

quote:
Originally posted by madhivanan

INSERT INTO TAB1(ClientName, Status, Product, ModifiedDate, ModifiedTime)
SELECT case when product='A' then DataCenter+'-'+Farm+'-'+ClientName when product='B' then DataCenter+'-'+Farm+'-'+ClientName ELSE othervalue end, 1 'Status', Product 'Product',
convert(varchar(10),getdate(),110) 'ModifiedDate',
convert(varchar(8),getdate(),108) 'ModifiedTime'
FROM TAB2
WHERE ClientName NOT IN(SELECT distinct ClientName FROM TAB1)

Madhivanan

Failing to plan is Planning to fail

you gave same expression for both cases

Also need to account for NULLs just in case columns are nullable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 10:30:24

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

INSERT INTO TAB1(ClientName, Status, Product, ModifiedDate, ModifiedTime)
SELECT case when product='A' then DataCenter+'-'+Farm+'-'+ClientName when product='B' then DataCenter+'-'+Farm+'-'+ClientName ELSE othervalue end, 1 'Status', Product 'Product',
convert(varchar(10),getdate(),110) 'ModifiedDate',
convert(varchar(8),getdate(),108) 'ModifiedTime'
FROM TAB2
WHERE ClientName NOT IN(SELECT distinct ClientName FROM TAB1)

Madhivanan

Failing to plan is Planning to fail

you gave same expression for both cases
Also need to account for NULLs just in case columns are nullable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 10:37:48

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

INSERT INTO TAB1(ClientName, Status, Product, ModifiedDate, ModifiedTime)
SELECT case when product='A' then DataCenter+'-'+Farm+'-'+ClientName when product='B' then DataCenter+'-'+Farm+'-'+ClientName ELSE othervalue end, 1 'Status', Product 'Product',
convert(varchar(10),getdate(),110) 'ModifiedDate',
convert(varchar(8),getdate(),108) 'ModifiedTime'
FROM TAB2
WHERE ClientName NOT IN(SELECT distinct ClientName FROM TAB1)

Madhivanan

Failing to plan is Planning to fail

you gave same expression for both cases
Also need to account for NULLs just in case columns are nullable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks

Madhivanan

Failing to plan is Planning to fail

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2010-02-19 : 11:57:30

THANKS MADI !!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 12:04:32

quote:
Originally posted by nitsmooth

THANKS MADI !!!!

Hope you used modified solution i suggested. No need of convertion of dates to varchar. Also use coalesce if you want to handle null values also in any of involved columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources