none
copy data from one database table to another database table

    Question

  • hi
    i have two databases in same server. i have 5 similar tables in both databases, now i want to make stored procedure in sqlserver 2005, to copy the data from tables of one database to corresponding tables of another database. thanks in advance for help .
    Wednesday, February 04, 2009 9:16 AM

Answers

  • You can build a stored procedure using INSERT...SELECT . See T-SQL example below which moves data from AdventureWorks2008 to tempdb. There are issues though, such as identity columns and constraints in the target tables. In the sample below the identity property is turned off by the CONVERT function in the target database.  Let us know if helpful.

    USE tempdb;  
     
    SELECT TOP(0) [ProductID]= CONVERT(int,ProductID)  
          ,[Name]  
          ,[ProductNumber]  
          ,[MakeFlag]  
          ,[FinishedGoodsFlag]  
          ,[Color]  
          ,[SafetyStockLevel]  
          ,[ReorderPoint]  
          ,[StandardCost]  
          ,[ListPrice]  
          ,[Size]  
          ,[SizeUnitMeasureCode]  
          ,[WeightUnitMeasureCode]  
          ,[Weight]  
          ,[DaysToManufacture]  
          ,[ProductLine]  
          ,[Class]  
          ,[Style]  
          ,[ProductSubcategoryID]  
          ,[ProductModelID]  
          ,[SellStartDate]  
          ,[SellEndDate]  
          ,[DiscontinuedDate]  
          ,[rowguid]  
          ,[ModifiedDate]  
      INTO dbo.Product  
      FROM [AdventureWorks2008].[Production].[Product]  
    GO  
     
    INSERT dbo.Product  
    SELECT * FROM [AdventureWorks2008].[Production].[Product]  
    GO  
     
    SELECT COUNT(*) FROM dbo.Product  
    GO  
    -- 504  
    INSERT dbo.Product  
    SELECT * FROM [AdventureWorks2008].[Production].[Product]  
    GO  
     
    SELECT COUNT(*) FROM dbo.Product  
    GO  
    -- 1008  
     
    DROP TABLE tempdb.dbo.Product  
    GO 


    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    Wednesday, February 04, 2009 1:06 PM

All replies

  • Try this,
    This helps you in copying tables from one DB to other

    1. Right Click on Database in which tables exists
    2. Select Task
    3. Click on Export Data and  Click on Next
    4. Select Required ServerName and Database and  Click on Next
    5. Select Required ServerName and Database and Click Next
    6. Select Copy data from one or more tables or rows radio button and Click on Next
    7. Select Required tables and Click Next and then  Click on Finish


    Rajesh Jonnalagadda http://www.ggktech.com
    Wednesday, February 04, 2009 9:29 AM
  • hi rajesh,
    thanks for replying,
    but i do need to implement through coding. The copy activity should occur on clicking button, what will be the idea behind it. Thanks for help.
    Wednesday, February 04, 2009 10:05 AM
  • You can build a stored procedure using INSERT...SELECT . See T-SQL example below which moves data from AdventureWorks2008 to tempdb. There are issues though, such as identity columns and constraints in the target tables. In the sample below the identity property is turned off by the CONVERT function in the target database.  Let us know if helpful.

    USE tempdb;  
     
    SELECT TOP(0) [ProductID]= CONVERT(int,ProductID)  
          ,[Name]  
          ,[ProductNumber]  
          ,[MakeFlag]  
          ,[FinishedGoodsFlag]  
          ,[Color]  
          ,[SafetyStockLevel]  
          ,[ReorderPoint]  
          ,[StandardCost]  
          ,[ListPrice]  
          ,[Size]  
          ,[SizeUnitMeasureCode]  
          ,[WeightUnitMeasureCode]  
          ,[Weight]  
          ,[DaysToManufacture]  
          ,[ProductLine]  
          ,[Class]  
          ,[Style]  
          ,[ProductSubcategoryID]  
          ,[ProductModelID]  
          ,[SellStartDate]  
          ,[SellEndDate]  
          ,[DiscontinuedDate]  
          ,[rowguid]  
          ,[ModifiedDate]  
      INTO dbo.Product  
      FROM [AdventureWorks2008].[Production].[Product]  
    GO  
     
    INSERT dbo.Product  
    SELECT * FROM [AdventureWorks2008].[Production].[Product]  
    GO  
     
    SELECT COUNT(*) FROM dbo.Product  
    GO  
    -- 504  
    INSERT dbo.Product  
    SELECT * FROM [AdventureWorks2008].[Production].[Product]  
    GO  
     
    SELECT COUNT(*) FROM dbo.Product  
    GO  
    -- 1008  
     
    DROP TABLE tempdb.dbo.Product  
    GO 


    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    Wednesday, February 04, 2009 1:06 PM