Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    41

    Unanswered: copying all rows from one table into another existing table and overwriting data

    i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.

    any ideas on how i could do this easily?

    thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    SELECT * INTO myTable1 FROM Employees
    GO
    
    -- Have a Look
    
    SELECT * FROM myTable1
    
    -- Make a Backup
    
    SELECT * INTO myTable2 FROM myTable1
    GO
    
    -- oops 
    
    DELETE FROM myTable1 WHERE EmployeeID BETWEEN 2 AND 6
    GO
    
    -- Have a Look
    
    SELECT * FROM myTable1
    GO
    
    SET IDENTITY_INSERT myTable1 ON
    GO
    
    -- Recover the Rows
    
    INSERT INTO myTable1 (
    			  EmployeeID
    			, LastName
    			, FirstName
    			, Title
    			, TitleOfCourtesy
    			, BirthDate
    			, HireDate
    			, Address
    			, City
    			, Region
    			, PostalCode
    			, Country
    			, HomePhone
    			, Extension
    			, Photo
    			, Notes
    			, ReportsTo
    			, PhotoPath
    )
         SELECT 		  EmployeeID
    			, LastName
    			, FirstName
    			, Title
    			, TitleOfCourtesy
    			, BirthDate
    			, HireDate
    			, Address
    			, City
    			, Region
    			, PostalCode
    			, Country
    			, HomePhone
    			, Extension
    			, Photo
    			, Notes
    			, ReportsTo
    			, PhotoPath
           FROM myTable2
          WHERE EmployeeID NOT IN (SELECT EmployeeID 
    				 FROM myTable1)
    GO
    
    SET IDENTITY_INSERT myTable1 OFF
    GO
    
    -- Have a Look
    
    SELECT * FROM myTable1
    GO
    
    -- Clean up
    
    DROP TABLE myTable1
    DROP TABLE myTable2
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •