add a CE to an insert? - 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

add a CE to an insert?

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-08-10 : 10:37:01

I need to add one more column to insert into this query. That column is "CODE". However, i need to set code in the destination table to "S" when the column in the source table named Subject has a value of '1'. I need to set the value of the column "CODE" to 'V' when the value of the column named complainantvictim in the source table has a value of '1'. how would i code this into the insert statement? I suspect a CE but have never included one in an insert like this before.




Insert  into offense.dbo.offense 
       ([OFFENSENO]
      ,OC_STREET
      ,[OCCUR_DATE]
      ,[OCCUR_DAT2]
      ,[UNIQUEKEY]
      ,NEW)
            
 SELECT top 100 Offenseno
      ,Substring([Location],1,30) as OC_STREET
      ,[OccuredStart] as occur_Date
      ,[OccuredEnd] as occur_dat2
      ,SCADMIN.dbo.fnsc_GetUniqueID(newid())
      ,'X'
      
 FROM [Douglas].[dbo].[CFSData] t1 WHERE offenseno NOT IN (SELECT offenseno FROM offense.dbo.offense)
GO

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-10 : 10:41:33

Insert into offense.dbo.offense
([OFFENSENO]
,OC_STREET
,[OCCUR_DATE]
,[OCCUR_DAT2]
,[UNIQUEKEY]
,NEW
,CODE)

SELECT top 100 Offenseno
,Substring([Location],1,30) as OC_STREET
,[OccuredStart] as occur_Date
,[OccuredEnd] as occur_dat2
,SCADMIN.dbo.fnsc_GetUniqueID(newid())
,'X'
,CODE = case when Subject = '1' then 'S' when complainantvictim = '1' then 'V' end
FROM [Douglas].[dbo].[CFSData] t1 WHERE offenseno NOT IN (SELECT offenseno FROM offense.dbo.offense)
GO

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-08-10 : 10:53:36

easier than i thought. thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources