how can I want to populate the rows sequentially in the destination?

Question

how can I want to populate the rows sequentially in the destination?

ronsql

Old Hand

Points: 382
More actions
March 14, 2009 at 9:52 am

#201566

Hi All,
I have requirement like to populate the row by row sequentially in destination table using SSIS.
Its look like
Seq no col2
1 x
2 x
3 x
1 y
2 y
one more scenario is
Seq no col2
1 x
1 y
2 x
2 y
3 x
What is the approach to get the output like above.
Could any one please give the solution fro me as early as possible?
its very urgent to me.
Thanks,
Naidu.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

You just need 2 connections & 1 data flow task, within the flow task you will have source and destination components. And in the source, you select SQL command as the source and write a query that orders the data according to your requirement. That's it.

--Ramesh

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

There is no guarantee that when viewing data from a database table that the rows will be displayed in the sequence in which they were input. You control the order in which the rows will be returned when you compose the T-SQL statement that extracts the desired data from the table, and that can be accomplished by using the ORDER BY statement.

For a simple example:

SELECT [Seq No], Col1, Col2 FROM mytable

ORDER BY [Seq No], Col2

Refer to Books On Line

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/764070d4-23dd-4373-8500-6c77d956ba23.htm

for further examples

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

ronsql Old Hand Points: 382 More actions · Answer 3

i have to populate record by record sequentially based on the number of times the increments on X or Y.

For example:

Seq no col Seq no col

1 x or 1 x

1 y 2 x

2 x 1 y

2 y 2 y

i have to display either way in the destination table.based on the increments of a row.

Thanks,

Naidu.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

ROW_NUMBER() is what are looking for, Check Books Online for more info..

SELECT ROW_NUMBER() OVER( PARTITION BY Col2 ORDER BY Col2 ) AS SeqNo, Col2

FROM SomeTable

--Ramesh

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

Using:

SELECT [Seq No], Col1, Col2 FROM mytable

ORDER BY [Seq No], Col2

will give you

Seq no col2

1 x

1 y

2 x

2 y

Using:

SELECT [Seq No], Col1, Col2 FROM mytable

ORDER BY Col2, [Seq No]

will give you

Seq no col

1 x

2 x

1 y

2 y

I urge you to refer to Books On Line

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/764070d4-23dd-4373-8500-6c77d956ba23.htm

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]