BCP Import and Export - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

BCP Import and Export

Author

Topic

7crystal7
Starting Member

7 Posts

Posted - 2003-04-03 : 04:32:39

Hi guys,

can anyone give me a correct syntax how to import and export a table with the bcp command line utility?

Thank
Babsi

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-03 : 04:41:14

bcp dbname..tblname in c:\myfile.txt -Uuser -Ppwd -Ssrvname -c
bcp dbname..tblname out c:\myfile.txt -Uuser -Ppwd -Ssrvname -c

see bol for options

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

7crystal7
Starting Member

7 Posts

Posted - 2003-04-03 : 04:51:19

thx,

I export a table from a sql server 6.5 platform with bcp.. this works

but the bcp in doesn't work..

Do I have to create an empty table with the same name or what is my problem? Do you know how this format file works?

Thx
Babsi

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-03 : 05:04:26

The example I gave doesn't have a format file.

The table has to exist with the correct format. A copy of the export table should work.

If you want to just export and import consider using -N instead of -c for native format.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

7crystal7
Starting Member

7 Posts

Posted - 2003-04-03 : 05:10:41

Ok, thanks

but do i have to create an empty table in the sql server 2000 where the table form the 6.5 will be imported?

Babsi

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-03 : 05:21:31

Yes the table has to exist.

Another option if you can connect to both servers at the same time is to create a remote server on the 2000 server pointing at the 6.5 server.
Create an sp on the 6.5 server to select the data.
Then on the 2000 server
insert mytbl exec svr65.dbname.dbo.spGetData

bcp is often a good idea as you can do the import many times and you have a saved copy of the data.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

7crystal7
Starting Member

7 Posts

Posted - 2003-04-03 : 05:33:30

Ok, but do I have to create the table which already has to exist manually and add all colums manually?

Thanks
Babsi

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-03 : 05:42:45

Yes the table has to exist.
It's not going to be there unless you create it.

You can script it from one server and run the script on the other.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

7crystal7
Starting Member

7 Posts

Posted - 2003-04-03 : 05:48:12

Ok, sorry I'm a "newbie"

Maybe you can tell me where I can do this (scripting)??

Thank for your patience! ;)

Babsi

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-03 : 05:54:12

From enterprise manager you can script the tables.
Not sure how to get to it in 6.5 but if you display the tables you should find it.
It might be worthwhile scripting all the tables while you're at it.

If you can connect via dts to both servers from the 2000 server you could transfer the object which would do the create as well.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

7crystal7
Starting Member

7 Posts

Posted - 2003-04-03 : 06:20:12

GReat!!

Thx, it works ;-)

Cu
Babsi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources