[Resolved] Insert - set field value

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

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Insert - set field value

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-02-25 : 07:36:01

I am doing an "insert" into q sql table and a "select" from a db2/400 table

begin transaction
exec('insert into ZyTax.dbo.ttable(
                             report_state, report_year, report_month...
Select                       ztadds,  ztyear,  zt#mt ......
from AS400SRV_MSDASQL.VGSYS400.' + @AliasLibrary + '.' + @SourceFile) 
commit transaction

Records are ammended to existing records in the sql table.

The sql table has a field (custom1) which does not exists in the db2/400 table. At time of insert we would like to manually populate this field with date time stamp. How would I go about doing this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 07:49:18

begin transaction
exec('insert into ZyTax.dbo.ttable(
report_state, report_year, report_month..., quirkycolumn
Select ztadds, ztyear, zt#mt ......, CURRENT_TIMESTAMP
from AS400SRV_MSDASQL.VGSYS400.' + @AliasLibrary + '.' + @SourceFile)
commit transaction

or simply make a default on the column in mind as GETDATE().

E 12°55'05.63"
N 56°04'39.26"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-02-25 : 09:48:29

Got it working. Is there a way to have current time stamp insert mm dd yyy & time instead of "Feb 25 2008 9:41AM" ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-25 : 09:52:02

quote:
Originally posted by snufse

Got it working. Is there a way to have current time stamp insert mm dd yyy & time instead of "Feb 25 2008 9:41AM" ?

why? isnt it better to store date values in universal format yyyy-mm-dd hh:mm:ss.msmsms
you can always format date in way you want in your front end application

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-02-25 : 10:17:53

Format yyyy-mm-dd hh:mm:ss.msmsms is perfect but it looks like the date/time is stored as "Feb 25 2008 9:41AM". This is what I see when I do a select?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-25 : 10:27:15

quote:
Originally posted by snufse

Format yyyy-mm-dd hh:mm:ss.msmsms is perfect but it looks like the date/time is stored as "Feb 25 2008 9:41AM". This is what I see when I do a select?

isnt your field storing date values a datetime field?

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-02-25 : 10:30:03

No the field is defined as varchar(50).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-25 : 10:35:42

why is it so? you're making date manipulations hard by doing it. using a datetime field would have been much better.
any ways to get values in required format use convert() function

http://doc.ddart.net/mssql/sql70/ca-co_1.htm

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-02-25 : 11:28:46

Do not have any choice, this is a third party table and custom fields are for general purpose to be used for "whatever".

So would this be correct?

begin transaction

exec('insert into ZyTax.dbo.ttable(
report_state, report_year, report_month, custom1

select ztadds, ztyear, zt#mt, convertdatetime,current_timestamp,12)
from AS400SRV_MSDASQL.VGSYS400.' + @AliasLibrary + '.' + @SourceFile)
commit transaction

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-25 : 12:16:24

your braces are screwed up..

select ztadds, ztyear, zt#mt, convert(datetime,current_timestamp,12)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 15:59:53

convert(varchar(50), current_timestamp, 121)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources