Inserting a single record - 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

Inserting a single record

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-11-20 : 09:14:51

Looking at a table that holds order information and there could be mulitple records with the same ord_no. I want to insert one record for each ord_no. What I'm doing now is inserting a record for each record in the table.

Example:


ord_no  Vendor    Date
  100    299      11/20/09
  100    299      11/19/09
  200    388      11/20/09
  200    388      11/11/09
  200    388      11/09/09

In the sample above I want to insert one record for each ord_no.

This code inserts a record for each record. How do I modify it so that it only inserts one record for each ord_no?????

insert into table(ord_no,vendor,date)
select ord_no, vendor, date
from poordhdr_sql

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-20 : 09:32:21

Try

insert into table(ord_no,vendor,date)
select ord_no, vendor, max(date)
from poordhdr_sql
group by ord_no, vendor

Madhivanan

Failing to plan is Planning to fail

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-20 : 09:39:51

Well, you'll have to decide which record you want inserted somehow. This will put the 1st (chronicalogically) in


with orders (rank, ord_no, vendor, date) as (
	SELECT	rank() over(partition by ord_no order by ord_no, date), ord_no, vendor, dt FROM poordhdr_sql
)
SELECT ord_no, vendor, date FROM orders WHERE rank = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources