Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    11

    Unanswered: 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!, Mitch

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Does field exist in backup?

    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...

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2003
    Posts
    11

    Re: Does field exist in backup?

    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...

    Code:
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Does field exist in backup?

    Mitch,

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

    Just execute...I already tested it and it runs like a champ...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •