Adding missing column to another table

• haiao2000

  Hall of Fame

  Points: 3734

  More actions

  November 19, 2012 at 1:05 pm

  #284979

  The problem:

  Initially, SourceTable and TargetTable both have same structure with columns (A,B,C). from time to timemy code does the following:

  insert into TargetTable (A,B,C)

  select A,B,C from SourceTable where criterion=true

  ***where A,B, C be a list of column named generated dynamically based on SourceTable

  However the issue is another application may sometimes add new columns (D,E,F) to SourceTable, so the above select statement no longer work.

  I can dynamically get a list of columns from SourceTable, then loop thru the list, check to see which one does not exist in the TargetTable then add it before doing the insertion, but that seems to be a nasty solution. below is a sample DDL.

  if object_id('tempdb..#SourceTable') is not null drop table #SourceTable;

  if object_id('tempdb..#TargetTable') is not null drop table #TargetTable;

  create table #SourceTable(state varchar(50),county varchar(50), city varchar(50),Action varchar(50), ActDateTime DateTime)

  create table #TargetTable(state varchar(50),county varchar(50), city varchar(50),Action varchar(50), ActDateTime DateTime)

  insert into #TargetTable (State,County,City,Action, ActDateTime)

  select 'Texas','Harris','Houston','Census Taken','11/12/2010 00:00:00' Union all

  select 'Texas','Harris','Houston','Census Submitted','12/15/2010 00:00:00'

  insert into #SourceTable (State,County,City,Action, ActDateTime)

  select State,County,City,Action, ActDateTime from #TargetTable;

  --testing only.....

  alter table #TargetTable add newColum1 varchar(50), newColumn2 bit;

  --here is the problem, again this list of fields being retrieved dynamically based on TargetTable and not known ahead up time, so we can't simply use the Alter statement

  insert into #SourceTable (State,County,City,Action, ActDateTime)

  select State,County,City,Action, ActDateTime from #TargetTable,newColum1,newColumn2;

  Any suggestion would be greatly appreciated.

• haiao2000

  Hall of Fame

  Points: 3734

  More actions

  November 19, 2012 at 1:21 pm

  #1560501

  I just thought of a solution after posting:

  --dump existing data from target table into a temp table

  select * INTO #TempTable from TargetTable;

  --drop the TargetTable

  drop targetTable;

  --recreate TargetTable with additional fields from SourceTable

  select * INTO TargetTable from SourceTable

  --move the data from temp table back to TargetTable

  insert into TargetTable(A,B,C)

  select A,B,C FROM #TempTable;

  Job well done????

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  November 19, 2012 at 2:40 pm

  #1560524

  haiao2000 (11/19/2012)

  ---

  I just thought of a solution after posting:

  --dump existing data from target table into a temp table

  select * INTO #TempTable from TargetTable;

  --drop the TargetTable

  drop targetTable;

  --recreate TargetTable with additional fields from SourceTable

  select * INTO TargetTable from SourceTable

  --move the data from temp table back to TargetTable

  insert into TargetTable(A,B,C)

  select A,B,C FROM #TempTable;

  Job well done????

  Hi,

  Select * INTO TargetTable from ... is not a good practice.

  Use queries like last one:

  insert into TargetTable(A,B,C)

  select A,B,C FROM #TempTable;

  Regards

  IgorMi

  Igor Micev,My blog: www.igormicev.com

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  November 19, 2012 at 3:02 pm

  #1560530

  IgorMi (11/19/2012)

  ---

  Select * INTO TargetTable from ... is not a good practice.

  Use queries like last one:

  insert into TargetTable(A,B,C)

  select A,B,C FROM #TempTable;

  Where did you hear this? There is nothing wrong with using select into.

  I would caution the OP that if there is any amount of data this will potentially be very slow because it has to drop all the data and the insert it again.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  November 19, 2012 at 3:09 pm

  #1560536

  Sean Lange (11/19/2012)

  ---

  IgorMi (11/19/2012)

  ---

  Select * INTO TargetTable from ... is not a good practice.

  Use queries like last one:

  insert into TargetTable(A,B,C)

  select A,B,C FROM #TempTable;

  Where did you hear this? There is nothing wrong with using select into.

  I would caution the OP that if there is any amount of data this will potentially be very slow because it has to drop all the data and the insert it again.

  I didn't hear it from anyone. Specifying the columns taking part in the insert is a clear work.

  It is my opinion anyway, I don't say it is wrong.

  Select * into ... is not a guaranty that will always work. What if the table structure get changed?

  That was my point.

  Thanks

  IgorMi

  Igor Micev,My blog: www.igormicev.com

• haiao2000

  Hall of Fame

  Points: 3734

  More actions

  November 19, 2012 at 3:13 pm

  #1560538

  Alright guy,my bad! I didnt intend to use "select * into" anyway, I was lazy typing so just threw out a quick post that caused someone hair stands...

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  November 19, 2012 at 3:26 pm

  #1560544

  IgorMi (11/19/2012)

  ---

  Sean Lange (11/19/2012)

  ---

  IgorMi (11/19/2012)

  ---

  Select * INTO TargetTable from ... is not a good practice.

  Use queries like last one:

  insert into TargetTable(A,B,C)

  select A,B,C FROM #TempTable;

  Where did you hear this? There is nothing wrong with using select into.

  I would caution the OP that if there is any amount of data this will potentially be very slow because it has to drop all the data and the insert it again.

  I didn't hear it from anyone. Specifying the columns taking part in the insert is a clear work.

  It is my opinion anyway, I don't say it is wrong.

  Select * into ... is not a guaranty that will always work. What if the table structure get changed?

  That was my point.

  Thanks

  IgorMi

  Of course select * into will always work because select into requires that the destination table does not exist so there is no table for a structure to be changed. 😉

  For the OP solution this seems one of those times it would actually be ok to use select *. The very challenge is due to changes in the source table and he wants to add those columns to the destination table. I normally would cringe to see select *. It is hard to say without more details but I suspect there is a better way to do what the OP is doing than dropping the table and recreating it every time it needs to gets updated.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• haiao2000

  Hall of Fame

  Points: 3734

  More actions

  November 19, 2012 at 3:31 pm

  #1560547

  For the OP solution this seems one of those times it would actually be ok to use select *. The very challenge is due to changes in the source table and he wants to add those columns to the destination table. I normally would cringe to see select *. It is hard to say without more details but I suspect there is a better way to do what the OP is doing than dropping the table and recreating it every time it needs to gets updated.

  I always open for better solution, so if you have a better suggestion, please do post it.

  Thanks!

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  November 19, 2012 at 3:39 pm

  #1560554

  haiao2000 (11/19/2012)

  ---

  For the OP solution this seems one of those times it would actually be ok to use select *. The very challenge is due to changes in the source table and he wants to add those columns to the destination table. I normally would cringe to see select *. It is hard to say without more details but I suspect there is a better way to do what the OP is doing than dropping the table and recreating it every time it needs to gets updated.

  I always open for better solution, so if you have a better suggestion, please do post it.

  Thanks!

  Well like I said, without more details it is hard to know for sure. One potential issue here is, does your source table ALWAYS contain ALL of data? It seems a bit odd to have a table that is nothing but a copy of another one. The approach you are proposing will drop the existing table. In the end you have nothing more than another copy of the source table. If you can explain in more detail what you are trying to do we can come up with ideas.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/