www.webdeveloper.com
Results 1 to 5 of 5

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

  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,
    |||||
    o . Q
    ___ "You live and you learn; or you don't live long." - Lazarus Long

    ^_^

  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
    |||||
    o . Q
    ___ "You live and you learn; or you don't live long." - Lazarus Long

    ^_^

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

    thepointless.com | rounded corner generator

    I agree with Apple. Flash is just terrible.

    Use CODE tags!

  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.
    |||||
    o . Q
    ___ "You live and you learn; or you don't live long." - Lazarus Long

    ^_^

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