Does field exist in backup?

I run scripts against my db,

1) Rename table
2) Create new table (original name)
3) Create Indexes
4) Insert into new, Select from backup

My problem is that one table may have more fields than on another db, but I want to run the same script to update the tables.

What I'd like it to do, is in the insert select stuff, I want to put logic to select field from backup if it exists and insert in new.

If it doesnt exist in backup then insert null into new table, instead of having the line blow up cause the field doesnt exist in backup.

Suggestions would be appreciated. Thanks!, MitchIf it's not in the list it will automatically put in nulls...as long as it is nullable...

and you could go crazy...but it might just easier to code the dang thing...

USE Northwind
GO
sp_help Orders
GO

-- The lazy man's way to create a table

SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO

ALTER TABLE NewOrders DROP Column RequiredDate
GO

DECLARE @.x varchar(8000)

SELECT @.x = 'INSERT INTO NewOrders ('

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ') SELECT '

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ' FROM Orders'

SELECT @.x

SET IDENTITY_INSERT NewOrders ON

EXEC(@.x)

SET IDENTITY_INSERT NewOrders OFF
GO

SELECT * FROM NewOrders
GO

DROP TABLE NewOrders
GO|||If you could send me a link or something, that would help me understand the logic below that would be awesome. I sort of follow the code below, but I'd like to see step by step what does what.
Thanks for your reply.
Mitch

Originally posted by Brett Kaiser
If it's not in the list it will automatically put in nulls...as long as it is nullable...

and you could go crazy...but it might just easier to code the dang thing...

USE Northwind
GO
sp_help Orders
GO

-- The lazy man's way to create a table

SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO

ALTER TABLE NewOrders DROP Column RequiredDate
GO

DECLARE @.x varchar(8000)

SELECT @.x = 'INSERT INTO NewOrders ('

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ') SELECT '

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ' FROM Orders'

SELECT @.x

SET IDENTITY_INSERT NewOrders ON

EXEC(@.x)

SET IDENTITY_INSERT NewOrders OFF
GO

SELECT * FROM NewOrders
GO

DROP TABLE NewOrders
GO|||Mitch,

Just cut and paste the code into a query analyzer window...

Just execute...I already tested it and it runs like a champ...