selecting one row and inserting it into another table one by one

i ve a table named "Employee" in wh i ve fields "Emp_Id","Name" and "Email". i want to select each employee "Emp_Id" one by one from "Employee" table and insert it into another table named "Concerned_Department". like if i ve three rows in "Employee" table with "Emp_Id" 1,2 and 3 respectively,in this case i want to first select "Emp_Id"=1 then "Emp_id"=2 and lastly "Emp_id"=3 and insert them into "Concerned_Department"table.

 after that the process starts again like inserting "Emp_Id"=1 in "Concerned_Department" and so on.

plz note that i want to insert "Emp_Id" one by one not all the fields together.

 can u plz tell me the query for that wh i ve to write in SQL

Thanks and happy valentine day to all of u

 regards

Ahmed Bilal Jan


Failure is not but aiming Low is a Crime.

Ahmed Bilal Jan
University of Engg and Tecnhnology,Taxila
Pakistan.
0
bilal_jan321
2/14/2008 3:57:18 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
888 Views

Similar Articles

[PageSpeed] 31

One way you could do it is by using a cursor, looping through the Employee table, getting the Emp_id and then inserting into the Concerned_Department table:

 DECLARE @Emp_id int

DECLARE EmpCursor CURSOR FOR
SELECT Emp_id FROM Employee

OPEN EmpCursor
FETCH NEXT FROM EmpCursor INTO @Emp_id


WHILE @@FETCH_STATUS = 0
BEGIN

INSERT Concerned_Department (Emp_id) VALUES (@Emp_id)

FETCH NEXT FROM EmpCursor INTO @Emp_id

END

CLOSE EmpCursor
DEALLOCATE EmpCursor

0
CaptKangaroo
2/14/2008 4:26:08 PM

i ve tried that cursor.it worked great
but some questions
as soon as run the cursor all the "Emp_Id" fields from "Employees" table are inserted one by one into the "Concerned_Department"table "Emp_id"field.

i want this but with some pause like if i have a complaint registered into the "Comp_table" then
i want that first "Emp_ID" from "Employees" table to be inserted into the "Concerned_department"table "Emp_ID" field.
then on the second insertion in the "Comp_Table" ,second "Emp_ID" from "Employee" table must be inserted into the "Concerned_Department"table "Emp_id" field. and so on.
i want this to be done from asp.net like user will register a complaint in "Comp_table" from an asp.net webpage
and after he inserts a row in the "Comp_table" at that moment i want to insert first "Emp_Id" from "Employees" table into "Concerned_department"table "Emp_id" field.
i know how to call stored procedure from ASP.NET(C# as code behind).but plz tell me how can cursors be used/called from ASP.NET

as stored procedures can be found in Database>Programmability>Stored Procedures.where can i see cursors created

thnx 4 ur consideration
Ahmed Bilal Jan


Failure is not but aiming Low is a Crime.

Ahmed Bilal Jan
University of Engg and Tecnhnology,Taxila
Pakistan.
0
bilal_jan321
2/15/2008 5:34:43 PM

If you want to do the insert from a web page, a trigger on the table. Running the cursor from a page will cause the query to loop through all of the records in the Employees table every time, that is actually not necessary and will have a major performance impact on the application. If you want more info on triggers and how to create them, have a look at : http://www.sqlteam.com/article/an-introduction-to-triggers-part-i


If you do want to use a cursor to do this, you can do a lookup while the cursor is executing and do something based on the lookup. For example, in the first example, you could have the following statement where the cursor is looping through the records:


INSERT Concerned_Department (Emp_id) VALUES (@Emp_id)


IF EXISTS (SELECT EmployeeID FROM Comp_Table WHERE EmployeeID = @Emp_id)
BEGIN
   --Do something here
END


FETCH NEXT FROM EmpCursor INTO @Emp_id
END

As far as the viewing of Cursors in Management Studio goes, you won’t be able to view them like the Stored Procedures and functions. They are dynamic queries, just like Select or Update statements. You can however create a stored procedure with a cursor in it, executing the stored procedure will then run the cursor.

 

0
CaptKangaroo
2/18/2008 4:24:34 PM
Reply:

Similar Artilces:

SELECT a single row with one SqlDataSource, then INSERT one of the fields into another SqlDataSource
What is the C# code I use to do this? I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.Disco Patrick writes web applications using:Visual Studio 2008ASP.NET 3.5C#SQL Server 2008 What do you mean by "insert one of the fields into another SqlDataSource"?  Do you mean you want to make use of the value as a parameter for another datasource control or that you actually want to use another SqlDataSource to insert the value into a table so...

insert multiple values from one table into one column from another table?
Hi, I'm attempting to create an insert statement that gets the values from multiple columns and insert them into one column from another table, is there a simple solution to do this?Here is something similar to what i'm wanting done, just it's not the correct way to do it, i want value1, value2 and value3 from one table to be combined and put into the value1 column of another table, what am i doing wrong? INSERT INTO tablename2 (value1 + value2 + value3)SELECT value1FROM tablename1 thanks in advance! INSERT INTO tablename2 (SELECT val1 + val2 + val3 FROM tabl...

Get records from one table and insert into another one
I make that but I did not work, thanks  SqlCommand cmd2;cmd = new SqlCommand("SELECT CS_ID FROM Tbl_Conselheiros WHERE CS_TIPO IN (5,6,7)", conn); dr = cmd.ExecuteReader();  while (dr.Read()) {cmd2 = new SqlCommand("SP_PRES_GRAVAR", conn); cmd2.CommandType = CommandType.StoredProcedure;SqlParameter id = new SqlParameter("@ID_CONSE", SqlDbType.Int, 4);id.Value = Convert.ToInt32(dr[0]); cmd2.Parameters.Add(id);SqlParameter data2 = new SqlParameter("@DATA", SqlDbType.VarChar, 8); data2.Value = FormataData(); cmd2.Parameters.Add(data...

One-to-One or Many-to-One?
 Hi Friends,I've made huge database for Learning Management Systems which has around 30 - 40 tables.I was checking all the relationships between tables and everything seems fine and well defined. But, it's 4:22 AM here and I'm bit sleepy but I was thinking which of the following relationship is true between the following two tables:Table1: tbl_users (PK username, password, name, surname, email, address1, address2, phNumb)Table2: tbl_testAttempts (PK Id, username, testId, attempt, timeStart, timeEnd)As you see, the Primary key of tbl_users is foreign key in tbl_testAttempts. ...

One to Many to One to One
I have a kinda small problem; if someone could help me that' would be niceTo keep things simplier, allow me to give the following exampleI have two tables one-to-many relationship,table1: itemtable2: itemhistoryitem has many itemhistorybasically i want retrieve all the records in 'item' sorted by 'name', along with ONE "latest" itemhistory" (date). If there aren't any historyhistory for a particular 'item', just allow null values. egcreate table #item(itemid int, name varchar(10))create table #itemhistory(itemhistoryid int, itemid int, date...

can I compare one record from one table to another record in another table?
can I compare one record from one table to another record in another table? I need to test if this condition is met. My scenario is: i need to check one table for a value and compare it to a list of records. for example, table 1           columnrecord 1- applesrecord 2- orangesrecord 3- grapes table 2            col1   col2   col3    col4record 1- Null , Null, Null, grapes I need to compare table 2 records with a list of records from table 1. the fru...

Selecting data from one table, and counting from another in one query. Help!
Hello,I wrote a news system that has a commenting system. Instead of having to actually keep track of the number of comments inside the news table, I want to get the count directly from the NewsComments table. So my tables:News: id, author, subject, postdate, news, category, commentcountNewsComments:Id, Author, Message, postDate, IP, Browser, NewsId As you can see, when a new comment is posted I have to update the comment count. I don't want to do that. I basically need to select all rows from the news (WHERE Category = 'CATID'), and at the same time COUNT the number of COMM...

select all row one more than one column
   I add checkbox control in header template to select all row. if one column have select all propert there is no problem. However, when add second column and add select all another column there occur a problem. When ı select first column select all checkbox, second colum checkbox checked. How can ı solve this problem.          Hi,Following links will help you out ...http://programming.top54u.com/post/ASP-Net-GridView-Checkbox-Select-All-using-C-sharp.aspxhttp://aspnet.4guysfromrolla.com/articles/053106-1.aspxhttp://aspnet.4guy...

CheckBoxList Databinding, Select from One Table, Insert Into another Table
Newbie here! I've looked all over the place to see if I find some code that can get me started with this project, here it goes guys, as usual, all help is appreciated... SQL Server, I have 1 table tblServices that shows all of the services we offer, with 3 fields, such as: ID, CategoryCode, CategoryDescription. When loading my web form I want to offer all of these service options showing them the "CategoryDescription" in a checkboxlist option for my users to click on, and then submit the form into a second table. The second table "tblEx...

Stored Procedure
I wish to select all post from Table Category and the first row in Product table. SQL goes (not working just to show you how im thinking): SELECT Category.CategoryID, Category.Title, TOP (1) Product.ImagePath, TOP (1)  Product.CategoryID FROM Category INNER JOIN Product ON Category.CategoryID = Product.CategoryID GROUP BY Category.CategoryID, Category.Title Thanks for your help!  SELECT Category.CategoryID, Category.Title, Product.ImagePath,   Product.CategoryID FROM Category crossJOIN (select top 1 * from  Product ) PGive a man a fish and you feed him for ...

SQL: mutiple rows one column to multiple columns one row
In InfoMaker SQL can you take a table with multiple rows for the same key and take a column from the rows and create mutiple columns with one report line with the key(no duplicate keys) on a report? For example Table books (key) (book title) 123 misery 123 titanic 123 roots 456 war 456 1984 456 giant The report I need would look like this Key title1 title2 title 3 123 misery titanic roots 456 war 1984 giant Thanks in advance. -- Kaye Hendry HealthInsight email:kaye.hendry@healthinsight.org ...

Selecting from one table inserting into another
hi, i am sure this is very simple, but its just got me stuck! i have a table which i use for inserting products, now its not very exciting to see the cat_id so i wanted to put a dropdown box on there which would show the cat_description from tbl_cat and then put the tbl_cat.cat_id into tbl_products.cat_id any help would be greatful  JezJeremy HusonSenior Network EngineerCarden Computers - PC Repair Brighton Hi Jezh, To display the description of a catelog in a dropdownlist, you will need to have 2 ObjectDataSources. Please check the following sample for more information:(VB Drop...

selecting from one table, inserting into another
hi there, i have a page where on my button click i need to select address, pcode from tbl_person where person_id = querystring and update or insert those values into tbl_del where order_ID = querystring 2 below is the code i am using, although im stuck on the sql stringSqlConnection objConnAddy = new SqlConnection(sConnectionStringCustInfo);using (objConnAddy) { objConnOID.Open(); // insert values into delivery table, second line updates price + VAT string sqlAddy = "?????"; SqlCommand objCmdAddy = new SqlCommand(sqlAddy, objConnAddy); objCmd1.ExecuteNonQuery(); i had a loo...

is there an sql statement to insert data to one table and getting the data from another table
Ok I have a table that have general categories with just name and CatNo. Now what I want to know is when I create a client to move all the data in general categories to another table called categories that have the clientid to associate it with the client. I would appreciate for any help what is your question here??Shravan Addaypally MCP...

Web resources about - selecting one row and inserting it into another table one by one - asp.net.sql-datasource

Consultation Document on the Methods for Selecting the Chief Executive and for Forming the LegCo in 2012 ...
The " Consultation Document on the Methods for Selecting the Chief Executive and for Forming the LegCo in 2012 " was a document published on ...

Voting Machine Caught on Camera Casting Ballot for Democrat when Selecting Republican - YouTube
**UPDATE.... I've heard many reports that this is "just a calibration issue." I will say this... when I went to page 2 and began voting for city ...

Selecting The Right Jeans
Fit, style, cut, colour and wash all factor into the pursuit for great denim.

Clock ticking on selecting successor to Leigh Clifford at Qantas
With Qantas on course to chalk up its largest annual profit since 2008, the question now is whether chairman Leigh Clifford is looking to hang ...

Why are the reserves selecting the team?
Backbenchers are the reserves of the parliamentary system. They are newcomers.

Jackie French: Why selecting the right place for your plants is important
The secret of growing anything easily is to choose the best spot for what you want to grow.

Liberals begin 'super weekend' of selecting delegates
A race for the top job in the federal Liberal party gained momentum on Friday as rank and file party members began the selection process for ...

Boston Marathon bombing trial: Judge, lawyers face challenges in selecting jurors - CTV News
As the second phase of jury selection began in the federal death penalty trial of Boston Marathon bombing suspect Dzhokhar Tsarnaev, it immediately ...

Selecting cabinet one of first orders of business for Trudeau
One of the first orders of business for any new prime minister is picking a cabinet. Who is likely to make the cut?

Agile Analysis, Self-Selecting Teams, TDD & BDD in Methods & Tools Winter 2014 issue
... for software developers, testers and project managers – has just published its Winter 2014 issue that discusses Agile Analysis, Self-Selecting ...

Resources last updated: 11/22/2015 12:58:37 AM