none
Portable insert RRS feed

  • Question

  • Hi

    I have two tables which get fields added over time as they are accessed by several apps for various reasons. Is there a portable way to insert rows from one to the other in a way that fields with matching names get data inserted and rest of fields in either table are ignored? This is to avoid changing code in my app every time fields are added to any of the tables by another user.

    Many Thanks

    Regards

    Saturday, April 23, 2011 1:04 PM

Answers

  • Yahya ,

    As far as i know there is no straight forward thing for it unless you go for some third party Solutions like AlfaAlfa,SQL Delta and SQL Server Comparison Expert for DB schema comparisons.
    i have used SQL Delta and it has good support for it.

    whereas here is a SQL script (in SQL Server 2008)  that will compare one table with another and if found same matching column as you said ignoring the mismatches, it will return you a query that you can execute to Synchronise data. i had two tables 'tblPrice' and 'tblPriceNew' with some similar column names.

     

    Declare @MachingColumnList varchar(1000)
    declare @result varchar(100)
    DECLARE @COLUMN_NAME nvarchar(250), @DATA_TYPE nvarchar(250)
    
      set @MachingColumnList = ''
    DECLARE country_cursor CURSOR FOR
    select COLUMN_NAME , DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = 'tblPrice' order by ORDINAL_POSITION
    
    OPEN country_cursor;
    
    FETCH NEXT FROM country_cursor
    INTO @COLUMN_NAME , @DATA_TYPE-- , @CountName
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
       select @result=COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'tblPriceNew' and COLUMN_NAME = @COLUMN_NAME
    
         if LEN(@result)>0
         begin
         set @MachingColumnList = @MachingColumnList + ',' + @result
         --select @result
         end
      FETCH NEXT FROM country_cursor
      INTO @COLUMN_NAME , @DATA_TYPE
    
    END
    CLOSE country_cursor;
    DEALLOCATE country_cursor;
    
    
    
    set @MachingColumnList = substring(@MachingColumnList,2,LEN(@MachingColumnList))
    
    Declare @QueryString varchar(2000)
    set @QueryString = 'insert into tblPrice ( ' + @MachingColumnList + ' )
    select '+ @MachingColumnList + ' from tblPriceNew'
    
    select @QueryString
    
    
    

    if you want you can run the generated query in last with

    execute @QueryString

     

     


    Please mark the replies as answers if they help and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    • Marked as answer by Y a h y a Wednesday, April 27, 2011 2:58 PM
    Wednesday, April 27, 2011 2:53 PM

All replies

  • hi,

    you need a basic lesson in database design;

    see http://en.wikipedia.org/wiki/Database_normalization to start with.

    regards,

    nico

    Monday, April 25, 2011 12:13 AM
  • Yahya ,

    As far as i know there is no straight forward thing for it unless you go for some third party Solutions like AlfaAlfa,SQL Delta and SQL Server Comparison Expert for DB schema comparisons.
    i have used SQL Delta and it has good support for it.

    whereas here is a SQL script (in SQL Server 2008)  that will compare one table with another and if found same matching column as you said ignoring the mismatches, it will return you a query that you can execute to Synchronise data. i had two tables 'tblPrice' and 'tblPriceNew' with some similar column names.

     

    Declare @MachingColumnList varchar(1000)
    declare @result varchar(100)
    DECLARE @COLUMN_NAME nvarchar(250), @DATA_TYPE nvarchar(250)
    
      set @MachingColumnList = ''
    DECLARE country_cursor CURSOR FOR
    select COLUMN_NAME , DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = 'tblPrice' order by ORDINAL_POSITION
    
    OPEN country_cursor;
    
    FETCH NEXT FROM country_cursor
    INTO @COLUMN_NAME , @DATA_TYPE-- , @CountName
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
       select @result=COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'tblPriceNew' and COLUMN_NAME = @COLUMN_NAME
    
         if LEN(@result)>0
         begin
         set @MachingColumnList = @MachingColumnList + ',' + @result
         --select @result
         end
      FETCH NEXT FROM country_cursor
      INTO @COLUMN_NAME , @DATA_TYPE
    
    END
    CLOSE country_cursor;
    DEALLOCATE country_cursor;
    
    
    
    set @MachingColumnList = substring(@MachingColumnList,2,LEN(@MachingColumnList))
    
    Declare @QueryString varchar(2000)
    set @QueryString = 'insert into tblPrice ( ' + @MachingColumnList + ' )
    select '+ @MachingColumnList + ' from tblPriceNew'
    
    select @QueryString
    
    
    

    if you want you can run the generated query in last with

    execute @QueryString

     

     


    Please mark the replies as answers if they help and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    • Marked as answer by Y a h y a Wednesday, April 27, 2011 2:58 PM
    Wednesday, April 27, 2011 2:53 PM