www.webdeveloper.com
Results 1 to 5 of 5

Thread: [RESOLVED] Copy data tableA > tableB - column differences prevent

Hybrid View

  1. #1
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582

    resolved [RESOLVED] Copy data tableA > tableB - column differences prevent

    I have two tables that are ALMOST identical; one is primary data, the other is archive.

    The difference between the two:
    1- Primary has PROJ_ID that is an identity; Archive has PROJ_ID that is not.
    2- Archive has one extra column (archive_date) that Primary does not.

    I'd like to copy data from one table to the other. Unfortunately, I can't use SELECT INTO, because the table already exists; and I can't use INSERT INTO tablename SELECT * because of the one column difference.

    I've been trying for an hour. Is there a way to copy the data? I've tried declaring a variable, selecting the column names of Primary into it, and using that as the column names for the insert, but I keep getting error messages.

    Any suggestions?

    Thanks,

  2. #2
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Found a resolution:
    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[Archive_Data]
    	@id int, @table varchar(20)
    
    AS
    BEGIN
    DECLARE @fromTable nvarchar(50), @toTable nvarchar(50), @idColumn nvarchar(20), @xSQL nvarchar(4000), @columns nvarchar(4000)
    SET @fromTable = '[APP_' + @table + ']';
    SET @toTable = 'APP_' + @table + '_ARCHIVE]';
    SET @idColumn = CASE WHEN @table = 'EXPERIMENT' THEN 'EXPERIMENT_ID'
    				ELSE 'FINALIZED_ID' END
    SET @xSQL = '';
    
    SELECT @columns = COALESCE(@columns + ',','') + name
    	FROM sys.columns 
    	WHERE [object_id] = OBJECT_ID('' + @fromTable + '')
     
    SET @xSQL = @xSQL + 'INSERT INTO ' + @toTable + ' (' + @columns + ') ';
    SET @xSQL = @xSQL + 'SELECT * FROM ' + @fromTable + ' WHERE ' + @idColumn + ' = ' + CAST(@id as nvarchar(20)) + ' ';
    SET @xSQL = @xSQL + 'DELETE FROM ' + @fromTable + ' WHERE ' + @idColumn + ' = ' + CAST(@id as nvarchar(20)) + ' ';
    
    EXEC sp_executesql @xSQL
    
    END

  3. #3
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    Gratz. Thanks for posting your solution.

  4. #4
    Join Date
    May 2011
    Posts
    25
    I'd like to copy data from one table to the other. Unfortunately, I can't use SELECT INTO, because the table already exists; and I can't use INSERT INTO tablename SELECT * because of the one column difference.
    Thats a little bit incorrect, so just for future ref:

    PHP Code:
    INSERT INTO <table_a> (col1col2col3select col1col2col3 FROM <table_b
    This query will skip all other columns present in table_a or table_b and will fill table_a with the default values if the colum is not specified

  5. #5
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    This might work if you know all the column names. It didn't work dynamically.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles