select

Question

select

reddy06

SSC-Addicted

Points: 403
More actions
April 14, 2010 at 10:07 am

#231679

I am having a temporary table with 10 rows and 10 columns
I need to insert each cell into a new table.
How can i find the cell values like cell(1,2), cell(4,4) .....
Thanks in advance

Viewing 13 posts - 1 through 12 (of 12 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

reddy06 (4/14/2010)
I am having a temporary table with 10 rows and 10 columns
I need to insert each cell into a new table.
How can i find the cell values like cell(1,2), cell(4,4) .....
Thanks in advance

You want to insert data or read from the table?

Have you read Insert/Select statements on BOL?

Pradeep Singh

reddy06 SSC-Addicted Points: 403 More actions · Answer 2

I need to select values of each cell from temp table and insert them into other table in a single column one by one

seth delconte SSCertifiable Points: 6388 More actions · Answer 3

reddy06 (4/14/2010)
I need to select values of each cell from temp table and insert them into other table in a single column one by one

So you want to select all 100 values (10 rows * 10 columns) and insert them into 100 rows in ONE column in a new table?

_________________________________
seth delconte
http://sqlkeys.com

ps. One Orange Chip Points: 29422 More actions · Answer 4

reddy06 (4/14/2010)
I need to select values of each cell from temp table and insert them into other table in a single column one by one

You didnt reply to the second question.

something like this?

insert into table1(col1, col2, col3 ....)

select col1, col2, col3 from table2

Pradeep Singh

reddy06 SSC-Addicted Points: 403 More actions · Answer 5

in the new table i should insert 100 records

new table has 5 columns

i have the hardcoded values of 1st 4 columns

and the 5th column value should be cell(1,1) of temp table for 1st record

insert into 2nd table

values(1,2,3,4, cell(1,1) value of temp table)

insert into 2nd table

values(3,6,2,4, cell(1,2) value of temp table)

...

100 records should be inserted in 2nd table

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

I think you need to write this out with some sample data, and explain how you get those values. What is the second table? The new one? Is the first table the temp table?

How do you go from 1, 2, 3, 4 to 3, 6, 2, 4? No pattern I can see there.

There is no "cell" in a table in a relational database. You have data in columns, and you select the columns with SELECT col1, col2, etc.

For rows, you need to have a way to identify the row, and typically we use a WHERE clause here.

WHERE COL1 = 1

reddy06 SSC-Addicted Points: 403 More actions · Answer 7

1st table is temp table (10X10)

2nd table is Table2 with 5 columns

i need to insert the cell values of 1st table (i.e 100 cells) into 100 rows in 2nd Table. The value should be inserted in 5th column of 2nd Table.

The first 4 column values keep them as null for all records.

seth delconte SSCertifiable Points: 6388 More actions · Answer 8

reddy06 (4/14/2010)
in the new table i should insert 100 records
new table has 5 columns
i have the hardcoded values of 1st 4 columns
and the 5th column value should be cell(1,1) of temp table for 1st record
insert into 2nd table
values(1,2,3,4, cell(1,1) value of temp table)
insert into 2nd table
values(3,6,2,4, cell(1,2) value of temp table)
...
100 records should be inserted in 2nd table

This will put all the values in one column, although I'm not sure if it's in the order you're looking for:

INSERT secondtable (columnfive)

(SELECT columnone from firsttable

UNION ALL

SELECT columntwo from firsttable

UNION ALL

...

SELECT columnten from firsttable)

_________________________________
seth delconte
http://sqlkeys.com

ps. One Orange Chip Points: 29422 More actions · Answer 9

Seth, I guess this is what the OP is looking out for..

Pradeep Singh

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 10

Seth's answer should work for you. Note that ordering isn't going to matter here to SQL Server. You would need some ORDER BY clause to get data back in a particular order.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 11

This is either homework or one of the top five most weird business specs I've ever seen.

By the way, there is not such a thing as a cell in an rdbms, there are rows and columns.

Here is what your professor is looking for...

1- Load all columns of all rows from source table into a cursor.

2- Loop#1 - until cursor is empty

3- Fetch from cursor

4- Loop#2 - Insert ten rows on target table based on values on fetched cursor.

5- endloop, endloop

...last class she/he did mention the word "loop", isn't it? 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

WayneS SSC Guru Points: 95460 More actions · Answer 12

PaulB-TheOneAndOnly (4/14/2010)
This is either homework or one of the top five most weird business specs I've ever seen.

I'd vote for this being the top 1 weird business spec :w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2