How to accompilish this in sql? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to accompilish this in sql?

Author

Topic

ssiss
Starting Member

10 Posts

Posted - 2009-02-06 : 20:23:44

TableA has 2 columns

ColumnA, columnB
2845, A
2845, B
2845, C
2848, A
2848, B

I need to populate TableB which has only one column
(PK)
Col1 which is Auto generated PK
1
2
3
4
5

and TableC which is a junction between TableA and TableB

(PK) (FK) (FK)
Colum1, Colum2, Column3, colum4
1 2845 1 A
2 2845 2 B
3 2845 3 C
4 2848 4 A
5 2848 5 B

My destination TableC should look like above

How do I accomplish this in SQL?

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 22:36:06

Why you need junction table over here?

ssiss
Starting Member

10 Posts

Posted - 2009-02-06 : 23:01:49

quote:
Originally posted by sodeep

Why you need junction table over here?

I need a junction table, because TableB has several other columns and primary key of TableB is used as foreign key in several other tables.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-02-06 : 23:38:30

u r requirement is not clear, but try this

SELECT	A.ColumnA
		, B.Col1
		, A.ColumnB
FROM		(
		SELECT *, ROW_NUMBER() OVER( ORDER BY ColumnA, ColumnB) AS 'RowSeq'
		FROM TableA
		) A
INNER JOIN	TableB B ON A.RowSeq = B.Col1

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 09:07:24

isnt it just a matter of adding an two columns in tableC and populating them using ROW_NUMBER() function as Peter pointed out?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources