Insert Statement

I have an insert statement with 2 tables, such as:
Insert tForm Select * From tForm2
I know that tForm has all the fields in tForm2, but tForm2 may have some
additional fields not in tForm. The insert statement will not work because
the same # of columns do not exist in both tables. Is there a way to do
this without going through the task of building dynamic sql to determine
which fields do exist in the tables?
Derek HartWhy does it have to by dynamic SQL? Are these tables really changing that
often?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Derek Hart" <derekmhart@.yahoo.com> wrote in message
news:eixLbP9bGHA.4900@.TK2MSFTNGP02.phx.gbl...
>I have an insert statement with 2 tables, such as:
> Insert tForm Select * From tForm2
> I know that tForm has all the fields in tForm2, but tForm2 may have some
> additional fields not in tForm. The insert statement will not work
> because the same # of columns do not exist in both tables. Is there a way
> to do this without going through the task of building dynamic sql to
> determine which fields do exist in the tables?
> Derek Hart
>|||I have 150 tables in a development database. I send new versions out into
production; there could be 200 versions out there. The development database
will never remove tables or fields, but the production versions may not have
all the fields in a given table. I have been working with DTS to move data
between systems, but if the tables do not have the exact same fields, I
cannot use a statement such as:
Insert Database1.tForm Select * From Database2.tForm
I could do this in DTS or in a stored procedure, but I cannot determine
solutions for either of them. In DTS, the transformations must be built
dynamically because they do not simply auto-map on their own. Any thoughts
on this?
Derek Hart
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uAPmtR9bGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Why does it have to by dynamic SQL? Are these tables really changing that
> often?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Derek Hart" <derekmhart@.yahoo.com> wrote in message
> news:eixLbP9bGHA.4900@.TK2MSFTNGP02.phx.gbl...
>|||You might have to look into versioning these databases, instead of trying to
do this in an ad hoc manner. Are you sure that an insert without all of the
columns, for any given table, will actually make sense?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Derek Hart" <derekmhart@.yahoo.com> wrote in message
news:OfIaTc9bGHA.4040@.TK2MSFTNGP02.phx.gbl...
>I have 150 tables in a development database. I send new versions out into
>production; there could be 200 versions out there. The development
>database will never remove tables or fields, but the production versions
>may not have all the fields in a given table. I have been working with DTS
>to move data between systems, but if the tables do not have the exact same
>fields, I cannot use a statement such as:
> Insert Database1.tForm Select * From Database2.tForm
> I could do this in DTS or in a stored procedure, but I cannot determine
> solutions for either of them. In DTS, the transformations must be built
> dynamically because they do not simply auto-map on their own. Any
> thoughts on this?
> Derek Hart
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uAPmtR9bGHA.3364@.TK2MSFTNGP05.phx.gbl...
>|||Why not just refresh you development environment once in the while, and
create a test environment also, which would be refreshed more often?
"Derek Hart" <derekmhart@.yahoo.com> wrote in message
news:OfIaTc9bGHA.4040@.TK2MSFTNGP02.phx.gbl...
> I have 150 tables in a development database. I send new versions out into
> production; there could be 200 versions out there. The development
database
> will never remove tables or fields, but the production versions may not
have
> all the fields in a given table. I have been working with DTS to move
data
> between systems, but if the tables do not have the exact same fields, I
> cannot use a statement such as:
> Insert Database1.tForm Select * From Database2.tForm
> I could do this in DTS or in a stored procedure, but I cannot determine
> solutions for either of them. In DTS, the transformations must be built
> dynamically because they do not simply auto-map on their own. Any
thoughts
> on this?
> Derek Hart
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uAPmtR9bGHA.3364@.TK2MSFTNGP05.phx.gbl...
that
some
to
>|||I have the following code which will give me all the tables, but I will need
to dynamically generate the select list and explicitly name the fields -
here is the code I received in another post. I would be grateful if somebody
could give me the information of how I could create the list of fields in
the source table (OldDB) to dynamically insert into the destination table
(NewDB) - I know that there may be fields in the NewDB that are not in the
OldDB, but not vice versa. And I know defaults will handle any fields that
do not make it into the select list. How can this be done? By the way I
have to have this scripted onto the production machines that I do not have
direct access to.
declare @.sql varchar(8000)
declare @.table_name varchar(256)
SELECT name FROM sysobjects where xtype = 'u' and name <>
'dtproperties'
DECLARE table_list CURSOR FOR
SELECT name FROM sysobjects where xtype = 'u' and name <>
'dtproperties'
OPEN table_list
FETCH NEXT FROM table_list INTO
@.table_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = 'Insert into NewDB..' + @.table_name + ' ' + '(Select * From
OldDatabase..' + @.table_name + ' ) '
--print @.sql
--EXEC (@.SQL)
FETCH NEXT FROM table_list INTO
@.table_name
END
DEALLOCATE table_list
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:enL6%23DFcGHA.4896@.TK2MSFTNGP03.phx.gbl...
> Why not just refresh you development environment once in the while, and
> create a test environment also, which would be refreshed more often?
> "Derek Hart" <derekmhart@.yahoo.com> wrote in message
> news:OfIaTc9bGHA.4040@.TK2MSFTNGP02.phx.gbl...
> database
> have
> data
> thoughts
> that
> some
> to
>