partitioned views and IDENTITY fields/ unique ids

Question

partitioned views and IDENTITY fields/ unique ids

netedk

SSC Veteran

Points: 227
More actions
February 26, 2004 at 1:24 pm

#160503

I have a table with a large number of records . I have decided to horizontally partition the table and I intent to use a partitioned view to select, insert, update and delete data.
My old table has an IDENTITY field as the primary key. In the partitioned scheme I plan to use a composite key - id, partition_id as the primary key. The id field here is not IDENTITY and I have to auto-increment the field using programmatic logic (this is because IDENTITY fields cannot be a part of the primary key for partitioned views to work).
Now, my old table has some BULK INSERT statements running on it. The BULK INSERT uses a .csv file - this file does not have a id field - the values are being generated automatically. But if I am to move away from IDENTITY field - how should I do BULK INSERT and still assign unique values into the 'id' field?
This is the first time I am working with partitioned views - so am not very clear about this - any help will be greatly appreciated.
Thanks a lot.....
DK

Viewing 10 posts - 1 through 9 (of 9 total)

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

TriggerMe SSChampion Points: 11752 More actions · Answer 1

One way to do this is to BULK INSERT from the csv into a temporary table, then from the temporary table insert into the real table using your programmatic logic method.

Kindest Regards,

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 2

I agree with MrSQL. Load the data into a staging table with BulkInsert then you can insert the data into the final table doing any kind of programatic manipulation you want.

The other option is to "preprocess" the csv file to include the primary key then do a bulk load into SQL.

netedk SSC Veteran Points: 227 More actions · Answer 3

Thanks for your input! I think I will try to "preproces" the csv file to include the primary keys in it.

The other option would not work for me as this table is itself a staging table. I need a sequentially increasing id because I have some queries that use them.

Thanks again...

DK

TV SSC Rookie Points: 38 More actions · Answer 4

Hello,

I have the same problem at my end where I need to bulk upload into a table with an IDENTITY.

I can see both the solutions you guys are suggesting.

1. Insert Primary key in the CSV file: The problem here is that my bulk upload is not a one off process. There would be uploads every month. So this solution is not useful for me.

2. Put the bulk upload into a temporary table and then manipulate and put it into the real one: My bulk upload would have thousands of records every time. Would it not affect the performance of the server if the temporary table manipulates the data and upload it row by row into the actual table?

Any help on this would be much appreciated.

Thank You

netedk SSC Veteran Points: 227 More actions · Answer 5

Hi!

If your field is defined as an "IDENTITY" field - then you can just ignore that field in your upload - SQL will automatically insert unique ids in that field - use a format file in your Bulk INsert statement to ignore the IDENTITY field while uploading.

See BOL for more info on Format files.

TV SSC Rookie Points: 38 More actions · Answer 6

Thank you very much for the reply. But how do I use a format file in Bulk Insert? Searching for BOL did not give any results in SQL Server help.

Really sorry for the trouble.

Thank You

netedk SSC Veteran Points: 227 More actions · Answer 7

Check here for info on Format Files:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_9yat.asp

Or IN BOL, go to:Administring SQL Server> Importing Exporting Data> Using BCP and Bulk Insert> Using Format Files

TV SSC Rookie Points: 38 More actions · Answer 8

Thank you very much for the link.

I had been working on another solution using views.

Here is my script.

create view v

as

select code, description from Category

go

master..xp_cmdshell 'bcp tempDB..v in c:\crimcode.csv -c -t,'

bulk insert tempDB..v from 'c:\code.csv' with (FIELDTERMINATOR = ',')

drop view v

It works fine and automatically inserts the IDENTITY key, but inserts all the values twice. ANy ideas why??

Thank you

TV SSC Rookie Points: 38 More actions · Answer 9

Hello,

I have successfully done what I wanted to do with the Format File.

My Table had 3 fileds. IDENTITY, code and description.

The CSV file had two fields, code and description.

Here is my Format file

8.0

2

1 SQLCHAR 0 10 "," 2 Code SQL_Latin1_General_CP1_CI_AS

2 SQLCHAR 0 100 "\r\n" 3 Description SQL_Latin1_General_CP1_CI_AS

and here is my BULK insert statement.

BULK INSERT

StatsDB.dbo.[CodeCategory]

FROM

'c:\codecat.csv'

WITH

( FORMATFILE = 'c:\code.fmt',

CHECK_CONSTRAINTS, FIELDTERMINATOR = ',', KEEPIDENTITY, KEEPNULLS, TABLOCK ,

ROWTERMINATOR = '\n'

)

Thank You