BCP

Question

BCP

IMary

Old Hand

Points: 361
More actions
January 5, 2020 at 10:18 am

#3712435
Hi,
I have below requirement. Please advise how to achieve that with BCP or any other tool
1. Have Table A with 5 million records
2. Adding few columns to Table A and those are not null column
So, can we simply add these columns or we need to export data from Table A and then drop Table A and then recreate it with new columns and then import data back?
What should be the correct approach here?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

What does this have to do with BCP? BCP means Bulk Copy Program; it doesn't perform DDL changes likes these, it's for data migration from one location to another.

If you want to add a column as NOT NULL , without a separate UPDATE statement, you need to use WITH VALUES:

CREATE TABLE dbo.YourTable (ID int IDENTITY(1,1),
                            SomeString varchar(10) NOT NULL);
GO

INSERT INTO dbo.YourTable (SomeString)
VALUES('abc'),('sdjkf'),('sdf'),('sdfhj'),('sdoho;');
GO
--Will fail
ALTER TABLE dbo.YourTable ADD SomeInt int NOT NULL;
GO
--Needs a default value, and to be populated
ALTER TABLE dbo.YourTable ADD SomeInt int NOT NULL DEFAULT 0 WITH VALUES;
GO

ALTER TABLE dbo.YourTable ADD SomeDate date NOT NULL DEFAULT GETDATE() WITH VALUES;

If you don't want to use a DEFAULT value, you'll need to add the column, set all the values, and then ALTER the the table again:

ALTER TABLE dbo.YourTable ADD SomeDecimal decimal(10,2) NULL; --Defined as NULL
GO
UPDATE dbo.YourTable
SET SomeDecimal = CHECKSUM(NEWID()) % 10;
GO

ALTER TABLE dbo.YourTable ALTER COLUMN SomeDecimal decimal(10,2) NOT NULL; --Change to NOT NULL

GO
--Clean up
DROP TABLE dbo.YourTable;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

IMary wrote:

Hi,
I have below requirement. Please advise how to achieve that with BCP or any other tool

<li style="list-style-type: none;">
Have Table A with 5 million records

<li style="list-style-type: none;">
Adding few columns to Table A and those are not null column

So, can we simply add these columns or we need to export data from Table A and then drop Table A and then recreate it with new columns and then import data back?
What should be the correct approach here?

I don't see anything having to do with BCP here. You can simply add the new NOT NULL columns provided that you assign a default at the same time. It'll happen very quickly (unlike the old days) because it won't actually cause the table to expand in size for the old rows anymore. It assigns the default to be used for old rows without actually updating the rows.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JigarShah Old Hand Points: 336 More actions · Answer 3

Hi,

Assuming you have to add 3 columns with data to the existing 5million row table.

Following is the approach which you can use -

Extract the 5 million rows data in excel or any other file.

2. Add the 3 column data in that data file.

3. Rename the main table (say XXX_Old) (taking care of all the constraints, keys, triggers etc)

4. Import the data file into the database using bcp or Import wizard. Give the original name to this table. Re-create the constraints, keys, triggers

5. if everything is good and proper, you may delete the old table (XXX_Old).

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

JigarShah wrote:

Hi,
Assuming you have to add 3 columns with data to the existing 5million row table.
Following is the approach which you can use -

<li style="list-style-type: none;">
Extract the 5 million rows data in excel or any other file.

2. Add the 3 column data in that data file.
3. Rename the main table (say XXX_Old) (taking care of all the constraints, keys, triggers etc)
4. Import the data file into the database using bcp or Import wizard. Give the original name to this table. Re-create the constraints, keys, triggers
5. if everything is good and proper, you may delete the old table (XXX_Old).

You just don't need to do this. Adding columns with a default no longer provides the arduous problems that it used to.

Also, you can't put 5 million rows into a single sheet in Excel. Even if you could, that would be a little tough on memory. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)