vijadon
asked on
Using bcp in Unix fails
Hello,
I am trying to take out data from one table and put it into another table, the second table has a new column added with not null.
I use the following bcp command and when I try the"in " command it gives me an DB-LIBRARY error:
Unexpected EOF encountered in BCP data-file. error.
Here are comands I am using ,I would really appreciate youe quick help.
bcp oil_grain_ops.dbo.userlist
bcp oil_grain_ops.dbo.userlist
The bcp out says:
copied 43 rows,
The bcp in says:
copied 2 rows.
Thank you for your help in advance,
Vsj
ASKER
Thank you for your help, I can try to use:
Given this is in the same server, it's probably easiest just to run this as a SELECT INTO:
SELECT *. "some value for the new column"
INTO userlist_email
FROM userlist
What should I put for the new column ,it is a email column and also cannot be null at the same time.
I will try this ,
thanks
vsj
vijadon
why not use native format, remove the -c and -t option and add -n
bcp oil_grain_ops.dbo.userlist out ogo.dat -Ssybcps2 -Usa -Ptest01 -n
bcp oil_grain_ops.dbo.userlist _email in ogo.dat -Ssybcps2 -Usa -Ptest01 -n -b5000
then you don't need to worry about whather your data contains seperators
why not use native format, remove the -c and -t option and add -n
bcp oil_grain_ops.dbo.userlist
bcp oil_grain_ops.dbo.userlist
then you don't need to worry about whather your data contains seperators
ASKER
The above select does not work, I tried it.
Aslo made the column in the second table made it to allow nulls, then tried it got the same error.
My format for the out file looks ok, onlh the bcp in gives me an error :
Starting copy...
DB-LIBRARY error:
Unexpected EOF encountered in BCP data-file.
21 rows copied.
Clock Time (ms.): total = 1 Avg = 0 (21000.00 rows per sec.)
It copies 21 rows out of 43 rows.
thank you
vsj
Aslo made the column in the second table made it to allow nulls, then tried it got the same error.
My format for the out file looks ok, onlh the bcp in gives me an error :
Starting copy...
DB-LIBRARY error:
Unexpected EOF encountered in BCP data-file.
21 rows copied.
Clock Time (ms.): total = 1 Avg = 0 (21000.00 rows per sec.)
It copies 21 rows out of 43 rows.
thank you
vsj
ASKER
I also used what Chrisking replied I got the following error:
sybserv-cps4% bcp oil_grain_ops.dbo.userlist _email in ogo.dat -Ssybcps4 -Usa -PTstsa -n -b5000
Starting copy...
2 rows copied.
Clock Time (ms.): total = 1 Avg = 0 (2000.00 rows per sec.)
This time it copied only 2 rows.
thank you
vsj
sybserv-cps4% bcp oil_grain_ops.dbo.userlist
Starting copy...
2 rows copied.
Clock Time (ms.): total = 1 Avg = 0 (2000.00 rows per sec.)
This time it copied only 2 rows.
thank you
vsj
how many rows did the native "out" do ?
ie
bcp oil_grain_ops.dbo.userlist out ogo.dat -Ssybcps2 -Usa -Ptest01 -n
ie
bcp oil_grain_ops.dbo.userlist
What datatype is the new column in the second table? That will determine what sort of literal value would be appropriate with the SELECT INTO method. Or if you make the column nullable, a straight SELECT * INTO new FROM old would do the trick.
Native mode bcp, even if the column allows NULLs, will fail because the format of the two tables is different. Bcp quite reasonably says "you have a table with N columns which you're trying to bcp into a table with N+1 columns - what am I supposed to put where???"
There are really two issues here:
- the tables are different in terms of their structure
- the new column doesn't allow NULLs
Any solution must deal with both.
If you don't mind the new column allowing NULLs, then SELECT INTO is the simplest.
For bcp, either use a view to bcp out of (to supply a non-NULL value for the new column), or set the new column to allow NULLs and either use native mode plus a format file, or character mode and edit the bcp file to add an extra column terminator at the end of the line (to indicate a NULL value for that column).
Good luck!
Native mode bcp, even if the column allows NULLs, will fail because the format of the two tables is different. Bcp quite reasonably says "you have a table with N columns which you're trying to bcp into a table with N+1 columns - what am I supposed to put where???"
There are really two issues here:
- the tables are different in terms of their structure
- the new column doesn't allow NULLs
Any solution must deal with both.
If you don't mind the new column allowing NULLs, then SELECT INTO is the simplest.
For bcp, either use a view to bcp out of (to supply a non-NULL value for the new column), or set the new column to allow NULLs and either use native mode plus a format file, or character mode and edit the bcp file to add an extra column terminator at the end of the line (to indicate a NULL value for that column).
Good luck!
ASKER
Hi Chrisking,
bcp oil_grain_ops.dbo.userlist out ogo.dat -Ssybcps2 -Usa -Ptest01 -n
this brings backs 43 rows.
thanks
Vinai
bcp oil_grain_ops.dbo.userlist
this brings backs 43 rows.
thanks
Vinai
ASKER
This is what my table looks like userlist (original)
Name Owner Type
---- ----- ----
userlist dbo user table
Data_located_on_segment When_created
----------------------- ------------
default Jun 30 1997 6:44PM
Column_name Type Length Nulls Default_name Rule_name
----------- ---- ----------- ----------- ------------ ---------
ul_id char 8 0 NULL NULL
ul_password char 8 0 NULL NULL
ul_location char 2 0 NULL NULL
ul_name char 40 0 NULL NULL
ul_group_id char 8 0 NULL NULL
ul_authority char 8 0 NULL NULL
ul_oldest_deldate_yyyymmdd char 8 0 NULL NULL
ul_updateuserid char 8 0 NULL NULL
ul_updatedatetime datetime 8 0 NULL NULL
ul_handset char 8 1 NULL NULL
ul_phonenumber char 20 1 NULL NULL
ul_print_area char 30 1 NULL NULL
index_name index_description index_keys
---------- ----------------- ----------
ul_indx1 clustered, unique located on default ul_id
************************** ********** ********** ********** ********** ********
my new table:
structure looks like this:
Create table userlist_email
(ul_id char(8) Null ,
ul_password char(15) Null ,
ul_location char(2) Null,
ul_name char(40) Null,
ul_group_id char(8) Null,
ul_authority char(8) Null,
ul_oldest_deldate_yyyymmdd char(8) Null ,
ul_updateuserid char(8) Null ,
ul_updatedatetime datetime ,
ul_handset char(8) Null ,
ul_phonenumber char(20) Null ,
ul_print_area char(30) Null,
ul_email varchar(50) Not Null)
my data is here:
ul_id ul_password ul_location ul_name ul_group_id ul_authority ul_oldest_deldate_yyyymmdd ul_updateuserid ul_updatedatetime ul_handset ul_phonenumber ul_print_area
----- ----------- ----------- ------- ----------- ------------ -------------------------- --------------- ----------------- ---------- -------------- -------------
BECKEB BRIGIT BM BRIGIT M BECKER PROD MODIFY 99990101 JOHNSC May 3 2002 4:35PM BM POWER
C01926 laurenb BM Lauren Bittel PROD MODIFY 99990101 minyong Nov 14 2003 3:40PM BM POWER
DAPUZC COREN BM COREN DAPUZZO PROD MODIFY 99990101 HARBAK Apr 30 2001 11:24AM BM POWER
DERRIS SUSAN BM SUSAN D. DERRICK PROD MODIFY 99990101 MASTRD Jun 28 2001 11:11AM BM POWER
DISNEJ JACK BM JACK G DISNEY PROD ADMIN 99990101 JOHNSC Jun 22 2001 11:34AM BM POWER
E10208 ALLIEG BM Allie Gill PROD MODIFY 99990101 minyong Jul 14 2004 11:14AM BM POWER
E10302 TATYANAF BM Tatyana Finkelshteyn PROD MODIFY 99990101 minyong Jun 1 2004 11:02AM BM POWER
E10322 SETHU BM Seth Unterschute PROD MODIFY 99990101 XXXXXX Jul 22 2004 10:00AM BM POWER
E10333 NATHANS BM Suzanne(Suzy) M. Nathan PROD MODIFY 99990101 minyong Jun 4 2004 9:28AM BM POWER
E10384 TIMP BM Tim Phillips PROD MODIFY 99990101 minyong Jun 16 2004 2:31PM BM POWER
E10564 LAMARCAD BM David J. Lamarca PROD MODIFY 99990101 minyong Jul 19 2004 10:34AM BM POWER
E10589 GEOFFREP BM Geoffrey Eisenberg PROD MODIFY 99990101 XXXXXX Jul 27 2004 10:37AM BM POWER
E10661 SUZANNEG BM Suzanne Griffith PROD MODIFY 99990101 XXXXXX Aug 10 2004 10:31AM BM POWER
E10662 VICTORIB BM Victoria Byrd PROD MODIFY 99990101 XXXXXX Aug 11 2004 11:56AM BM POWER
E10743 BRIANNEM BM Brianne Marinucci PROD MODIFY 99990101 XXXXXX Sep 3 2004 10:27AM BM POWER
E28747 LAWRENCE BM LAWRENCE S IRELAND PROD MODIFY 99990101 JOHNSC Nov 19 2001 9:09AM BM POWER
E35428 DONALD BM DONALD S SCHAFFER PROD MODIFY 99990101 JOHNSC Jan 31 2003 3:41PM BM POWER
E39856 MICHAEL BM MICHAEL W TYREE PROD MODIFY 99990101 JOHNSC Nov 19 2001 9:11AM BM POWER
E43831 GEORGE BM GEORGE H WELTZ PROD MODIFY 99990101 JOHNSC May 9 2003 1:56PM BM POWER
E46328 SCELSIL BM Laurie Scelsi PROD MODIFY 99990101 minyong Jul 1 2004 12:24PM BM POWER
E46330 SOMMERN BM Sommer Nickles PROD MODIFY 99990101 XXXXXX Sep 3 2004 10:25AM BM POWER
E46980 NANCY BM NANCY Z KALMS PROD MODIFY 99990101 JOHNSC Jul 26 2002 12:07PM BM POWER
E49665 ANNA BM ANNA M MEHRER PROD MODIFY 99990101 JOHNSC Jan 6 2003 1:32PM BM POWER
E49671 SHERIDA BM SHERIDA M VANBOCHOVE-ARIAS PROD MODIFY 99990101 JOHNSC Jan 24 2003 3:26PM BM POWER
E63115 CHRISTIN BM TINA HAYS PROD MODIFY 99990101 BRAUNS May 27 2003 1:08PM BM POWER
E63125 COLLEEN BM COLLEEN M KENTY PROD MODIFY 99990101 JOHNSC Jun 20 2003 12:11PM BM POWER
E63567 JEROME BM JEROME WESS PROD MODIFY 99990101 L00365 Oct 16 2003 10:37AM BM POWER
E63583 tamaram BM Tamara Molder PROD MODIFY 99990101 minyong Nov 12 2003 2:12PM BM POWER
E63591 waire BM John Waire PROD ADMIN 99990101 minyong Dec 9 2003 5:27PM BM POWER
EGWIMJ JANET BM JANET N. EGWIM PROD MODIFY 99990101 VALEND Jun 3 2002 11:16AM BM POWER
FISHEK KIMBERLE BM KIMBERLEY A FISHER PROD MODIFY 99990101 JOHNSC Apr 26 2002 1:44PM BM POWER
FITZPR RYAN BM RYAN FITZ-PATRICK PROD MODIFY 99990101 KIRSCMP Jul 11 1997 10:01AM BM POWER
HELLYS SHELLEY BM SHELLEY HELLYER PROD ADMIN 99990101 JOHNSC Apr 5 2002 5:03PM BM POWER
HOLLAK KAREN BM KAREN HOLLAND PROD MODIFY 99990101 REVANC May 25 2000 8:08AM BM POWER
HUH howardh BM Howard Hu PROD MODIFY 99990101 minyong Jan 15 2004 2:18PM BM POWER
KAHND DANIEL BM DANIEL R KAHN PROD MODIFY 99990101 JOHNSC Oct 29 2002 9:56AM BM POWER
LEWISD DAMIAN BM DAMIAN LEWIS PROD MODIFY 99990101 XXXXXX Aug 6 2002 12:09PM BM POWER
LYONSC CHRIS BM CHRIS LYONS PROD MODIFY 99990101 minyong Dec 9 2003 3:09PM BM POWER
MARLAT TOM BM TOM MARLATT PROD ADMIN 99990101 KIRSCMP Dec 29 1997 10:35AM BM POWER
MENZIL LINDA BM LINDA MENZIES PROD MODIFY 99990101 WANGJ Sep 3 1997 10:04AM BM POWER
PETTET PASS2WRD BM THERESA C. PETTEWAY PROD MODIFY 99990101 AULDB Oct 26 2004 5:37PM BM POWER
RUBENS 1234222c BM STU RUBENSTEIN PROD MODIFY 99990101 KIRSCMP Jun 30 1997 6:48PM BM POWER
WEBERH HOLLY BM HOLLY WEBER PROD MODIFY 99990101 MAHONP May 5 1999 8:46AM BM POWER
I need to put all the data from userlist table to userlist_email table.
MY ORIGINAL WORK STARTED AS TO ADD A NEW COLUMN INTO THE USERLIST TABLE CALLED UL_EMAIL VARCHAR(50) NOT NULL.
PLEASE HELP!
THANKS
VSJ
Name Owner Type
---- ----- ----
userlist dbo user table
Data_located_on_segment When_created
----------------------- ------------
default Jun 30 1997 6:44PM
Column_name Type Length Nulls Default_name Rule_name
----------- ---- ----------- ----------- ------------ ---------
ul_id char 8 0 NULL NULL
ul_password char 8 0 NULL NULL
ul_location char 2 0 NULL NULL
ul_name char 40 0 NULL NULL
ul_group_id char 8 0 NULL NULL
ul_authority char 8 0 NULL NULL
ul_oldest_deldate_yyyymmdd
ul_updateuserid char 8 0 NULL NULL
ul_updatedatetime datetime 8 0 NULL NULL
ul_handset char 8 1 NULL NULL
ul_phonenumber char 20 1 NULL NULL
ul_print_area char 30 1 NULL NULL
index_name index_description index_keys
---------- ----------------- ----------
ul_indx1 clustered, unique located on default ul_id
**************************
my new table:
structure looks like this:
Create table userlist_email
(ul_id char(8) Null ,
ul_password char(15) Null ,
ul_location char(2) Null,
ul_name char(40) Null,
ul_group_id char(8) Null,
ul_authority char(8) Null,
ul_oldest_deldate_yyyymmdd
ul_updateuserid char(8) Null ,
ul_updatedatetime datetime ,
ul_handset char(8) Null ,
ul_phonenumber char(20) Null ,
ul_print_area char(30) Null,
ul_email varchar(50) Not Null)
my data is here:
ul_id ul_password ul_location ul_name ul_group_id ul_authority ul_oldest_deldate_yyyymmdd
----- ----------- ----------- ------- ----------- ------------ --------------------------
BECKEB BRIGIT BM BRIGIT M BECKER PROD MODIFY 99990101 JOHNSC May 3 2002 4:35PM BM POWER
C01926 laurenb BM Lauren Bittel PROD MODIFY 99990101 minyong Nov 14 2003 3:40PM BM POWER
DAPUZC COREN BM COREN DAPUZZO PROD MODIFY 99990101 HARBAK Apr 30 2001 11:24AM BM POWER
DERRIS SUSAN BM SUSAN D. DERRICK PROD MODIFY 99990101 MASTRD Jun 28 2001 11:11AM BM POWER
DISNEJ JACK BM JACK G DISNEY PROD ADMIN 99990101 JOHNSC Jun 22 2001 11:34AM BM POWER
E10208 ALLIEG BM Allie Gill PROD MODIFY 99990101 minyong Jul 14 2004 11:14AM BM POWER
E10302 TATYANAF BM Tatyana Finkelshteyn PROD MODIFY 99990101 minyong Jun 1 2004 11:02AM BM POWER
E10322 SETHU BM Seth Unterschute PROD MODIFY 99990101 XXXXXX Jul 22 2004 10:00AM BM POWER
E10333 NATHANS BM Suzanne(Suzy) M. Nathan PROD MODIFY 99990101 minyong Jun 4 2004 9:28AM BM POWER
E10384 TIMP BM Tim Phillips PROD MODIFY 99990101 minyong Jun 16 2004 2:31PM BM POWER
E10564 LAMARCAD BM David J. Lamarca PROD MODIFY 99990101 minyong Jul 19 2004 10:34AM BM POWER
E10589 GEOFFREP BM Geoffrey Eisenberg PROD MODIFY 99990101 XXXXXX Jul 27 2004 10:37AM BM POWER
E10661 SUZANNEG BM Suzanne Griffith PROD MODIFY 99990101 XXXXXX Aug 10 2004 10:31AM BM POWER
E10662 VICTORIB BM Victoria Byrd PROD MODIFY 99990101 XXXXXX Aug 11 2004 11:56AM BM POWER
E10743 BRIANNEM BM Brianne Marinucci PROD MODIFY 99990101 XXXXXX Sep 3 2004 10:27AM BM POWER
E28747 LAWRENCE BM LAWRENCE S IRELAND PROD MODIFY 99990101 JOHNSC Nov 19 2001 9:09AM BM POWER
E35428 DONALD BM DONALD S SCHAFFER PROD MODIFY 99990101 JOHNSC Jan 31 2003 3:41PM BM POWER
E39856 MICHAEL BM MICHAEL W TYREE PROD MODIFY 99990101 JOHNSC Nov 19 2001 9:11AM BM POWER
E43831 GEORGE BM GEORGE H WELTZ PROD MODIFY 99990101 JOHNSC May 9 2003 1:56PM BM POWER
E46328 SCELSIL BM Laurie Scelsi PROD MODIFY 99990101 minyong Jul 1 2004 12:24PM BM POWER
E46330 SOMMERN BM Sommer Nickles PROD MODIFY 99990101 XXXXXX Sep 3 2004 10:25AM BM POWER
E46980 NANCY BM NANCY Z KALMS PROD MODIFY 99990101 JOHNSC Jul 26 2002 12:07PM BM POWER
E49665 ANNA BM ANNA M MEHRER PROD MODIFY 99990101 JOHNSC Jan 6 2003 1:32PM BM POWER
E49671 SHERIDA BM SHERIDA M VANBOCHOVE-ARIAS PROD MODIFY 99990101 JOHNSC Jan 24 2003 3:26PM BM POWER
E63115 CHRISTIN BM TINA HAYS PROD MODIFY 99990101 BRAUNS May 27 2003 1:08PM BM POWER
E63125 COLLEEN BM COLLEEN M KENTY PROD MODIFY 99990101 JOHNSC Jun 20 2003 12:11PM BM POWER
E63567 JEROME BM JEROME WESS PROD MODIFY 99990101 L00365 Oct 16 2003 10:37AM BM POWER
E63583 tamaram BM Tamara Molder PROD MODIFY 99990101 minyong Nov 12 2003 2:12PM BM POWER
E63591 waire BM John Waire PROD ADMIN 99990101 minyong Dec 9 2003 5:27PM BM POWER
EGWIMJ JANET BM JANET N. EGWIM PROD MODIFY 99990101 VALEND Jun 3 2002 11:16AM BM POWER
FISHEK KIMBERLE BM KIMBERLEY A FISHER PROD MODIFY 99990101 JOHNSC Apr 26 2002 1:44PM BM POWER
FITZPR RYAN BM RYAN FITZ-PATRICK PROD MODIFY 99990101 KIRSCMP Jul 11 1997 10:01AM BM POWER
HELLYS SHELLEY BM SHELLEY HELLYER PROD ADMIN 99990101 JOHNSC Apr 5 2002 5:03PM BM POWER
HOLLAK KAREN BM KAREN HOLLAND PROD MODIFY 99990101 REVANC May 25 2000 8:08AM BM POWER
HUH howardh BM Howard Hu PROD MODIFY 99990101 minyong Jan 15 2004 2:18PM BM POWER
KAHND DANIEL BM DANIEL R KAHN PROD MODIFY 99990101 JOHNSC Oct 29 2002 9:56AM BM POWER
LEWISD DAMIAN BM DAMIAN LEWIS PROD MODIFY 99990101 XXXXXX Aug 6 2002 12:09PM BM POWER
LYONSC CHRIS BM CHRIS LYONS PROD MODIFY 99990101 minyong Dec 9 2003 3:09PM BM POWER
MARLAT TOM BM TOM MARLATT PROD ADMIN 99990101 KIRSCMP Dec 29 1997 10:35AM BM POWER
MENZIL LINDA BM LINDA MENZIES PROD MODIFY 99990101 WANGJ Sep 3 1997 10:04AM BM POWER
PETTET PASS2WRD BM THERESA C. PETTEWAY PROD MODIFY 99990101 AULDB Oct 26 2004 5:37PM BM POWER
RUBENS 1234222c BM STU RUBENSTEIN PROD MODIFY 99990101 KIRSCMP Jun 30 1997 6:48PM BM POWER
WEBERH HOLLY BM HOLLY WEBER PROD MODIFY 99990101 MAHONP May 5 1999 8:46AM BM POWER
I need to put all the data from userlist table to userlist_email table.
MY ORIGINAL WORK STARTED AS TO ADD A NEW COLUMN INTO THE USERLIST TABLE CALLED UL_EMAIL VARCHAR(50) NOT NULL.
PLEASE HELP!
THANKS
VSJ
if you really want to use bcp, then you will want to use a view
1. create a view over the userlist table will all columns plus one dummy not null column for the email
2. then bcp out of this view
3. bcp in into the new table
frankly I would go with just use an INSERT ... SELECT. especially since there is only 43 rows
1. create a view over the userlist table will all columns plus one dummy not null column for the email
2. then bcp out of this view
3. bcp in into the new table
frankly I would go with just use an INSERT ... SELECT. especially since there is only 43 rows
ASKER
This is old sybase version ,how can I do insert ..............select when there is a not null column in the second table
and for the bcp in and out for ther views can you help me with the syntax please
thanks
vsj
and for the bcp in and out for ther views can you help me with the syntax please
thanks
vsj
INSERT ... SELECT
============
insert into userlist_email
( ul_id
, ul_password
, ul_location
, ul_name
, ul_group_id
, ul_authority
, ul_oldest_deldate_yyyymmdd
, ul_updateuserid
, ul_updatedatetime
, ul_handset
, ul_phonenumber
, ul_print_area
, ul_email
)
select ul_id
, ul_password
, ul_location
, ul_name
, ul_group_id
, ul_authority
, ul_oldest_deldate_yyyymmdd
, ul_updateuserid
, ul_updatedatetime
, ul_handset
, ul_phonenumber
, ul_print_area
, ul_email = 'dummy email address'
from userlist
BCP FROM VIEW
===========
create view userlist_view
as
select ul_id
, ul_password
, ul_location
, ul_name
, ul_group_id
, ul_authority
, ul_oldest_deldate_yyyymmdd
, ul_updateuserid
, ul_updatedatetime
, ul_handset
, ul_phonenumber
, ul_print_area
, ul_email = 'dummy email address'
from userlist
go
bcp oil_grain_ops.dbo.userlist _view out ogo.dat -Ssybcps2 -Usa -Ptest01 -n
bcp oil_grain_ops.dbo.userlist _email in ogo.dat -Ssybcps2 -Usa -Ptest01 -n -b5000
============
insert into userlist_email
( ul_id
, ul_password
, ul_location
, ul_name
, ul_group_id
, ul_authority
, ul_oldest_deldate_yyyymmdd
, ul_updateuserid
, ul_updatedatetime
, ul_handset
, ul_phonenumber
, ul_print_area
, ul_email
)
select ul_id
, ul_password
, ul_location
, ul_name
, ul_group_id
, ul_authority
, ul_oldest_deldate_yyyymmdd
, ul_updateuserid
, ul_updatedatetime
, ul_handset
, ul_phonenumber
, ul_print_area
, ul_email = 'dummy email address'
from userlist
BCP FROM VIEW
===========
create view userlist_view
as
select ul_id
, ul_password
, ul_location
, ul_name
, ul_group_id
, ul_authority
, ul_oldest_deldate_yyyymmdd
, ul_updateuserid
, ul_updatedatetime
, ul_handset
, ul_phonenumber
, ul_print_area
, ul_email = 'dummy email address'
from userlist
go
bcp oil_grain_ops.dbo.userlist
bcp oil_grain_ops.dbo.userlist
> MY ORIGINAL WORK STARTED AS TO ADD A NEW COLUMN INTO THE USERLIST TABLE CALLED UL_EMAIL VARCHAR(50) NOT NULL.
why not just add a nullable column ?
then change it to NOT NULL later when every row has an email
why not just add a nullable column ?
then change it to NOT NULL later when every row has an email
ASKER
I ran this insert ...select
insert into userlist_email
( ul_id
, ul_password
, ul_location
, ul_name
, ul_group_id
, ul_authority
, ul_oldest_deldate_yyyymmdd
, ul_updateuserid
, ul_updatedatetime
, ul_handset
, ul_phonenumber
, ul_print_area
, ul_email
)
select ul_id
, ul_password
, ul_location
, ul_name
, ul_group_id
, ul_authority
, ul_oldest_deldate_yyyymmdd
, ul_updateuserid
, ul_updatedatetime
, ul_handset
, ul_phonenumber
, ul_print_area
, ul_email = 'dummy email address'
from userlist
AND GOT THIS ERROR:
Server Message: Number 208, Severity 16
Line 1:
Invalid object name 'userlist_email'.
insert into userlist_email
( ul_id
, ul_password
, ul_location
, ul_name
, ul_group_id
, ul_authority
, ul_oldest_deldate_yyyymmdd
, ul_updateuserid
, ul_updatedatetime
, ul_handset
, ul_phonenumber
, ul_print_area
, ul_email
)
select ul_id
, ul_password
, ul_location
, ul_name
, ul_group_id
, ul_authority
, ul_oldest_deldate_yyyymmdd
, ul_updateuserid
, ul_updatedatetime
, ul_handset
, ul_phonenumber
, ul_print_area
, ul_email = 'dummy email address'
from userlist
AND GOT THIS ERROR:
Server Message: Number 208, Severity 16
Line 1:
Invalid object name 'userlist_email'.
Eliminating the obvious errors first:
Were you in the correct database (and server)?
Is userlist_email owned by 'dbo'?
Were you in the correct database (and server)?
Is userlist_email owned by 'dbo'?
I suggest a more practical solution. Edit your bcp file and add a new column with any value, let's say "X". You can do it by vi.
vi ogo.dat
Then type colon and enter below command:
:1,$ s/$/|X/
Then save the file and run bcp in.
vi ogo.dat
Then type colon and enter below command:
:1,$ s/$/|X/
Then save the file and run bcp in.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have you tried using the -J option when you bcp you data out?
i.e. bcp oil_grain_ops.dbo.userlist out ogo.dat -Ssybcps2 -Usa -Ptest01 -J
i.e. bcp oil_grain_ops.dbo.userlist
This is exactly the same as if you ran this in SQL :
insert userlist_email
select * from userlist
The number of columns won't match up, so it's as if you're inserting NULLs into the new column - but that isn't allowed.
Even if the column *did* allow NULLs, the bcp will still fail because it will be supplying an end of row character before the end of the table's row is reached.
Given this is in the same server, it's probably easiest just to run this as a SELECT INTO:
SELECT *. "some value for the new column"
INTO userlist_email
FROM userlist
If you couldn't do that, then you could specify a bcp format file and do the bcp in native mode, and make the column allow NULLs.
If you're determined to do this with BCP in character mode, then your other option is to create a view:
create view userlist_new_column
as
select *, [literal value of the correct datatype'
from userlist
and then (providing you're using any reasonably modern version of bcp, ie. 11.1 onwards) bcp out of the view instead of the table.
(I am assuming you do not have any carriage returns or "|" characters anywhere in your character data...)
Good luck!