trouble converting SELECT to INSERT statement

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

trouble converting SELECT to INSERT statement

Author

Topic

meth73
Starting Member

10 Posts

Posted - 2005-09-18 : 05:41:10

Trying to convert the following SELECT statement into a INSERT statement and having trouble. No doubt this will be a piece of cake to someone. To eventually get this to a trigger stage would be nice, but for the moment I'd settle for just plain SQL. Using MS SQL 2000. The database name is reporting. The table name is CallLog. I'm trying to convert seperate date (RecvdDate) and time (RecvdTime) columns into a single DateTime column. I've scoured a lot of web pages but I'm still lost.

==============

use reporting
go

SELECT RecvdDate + RecvdTime FROM [dbo].[CallLog]

===============

Any help much appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-18 : 10:00:32

>>I've scoured a lot of web pages but I'm still lost.
Did you scour Sql Server's help: Books Online?

well, it depends on what datatypes the RecvDate and RecvTime columns are. For the simplest case lets assume they are varchar. ie: '9/18/2005' and '9:49:00.000'. In this case you would concatenate the 2 values (with a space in between) and then convert the result to a datetime value:

select convert(datetime, RecvdDate + ' ' + RecvdTime) as ReceivedDate from dbo.CallLog
(see CONVERT in Books Online)

The trick is to make sure that the concatenation is a valid format to represent a varchar representation of a datetime value

to change the statement into an insert:

Insert myOtherTable (ReceivedDate)
select convert(datetime, RecvdDate + ' ' + RecvdTime) from dbo.CallLog

(see INSERT in Books Online)

If the values in your table are not char or varchar or the concatenation won't convert to datetime, post the datatypes and some example values.

Be One with the Optimizer
TG

meth73
Starting Member

10 Posts

Posted - 2005-09-19 : 03:29:46

Hi,

I tried the following, but instead of adding the concatenated column in next to the data of the other columns, it instead added it _after_ the data of the columns i.e. the data was concatenated & inserted, but with a whole bunch of NULLS on either side.

Insert CallLog (RecvdDatetime)
select convert(datetime, RecvdDate + ' ' + RecvdTime) from dbo.CallLog

What did I do wrong?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-19 : 03:34:51

Did any one RecvdDate and RecvdTime have null?
See what results you get after running this

select convert(datetime, Isnull(RecvdDate,'') + ' ' + Isnull(RecvdTime,'')) from dbo.CallLog

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-19 : 04:07:48

i believe you are looking for UPDATE instead of an INSERT since from the looks of it, you've added a new column

--------------------
keeping it simple...

meth73
Starting Member

10 Posts

Posted - 2005-09-19 : 07:14:49

Thanks very much for your help guys. Jen's suggestion finally got me there. The successful query was:

--------------------------
update dbo.CallLog
set RecvdDatetime = convert(datetime, Isnull(RecvdDate,'') + ' ' + Isnull(RecvdTime,''))

alter table dbo.CallLog
alter column RecvdDatetime datetime
--------------------------

:)

PatDeV
Posting Yak Master

197 Posts

Posted - 2005-10-11 : 08:40:07

update A
set fedref = [Fed Ref Number]
set fedrefdatetime = convert(datetime,[date sent] + ' ' + [time sent])
from empower.U_wire_info A
join ncenfedrefs on A.lnkey = ncenfedrefs.[loan id]

I am getting erro at = on set fedrefdatetime?
Don't understand.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-11 : 10:11:41

This should have been a new topic...

Just a syntax thing. Check out UPDATE in Books Online.
Also, be consistant with using table aliases. Use them everywhere if you use them at all.
(I guessed at which tables have which columns, you may need to change some aliases)


update A set 
	A.fedref = B.[Fed Ref Number]
	,A.fedrefdatetime = convert(datetime,B.[date sent] + ' ' + B.[time sent])
from empower.U_wire_info A
join ncenfedrefs B on A.lnkey = B.[loan id]

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources