Functions in SQL Server......
-
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.
-
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
SELECTwith aCURSOR. 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 byJOINing the two tables in aSELECT. 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)
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
-
You can loop over the result set of a
SELECTwith aCURSOR. 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 byJOINing the two tables in aSELECT. 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)
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
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)
-
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.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
-
Do two inserts, first gets the IDs from the master table. The second has a join and inserts the data from the detail table.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
-
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.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
-
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.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
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)
-
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?
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
-
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?
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
-
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
INSERTstatement 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 anORDER BYclause. The last column is used in theSELECT's column listSELECT Combined
FROM MyTable
ORDER BY Menu, SubMenu, DetailThe
INSERTstatements would need to be altered to add the additional data to your table, with nulls in place where there is no relevant information.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
-
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
INSERTstatement 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 anORDER BYclause. The last column is used in theSELECT's column listSELECT Combined
FROM MyTable
ORDER BY Menu, SubMenu, DetailThe
INSERTstatements would need to be altered to add the additional data to your table, with nulls in place where there is no relevant information.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website