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

SQL Server 2000 Forums

Transact-SQL (2000)

Insert

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-29 : 08:12:01

Sim writes "I want to insert into an existing table. All the rows should have type=2,actionid=200,statusid=101,statusdate=getdate(),user_id=371, but I want to add all archive_id(s) that satisfy the nested query.

Can this be done or am I approaching this the wrong way?

insert into archivetable
(type,archiveid, actionid,statusid,statusdate,user_id)
2,(select meta_id from cds_statsummary a
where
a.lastorderdate<'2005-11-17 00:00:00.000'
and a.idtype='p'
and a.lastorderdate=(select max(lastorderydate)
from cds_summary
where meta_id=a.meta_id
group by meta_id)),200,101,getdate(),371"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-29 : 08:37:46

[code]insert into archivetable
(type,archiveid, actionid,statusid,statusdate,user_id)
Select 2,
(select meta_id from cds_statsummary a
where
a.lastorderdate<'2005-11-17 00:00:00.000'
and a.idtype='p'
and a.lastorderdate=(select max(lastorderydate)
from cds_summary
where meta_id=a.meta_id
group by meta_id)),
200,101,getdate(),371[/code]

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 08:44:24

[code]insert into archivetable
(
type,
archiveid,
actionid,
statusid,
statusdate,
user_id
)
SELECT 2,
meta_id,
200,
101,
getdate(),
371
from cds_statsummary a
inner join (
select meta_id,
max(lastorderydate) mly
from cds_summary
group by meta_id
) d on d.meta_id = a.meta_id and d.mly = a.lastorderdate
where a.lastorderdate < '2005-11-17 00:00:00.000'
and a.idtype = 'p'[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources