Insert into question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Insert into question

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-07-22 : 10:05:47

I am using the familiar structure of

insert into table a

(fields ......)

Select

fields .....

From

table b

However, some of the values to be inserted are constants, such as today's date. What is the correct syntax for this please (if it can be done)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-22 : 10:13:52

For todays date (with time), use getdate() in your field selection, if you do not want time, use dateadd(dd,0,datediff(dd,0,getdate()))

So:

insert into table
(datefield,restoffields)
select
dateadd(dd,0,datediff(dd,0,getdate())) as datedfield,
restoffields
from table

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-07-22 : 10:25:48

Many thanks for that.

So, if I needed to add another constant, such an an integer, for example, I could have something like

insert into table
(datefield,id,restoffields)
select
dateadd(dd,0,datediff(dd,0,getdate())) as datedfield,
5,
restoffields
from table

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-22 : 10:41:44

yup

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-07-23 : 03:01:44

quote:
Originally posted by OldMySQLUser

insert into table
(datefield,id,restoffields)
select
dateadd(dd,0,datediff(dd,0,getdate())) as datedfield,
5,
True,
False,
restoffields
from table

If I want to add in True/False values, as above, I get an error 'invalid column names'. How Can I insert true/false values for bit fields please?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-23 : 03:06:51

insert 1 or 0 for the bit fields

and while displaying case when field = 1 then 'True' else 'False'

or if u want to insert as true change the datatype as varchar field

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-07-23 : 03:26:15

Of course! Thanks bklr.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources