Add Column Values in bcp? - 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

Import/Export (DTS) and Replication (2000)

Add Column Values in bcp?

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-10 : 10:20:26

I'm trying to import a text file to a table. The problem is my table has 10 colums while my input files will have values only 9 columns, the 10th Column, Iam supposed to fill in the name of the file which Iam importing...

If I go for dts I know its possible, is this possible with BCP?

Thanks

Karunakaran

Kristen
Test

22859 Posts

Posted - 2005-10-10 : 10:33:51

I would "import" into a staging table with BCP, and then from there into the real table using


INSERT INTO dbo.RealTable
(
    Col1, ... Col10
)
SELECT Col1, ... Col9, 'ImportTableName'
FROM dbo.MyStagingTable

Kristen

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-10 : 10:46:41

Thanks Kristen.

I was thinking about that idea for a while, but the problem for me is that I have some 12 files which I have to import to some 7 tables. so far each table i need 1 staging table which makes 14 tables overall. Thats the reason I'm little hesitent to go with staging table. Looks like now i dont have any other option.

Another quick question is some website I read bulk insert is faster than bcp is it true?

Thanks Again.

Karunakaran

Kristen
Test

22859 Posts

Posted - 2005-10-10 : 10:49:12

"bulk insert is faster than bcp"

I thought it was using the same underlying system, but maybe not!

Kristen

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-10 : 13:26:00

quote:
Originally posted by Kristen

"bulk insert is faster than bcp"

I thought it was using the same underlying system, but maybe not!

I'm not sure about the underlying system.

If possible, use the BULK INSERT statement rather than the bcp utility to bulk copy data into SQL Server. The BULK INSERT statement is faster than the bcp utility.

I found this in BOL..Hope somebody can shed light on this...

Thanks

Karunakaran

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-10 : 14:43:15

You can create a view on the table you are importing into that contains only the columns you want to import, and BCP into the view.

The columns not listed in the view will have to be nullable or have a default constraint.

CODO ERGO SUM

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-10 : 15:31:40

quote:
Originally posted by Michael Valentine Jones

You can create a view on the table you are importing into that contains only the columns you want to import, and BCP into the view.

The columns not listed in the view will have to be nullable or have a default constraint.

CODO ERGO SUM

Thanks MVJ...

It worked !!!

Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources