SQLTeam.com | Weblogs | Forums

Insert Into when field does not exist in table inserting into


#1

I have a unique situation, I have 2 tables, one is getting created dynamically and the other is the table I want to insert my data into. My problem is the dynamic table one time might only I have 10 fields and the next time it could have 20 fields. I want to be able to just add the record with blank or nulls in the fields where I do not have in my dynamic table. Can this be done automatically?
INSERT INTO TableA
SELECT *
FROM TableB

My thoughts were I could just to a SELECT *, but then I get the error about missing fields.


#2

Before I post the query below, I want to say that I don't like what I am going to post. There has to be some way in which your "dynamic" table can be made more deterministic. But, I don't know that, I am only guessing.

I don't like the query below for a few reasons: It uses dynamic sql - which is not recommended in most cases, it assumes the columns that you don't have in the source table are nullable, that you always have the ordinal positions the same way in the source and destination tables, that you have more columns in the destination table than in the source table etc.

With all those caveats,

CREATE TABLE SrcTable(a INT, b INT);
CREATE TABLE DestTable(a INT, b INT, c INT, d INT);
GO

INSERT INTO SrcTable VALUES (1,2),(3,4);

GO

DECLARE @n INT;
SELECT @n = 
	SUM(CASE WHEN OBJECT_NAME(OBJECT_ID) = 'DestTable' THEN 1 ELSE 0 END)
	- 	SUM(CASE WHEN OBJECT_NAME(OBJECT_ID) = 'SrcTable' THEN 1 ELSE 0 END)
FROM
	sys.columns;

DECLARE @sql NVARCHAR(4000) = 
'insert into DestTable '
+ 'select * ' + REPLICATE(', NULL ',@n)
+ 'from SrcTable';

EXEC sp_executesql @sql;