Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2007
    Location
    India
    Posts
    56

    Unanswered: bcp out/into tables

    Hi,
    I am naveen.i have a problem while i had bcp out the data previously.But i changed the table format means added columns into the table.i want to bcp in the old data which does not exist the additional columns. I used like this way:
    1)i created another table of old format.
    2)bcp in the data.
    3) create view on that table along with added columns.
    4)bcp out the data using the view.
    5)bcpin the data into new format table.

    but i have to create another 2 objects in that table.


    thanks
    VVS Naveen

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I don't understand "but i have to create another 2 objects in that table"
    Maybe some sample data and table ddl might help

  3. #3
    Join Date
    Feb 2007
    Location
    India
    Posts
    56
    hi,
    I created a table called student(sid,class,grade).I used to take bcp-out data every day.Now I add a column called 'status' in the middle of table i.e (sid,class,status,grade).So now i want to bcpin the data. For this i created a table with student_old(sid,class,grade) columns. bcp in the data into this table.create view stdetails_view as select sid,class,status=1,grade from student.bcp out the data using view.bcpin data into student.


    Thanks
    naveen.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Alternatives:
    1)
    After bcp in to student_old
    insert into student select sid,class,1,grade from student_old
    drop table student_old
    2)
    set a default value for status
    alter table student replace status default 1
    bcp in to new table with format file
    and specify grade as column 4 e.g.
    Code:
    10.0
    3
    1       SYBCHAR 0       12      ""      1       sid
    2       SYBCHAR 0       12      ""      2       class
    3       SYBCHAR 0       12      "\n"    4       grade
    Then you can remove the default again
    alter table student replace status default null

  5. #5
    Join Date
    Feb 2007
    Location
    India
    Posts
    56

    Hi

    I tried with the second alternative but i did not succeded.It is giving cs errors


    Thanks
    VVS Naveen Pabolu

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Well you haven't provided the table ddl
    And you gave no detail about the format of the data
    Nor do you show what your format file looks like
    Or the exact error message

    So let's make a few guesses
    Code:
    $ isql -Dtempdb -Usa -P -Smysrv
    1> create table student(sid int,class int,grade int)
    2> go
    1> insert into student select
    2> 1,1,5 union all select
    3> 2,1,7 union all select
    4> 3,2,4
    5> go
    (3 rows affected)
    $ bcp tempdb..student out t1.txt -Usa -P -Smysrv -c
    Starting copy...
    3 rows copied.
    Clock Time (ms.): total = 2  Avg = 0 (1500.00 rows per sec.)
    $ isql -Dtempdb -Usa -P -Smysrv
    1> drop table student
    2> go
    1> create table student(sid int,class int,status int,grade int)
    2> go
    1> alter table student replace status default 1
    2> go
    $ cat t1.fmt
    10.0
    3
    1       SYBCHAR 0       8       "\t"    1       col1
    2       SYBCHAR 0       8       "\t"    2       c2
    3       SYBCHAR 0       8       "\n"    4       xcol3
    $ bcp tempdb..student in t1.txt -f t1.fmt -Usa -P -Smysrv
    Starting copy...
    3 rows copied.
    Clock Time (ms.): total = 2  Avg = 0 (1500.00 rows per sec.)

  7. #7
    Join Date
    Feb 2007
    Location
    India
    Posts
    56

    Hi pdreyer

    Ok Thanks alot my problem has been solved with the above solution.


    Thanks alot.

Posting Permissions

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