none
saving the result of query in new table in data base RRS feed

  • Question

  • when i use a query like this"selelct address id from address" ,the result of that save in data set ,that is virtual,that's meant it do'nt save in data base.

    now if i want  to save the result of this query in a new table in this data base ,how can i do this?

     

     

    Friday, January 25, 2008 1:10 PM

All replies


  • Hi!

    If you are using Microsoft SQL Server Express then add a new storedprocedure, use the following sql-code:


    CREATE PROCEDURE dbo.UpdateUser

    (

              

               @LastName varchar(50),

               @FirstName varchar(50),

               @Address varchar(50),

               @City varchar(50),

               @State char(2),

               @ZIP varchar(10),

               @CustomerID int,

               @Modified timestamp OUTPUT

    )

    AS

     SET NOCOUNT ON;

     

     UPDATE User SET  

               LastName = @LastName,

               FirstName = @FirstName, 

               Address = @Address, 

               City = @City, 

               State = @State, 

               ZIP = @ZIP

     WHERE (UserID = @UserID) AND (Modified = @Modified) ;

    <!--[if !supportEmptyParas]--> <!--[endif]-->

     IF @@ROWCOUNT>0 AND @@ERROR=0

     -- Selects the updated timestamp which is put back into the dataset

       SELECT @Modified = Modified

       FROM User

       WHERE (UserID = UserID);

     ELSE

     -- Report an update conflict if there was one

       RAISERROR 50000 'Concurrency violation on table User.'


    This ii my only example of code.

    I wish you the Best of luck!
    Friday, January 25, 2008 1:42 PM
  • Hi,

     

    There a few ways to do this padari. Your running the select query and storing the data in a dataset so you must know a bit about ADO.NET. There is a class in ADO.NET called the DataAdapter, its there to act as a bridge between database and dataset. You can use the DataAdapter to both run your SELECT query taking the results and filling a dataset with them. You can also use the DataAdapter to then run a INSERT and UPDATE commands to take the information from the dataset and post them back to the database. Have a look in the help files for the DataAdapter class.

     

    Another option, depending on what database your using and on what you need to do, is you can run an INSERT INTO statement against the database, like the example below, this will make the database just copy the rows directly between tables meaning you don't need to fill a dataset. I don't know if this will work with Access database though.

     

    INSERT INTO MyTable  (Key, Description)
           SELECT Key, Description
           FROM AnotherTable

    Friday, January 25, 2008 1:54 PM
  • If you're using SQL Server, you can use SELECT INTO, which does exactly what you describe.

     

    Friday, January 25, 2008 7:12 PM