|
I want to loop one SELECT statement and get its value in a variable. Base on that variable and I want to loop Another SELECT statement and get it value in another variable. This both variable I want to put in one table which function will return from SQL Server.
|
|
|
|
|
You can loop over the result set of a SELECT with a CURSOR. However, cursors should be used as a last resort because they are very slow. SQL is a set based language. In other words it operates most efficiently on sets of data rather than processing one row at a time.
From the description of your problem it looks like there is a strong possibility of a set based answer. You can do this by JOINing the two tables in a SELECT. If you want help with that you will need to supply more information. The relevant columns in the source tables (including primary keys, foreign keys and output columns)
|
|
|
|
|
Thanks for ur help Sir.
I can use JOINS but it wil give only one or both column from the tables...
I want only one column...in which both the column from both table should come... and cursors are very slow... as i want use it in a website then wil b of no use....
I wil give one scenario.....
Suppose i ve two tables say master and detail. In Master i m taking data with SELEC statement, in wich i got unique value say ID. Now this master table's ID Column i want to loop Row By Row and get data from detail table. and input it in one single column of the new table.
e.g
ID = 1234 (Master Table)
From Detail Table i ve Foreign key as PID, wich der are multiple records..
now my new table should reflect like this...
New Table
NewColumn
1234
ABC
XYZ
5678
DEF
TUV
where all the numeric data is from Master table and Alphabetic is from Detail table...
for this i want to use user-define functions from SQL.
-- modified at 2:44 Thursday 15th March, 2007
Regards,
Smart Boy
Mumbai,
(INDIA)
|
|
|
|
|
Do two inserts, first gets the IDs from the master table. The second has a join and inserts the data from the detail table.
|
|
|
|
|
Thanks Sir,
Dats true but how i wil loop it i.e the syntax....
Regards,
Smart Boy
Mumbai,
(INDIA)
|
|
|
|
|
You insert it like this:
INSERT INTO NewTable(ColumnName)
SELECT ID
FROM Master;
INSERT INTO NewTable(ColumnName)
SELECT SomeColumn
FROM Detail
INNER JOIN Master ON Master.ID = Detail.PID;
No looping is required. SQL is a set based language. It is designed an optimised to operate on large quantities of data in a single operation.
|
|
|
|
|
thanks sir,
here is the code wich u ve given and results this output.
INSERT INTO menu(menus)
SELECT
'Main - '+ menuname
FROM modulemast
INSERT INTO menu(menus)
SELECT
'Detail - '+optionname
FROM modules
INNER
JOIN modulemast ON modulemast.PID = modules.PID
menus
-------------------
Main - Transactions
Main - Masters
SubMain - Accounts
Detail - Equity
Detail - Derivatves
Detail - Mutual Fund
Detail - Bank
Detail - Cash
Detail - JV
Detail - Bank Reco
Detail - Accounts
Detail - Banks
But... I want want in this fashion ..... so how could i do it....
menus
-------------------
Main - Transactions
Detail - Equity
Detail - Derivatves
Detail - Mutual Fund
SubMain - Accounts
Detail - Bank
Detail - Cash
Detail - JV
Detail - Bank Reco
Main - Masters
Detail - Accounts
Detail - Banks
Please Help me 4 this scenario......Pleaseee....
Regards,
Smart Boy
Mumbai,
(INDIA)
|
|
|
|
|
Databases are set based - That means there is no order. The implementation of most database systems give the illusion of order, but that is just a by-product of the algorithms used in the implementation. There is no guarantee that the perceived order will be preserved if you migrate to a new database, upgrade your existing database, or possibly even if you apply a service pack.
Next, Where does this "Submain" come from - the code you showed cannot create that row?
|
|
|
|
|
Sir,
I didnt got ur First Point....
And regarding Submain1 it just a column in table which i ve not inclucded. Its just a reference.....
Regards,
Smart Boy
Mumbai,
(INDIA)
|
|
|
|
|
Smart_Boy wrote: I didnt got ur First Point....
You want the data to be inserted in a specific order. You cannot. The table contains no order. Any perceived order is an illusion created by the specific implementation of the database.
If you attempt to add an order by class to an INSERT statement it will come back with an error. The order data is inserted is not necessarily the order in which it is stored. The order in which the data is stored may change. It is outwith your control.
If you want the data to be retrieved in a specific order then you have add some markers to the table so that it can be done.
e.g.
The table contains the columns:
Menu, SubMenu, Detail, Combined
The first three columns can be used in an ORDER BY clause. The last column is used in the SELECT's column list
SELECT Combined
FROM MyTable
ORDER BY Menu, SubMenu, Detail
The INSERT statements would need to be altered to add the additional data to your table, with nulls in place where there is no relevant information.
|
|
|
|
|
Thanks Sir,
I forgetted dat i ve given order column in the table.
And I used UNION to join 2 tables, and then given order by order column.
Thank you Sir for ur help..
My task is Achieved......
Regards,
Smart Boy
Mumbai,
(INDIA)
|
|
|
|
|
Do SQL Server 2000 and 2005 co-exist peacefully on the same development machine, or should I uninstall 2005 before installing 2000?
-- moved at 17:18 Tuesday 13th March, 2007
--------------------------------
"All that is necessary for the forces of evil to win in the world is for enough good men to do nothing" -- Edmund Burke
|
|
|
|
|
I have SQL server 2000 and SQL server express 2005 installed, and no problem occurred.
|
|
|
|
|
Thanks for your post. I'll give it a try.
--------------------------------
"All that is necessary for the forces of evil to win in the world is for enough good men to do nothing" -- Edmund Burke
|
|
|
|
|
We can't install lower version of SQL Instance, if we have higher version in it
But its possible for reverse (SQL 2000 and then SQL 2005)
Bala, Hero ITES, Gurgaon (INDIA)
|
|
|
|
|
"We can't install lower version of SQL Instance, if we have higher version in it"
its not the case. We can still install lower version, yeah some unexpected problems might happen and sometimes , you have to break head with the nearest wall.
In my case, I installed SQL 2005 Express and then SQL 2000 Enterprise on my machine, When i tried running DotNetNuke there, it said "Failed to generate server instance of SQL Server" something like that.
Recommended: I think you better use only one , or if you want two, go from 2000 to 2005. Btw, 2007 is in line now 
|
|
|
|
|
It shouldnt be a problem at all.
Keshav Kamat
India
|
|
|
|
|
hi
i'm trying to connect to the server without giving the static connection string.
i want my application to access the connection string automatically. i'm using the follwoing code, but it doesn't give the connection string
ConnectionString = @"";
conn = new OleDbConnection(ConnectionString);
cmd = conn.CreateCommand();
can u please explain that what am i missing, as i've already used the same code once and it is still working, but now that i am using the same code at some other location in the application it is not working.
thanku for the time
|
|
|
|
|
ConnectionString = ConfigurationManager.ConnectionStrings("YourConnectionString")
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
|
i have two table first table is "book " .that i keep book information in this and the next table is "Translater" that i store name,lname,id of translaters in it.
i want to create a query to see all information of all book with and without translater
such as:
--title -transName---transLastName
..... ...... .......
and if a book has not any translater fill that filed free in query
i use a table to join them to each other "BT"
that contain book.id and trans.id but when execute query i just see the book that have translater
and i can't see all book
how can i see all of my book?
sepel
|
|
|
|
|
Use a LEFT JOIN on the Book table.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
|
Hi:
We are combining alot of old applications which each have their own tables and structures within those tables...not really compatible enough to combine straight out...but they could all use the "text" or similar sql server type and be put into one or two "super" tables with a type to differentiate them.
On one side, this would vastly simplify our schema and allow the data to be centralized. On the other, its not intuitive and could be confusing if we fall behind with our database schema documentation. But more importantly, having all of these applications hitting these one or two tables puts a fair amount of stress on our server.
This table might look something like this:
Application 1 is type 1
Application 2 is type 2
Application n is type n
Text1--Text2--Text3...--Textn--Type
val1 val2 val3 valn 1
val1 val2 val3 valn 2
val1 val2 val3 valn n
Any thoughts of this issue would be appreciated.
Thanks!
|
|
|
|
|
A database should contain data that is related, assuming you are talking about a RDBMS. If the data is not related then putting it all together in a few master tables will be confusing years from now, or even tomorrow if there is enough of it.
You could use master DBs for things like zip code and other common fields but to try to mash everything together would be a big mistake IMHO.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
