JOIN HELP? - 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)

JOIN HELP?

Author

Topic

JScottP99
Starting Member

5 Posts

Posted - 2006-03-14 : 12:08:46

I have a DTS package that imports records from a text file and generates an Identity column.

Two rows may contain the exact same data, the only difference would be that the Identity Column would be different. I need to be able to generate a view that shows only Distinct Rows, but keep the Identity column. For instance if my data looks like this:

Identity Col Col 1 Col 2
1 Hello New
2 Hello New
3 Hello Old
4 Hello Old

I need my view to look like this:

Identity Col Col 1 Col 2
1 Hello New
2 Hello Old

I hope I am making sense here. . . Any ideas?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-14 : 12:19:30

Get data except Identity column
Group by the other 2 fields
Insert those 2 fields

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-14 : 12:20:18

Instead of a view, can you use a stored proc?

If so then:


declare @tbl as table (id int identity(1,1), col varchar(10),col1 varchar(10))

insert into @tbl
select distinct Col,Col1 from table

select * from @tbl

JScottP99
Starting Member

5 Posts

Posted - 2006-03-14 : 12:28:03

quote:
Originally posted by Srinika

Get data except Identity column
Group by the other 2 fields
Insert those 2 fields

But I need the identity column. If there are 3000 rows in the table I need 1500 of them (given that every two rows has the same data) but I also need a unique identifier for each row (the identity column). I hope I have not misunderstood your response.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-14 : 12:44:44

JScottP99,
U have to have the identity column in the table, but u must not enter data to it.
When u insert to the other columns, it'll automatically put its identity in it

JScottP99
Starting Member

5 Posts

Posted - 2006-03-14 : 14:35:02

quote:
Originally posted by Srinika

JScottP99,
U have to have the identity column in the table, but u must not enter data to it.
When u insert to the other columns, it'll automatically put its identity in it

Sorry, I am such a newbie at this .. Yes, that works great! Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources