Replicating an MSSQL table structure 2

[MattN]

Currently I have an import table which inserts data into temporary tables which I have manually created.

Whenever I add a field to the import table I need to manually add the field to the other tables. This is obviously time consuming and primitive!

Is there a Stored Procedure (or other way) I can automate this so that I can automatically create a copy of the import table. I am working with VB6.

Thanks

Matt N

 

[ClaireHCM]

If you want to add column to the existing table, there is no other choise but do it manually.Also there is no stored procedure which is going to check the changes in table structure.

But if you want to duplicate the table structure without copying the data.There is a sql command to do so.

select * into NewTableName from OriginalTable where 1 = 0

 

[MattN]

Hello Clare,

I have run this command;-

select * into NewPaths from Paths where 1 = 0


through SQL analyser and the following message has occurred.

Server: Msg 268, Level 16, State 3, Line 1
Cannot run SELECT INTO in this database. The database owner must run sp_dboption to enable this option.

Any suggestions.
Thanks

 

[ClaireHCM]

Your database's 'select into/bulkcopy' option is not enabled.

If you are the either sysadmin, dbcreator or dbowner of that database. you could run the following command to enable this option

sp_dboption 'claire','select into/bulkcopy','true'

 

[MattN]

Thanks Claire,

All is ok now



Regards

Matt N