Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    6

    Unanswered: Copy Selected column data from table to another during Upgrade of App

    Hi,
    I need to write a script that will be called during the database upgrade of my application. This is part of reorg of the tables. The script has to get data for say 4 columns from table A and insert it into another table B. Table B has identity insert column and remaining 4 columns matching the ones to be copied. The data is dependent on user database, hence number of records needs to be copied might be different. Also the columns can have null values.

    I tried using bcp Command as follows..
    bcp "select colA,colB,colC,colD from A" queryout "c:\temp\A.dat" -t"\t" -r"\n" -c

    I'm able to get the dat file, but not the format file. Can anyone tell me how to get it using query file with -c option. Also if there is better option to copy data, kindly let me know.

    This is very critical. Appreciate your help.

    Thanks,
    Ramya.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Why not copy the data into a local temporary (or permanent) table?

    I presume (perhaps incorrectly) that you want to retain this data to be inserted back into the modified table (or another table) later during the upgrade process.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Sep 2004
    Posts
    6
    That's correct. I want to retain the data in Table A maybe delete a column after data copy. And i also want table B to have the values. Can you suggest any way to accomplish this..

    Thanks,
    Ramya.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Code:
    Create TableTemp (
      ID int IDENTITY(1,1),
      ColumnA varchar(10),
      ColumnB varchar(10),
      ColumnC varchar(10),
      ColumnD varchar(10)
    )
    GO
    
    INSERT INTO TableTemp (ColumnA, ColumnB, ColumnC, ColumnD)
    SELECT ColumnA, ColumnB, ColumnC, ColumnD
    FROM
      MySourceTable
    GO
    
    ALTER TABLE MySourceTable DROP COLUMN ColumnA
    GO
    This will leave a permanent copy of the data from MySourceTable in TempTable.

    Regards,

    hmscott

    Quote Originally Posted by Ramyasaro
    That's correct. I want to retain the data in Table A maybe delete a column after data copy. And i also want table B to have the values. Can you suggest any way to accomplish this..

    Thanks,
    Ramya.
    Have you hugged your backup today?

Posting Permissions

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