If Column Exists in Source But Not Destination Then Create New Column

Transact-SQL
1

HI All

Wanted to check if it was possible for a destination table to have columns created based on what the source table has.

For example

Destination Table Structure:

Column1, Column2, Column3

Source Table Structure:

Column1, Column2, Column3, Column4, Column5

Amended Destination Table Structure:

Column1, Column2, Column3, Column4, Column5

At present we have a number of stored procedures that will be run as a source, each of which have different column heading, these in theory should already be in the destination table that the data from the stored procedure will be placed into. As a failsafe we want to ensure that if there is a column which is in the source but not in the destination, then the column is added to the destination table.

The process is likely to be run in SSIS.

Does anyone know it this is possible to do?

Thanks in advance.

2

You may have to loop through the system tables or information_schema to check for column match between source and destination and then build dynamic alter statements.

3 
SELECT	[Column missing] = C1.name
FROM	sys.tables AS T1
	OUTER JOIN sys.columns AS C1
		ON T1.object_id = C1.object_id
WHERE	T1.name = 'MySourceTableName'
	AND C1.name IN ('Column1', 'Column2', '...')
	AND NOT EXISTS
	(
		SELECT	*
		FROM	sys.tables AS T2
			JOIN sys.columns AS C2
				ON T2.object_id = C2.object_id
		WHERE	T2.name = 'MyDestinationTableName'
			AND C2.name = C1.name
	)

You can prefix the sys.XXX in the second select with "DestinationDatabaseName." if the destination table is in a different database.

Watch out for the required permissions to access the SYStem tables ...

4

Thanks All for the help, will give these a try.

5

I am not sure this will work unless you are dynamically building the destination and source objects in the SSIS data flow. The objects are bound to the previous definition and if that changes they will fail until their metadata is updated.

So even if you modify the table(s) appropriately you will still have to modify the SSIS package to update its metadata to reflect the new columns.

6

Off topic, and I don't use SSIS so my comments might be misplaced, but MS have done this "have to refresh the MetaData" in everything I have ever known, right from when I first remember it in some sort of C, or maybe C++, application generator / "studio" type APP development environment (probably back in the 80's ...)

I've never understood why, and it seems to me to be a huge negative point to using it.

We have MetaData in our APP (HUGE amounts of it in fact, our stuff completely revovles around the MetaData). FOr us MetaData is king - we define a "Label" for a Table + Column, for example. On every form, report column heading, etc. that we use that column on the Label is displayed, unless we explicitly override it (in that instance).

If I change the Label (or the Column Datatype / Width /or anything else), in the MetaData, it will change in all places where it is used. I don't have to rebuild anything. In practice that means that things do need to be rebuilt, but the "engine" detects that and rebuilds them "just in time" the next time they are used (or if I use a "Deploy" widget, which gets everything ready for release to QA or whatever).

It seems very StoneAge to me if SSIS doesn't adjust to the MetaData ... rather than require that the Package is rebuilt (or some operation like that).

If I've got the right end of the stick? perhaps there is some good reason why a Manual Rebuild, rather than an Automatic Rebuild, is required?