bcp fighting me, anyone with working example syntax to export / import a table? (2 Viewers)

[mdlueck]

I am accustomed to performing all schema changes via DDL scripts. The only other SQL database I have done such detailed development work with is DB2 Universal Database. This time I am working with SQL Server 2008 R2.

So, I came across the bcp (bulk copy Porsch) utility in SQL Serer which seemed comparable to the export / import capabilities in DB2.

I need to add a couple of columns to one table. So order of script oertaion is as follows:

1) Export existing data
2) Drop the table
3) Create the table with additional columns / index
4) Disable IDENTITY
5) Import legacy data (Bang!!)
6) Re-Enable IDENTITY
7) Drop default values for the additional / new columns

At first it sounded like a no-brainer to allow bcp to use the -n Native format. bcp complains that: The Import did however import one record before banging. Only one of the two new columns received its default value, however.

So I have tried to use the -c encoding format along with a format file. Using this method just bangs right away and imports no rows.

I seem to be trapped in this type of error:

Unexpected EOF encountered in BCP data-file

Does anyone have working sample command line syntax of how to get bcp to work properly?

Is there some reason, such as bdc can not import back into a table with additional columns - even though default values are supplied for the additional columns - that bcp would be putting up such a fight?

 

[mdlueck]

I can bcp export / import if I do not change the table schema.

:banghead:

But I NEED to change the table schema! ggggrrrr.....

In this area, DB2 UDB is MUCH easier to work with than SQL Server.

 

[Lightwave]

Have you tried linking the old and new tables to an access mid point and then using Access queries to transfer the information between the backends.

I found this much the best way of getting information into SQL Server 2005.

 

[mdlueck]

@Lightwave: I would prefer an entirely SQL Server based ETL solution. "DB2 can do it!" DDL/DML scripts I may check into source code control attached to this particular upgrade.

I ended up hand coding a SQL based INSERT script. The turn off of the auto number column so I may restore the records only seems to stay in effect during a single transaction. So I do not know how it would be possible to start a SQL transaction to turn off the auto numbering, switch to bcp.exe, and then turn back on the auto numbering with further SQL. Just ugly, ugly, ugly compared to DB2... (I can remember in the DB2 UDB v5.0 pre-release conference in Toronto, IBM having said that the number one complaint about DB2 v2 was that the admin interface was clunky / cryptic and people were preferring SQL Server. "Shoe is on the other foot now!")

Oh... I saw reference to BULK INSERT via CSV file. Is there some way to export to CSV natively with SQL Server? That might be viable and save me the trouble of creating the SQL INSERT script. And that would be an entirely SQL based solution so I could turn off the auto number, perform the BULK INSERT, and then turn the auto numbering back on.

 

[SQL_Hell]

Can't help regarding BCP I have only ever done these types of things in SSIS / DTS.

Where I am sure you would be able to achieve this

 

[mdlueck]

Late in the day Friday, I came upon this discussion thread:

http://social.msdn.microsoft.com/fo...e4bae1e/#db8249b2-b75d-4e92-b71e-41d3a237ac8b

Which includes humorous quotes:

You are telling me that the CSV export in SQL Server is not actually what is generally understood as CSV - it's just comma delimited output:

Why can't SQL Server support CSV properly? Is it so hard?

<><><><><>

It seems odd that every other application that I have used can deal with a text qualifier, even the lowly MS Access has been able to deal with them for years.

<><><><><>

I believe this is a limitation with the current implementation of the FlatFile connection manager and there is no fix in the 2008 release. A serious problem in my opinion as it forces every developer to write script to escape those quotes.

etc....

So I decided to use tiny pieces of Access within my ETL process. Copy/Paste this line of code into the VBA editor Immediate window:

DoCmd.TransferText acExportDelim, , "dbo_projects", "C:\Documents and Settings\c_mlueck\Desktop\projects.csv"

And the proper CSV file is created at the filespec specified.

So, for the Access steps in the ETL process, I may copy/paste those above lines into a txt file and check that into version control to capture the necessary step.

Off to the races now!

 

[SQL_Hell]

Use Text Qualifier

Text qualifier is another option that is not available to BCP but it can be worked around as well. In the BCP
query you can make use of the TSQL function Quotename, Which surronds the field with quotes or what ever charater
you want functioning very closely to the SSIS Text Qualifier.

Example:
BCP " select quotename(AddressID,CHAR(34)),quotename(AddressLine1,CHAR(34)),quotename(AddressLine2,CHAR(34)) from AdventureWorks.Person.address" queryout C:\bcp\temp2.csv -c -T -S. -t"|"


Taken from http://social.technet.microsoft.com...es/4666.aspx#Use_Text_Qualifier_on_BCP_Output

Strange that you do have an option to use a text qualifier in SSIS and the import / export wizard.

Nowadays I tend to use a better delimiter anyway such as ¬ (pipe)