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

[WolfShade]

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,

[WolfShade]

Found a resolution:

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

[svidgen]

Gratz. Thanks for posting your solution.

[qrizly]

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:

INSERT INTO <table_a> (col1, col2, col3) select col1, col2, col3 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

[WolfShade]

This might work if you know all the column names. It didn't work dynamically.