SQL Server: Insert from select, function-generated primary key, Cannot insert duplicate key

Question

I have a table in SQL Server.

It has a single column Primary Key.
The Key is nvarchar and looks (mostly) like 'C12345'
This key is autogenerated by a scalar function.
Inserting records in my app is trivial, and works without a hitch.

The problem:
I am trying to upload some spreadsheet data from a table I created to the data table. the spreadsheet data is already in a table.

insert into sales (Customer, Invoice_Date, upload_Po, UploadID)
(
    select 'CUSTOMER', getdate(), PoNo, ID
    from UPLOADS.Upload_Sales
    where ImportDone=0
)

This crunches for over 33 minutes (my fault for testing on 3707 rows), and then gives:

Msg 2627, Level 14, State 1, Line 9
Violation of PRIMARY KEY constraint 'PK_Sales'. Cannot insert duplicate key in object 'dbo.Sales'. The duplicate key value is (C109666).
The statement has been terminated.

(0 rows affected)

Obviously it's executing my proc once for every line, but not inserting until all the values are created. Since the function uses the data existing in the table to find the next value, it's always giving the same ID.

Is there some way to make this insert go
CreateID, Insert, CreateID, Insert, CreateID, Insert, CreateID, Insert, CreateID, Insert
instead of
CreateID, CreateID, CreateID, CreateID, CreateID, Insert, Insert, Insert, Insert, Insert
???

Answer 1

Hi,

I have a table in SQL Server.

If the following explanation does not solve your need, then please instead of stories (description), provide queries to create the tables and to insert sample data (several tows). This will allow us to reproduce the scenario. Please describe the expected result SET which you want to get from that sample data.

I am trying to upload some spreadsheet data from a table I created to the data table.

The format of the query which you use is wrong. In order to INSERT data from a SELECT query you should use the format

INSERT Table_Name(column list comes here) 
SELECT ...<any select query which you want and returns the exact same structure of columns which you want to INSERT>

Here you have a demo:

use tempdb
GO

create table A (id int)
insert A(id) values (4),(345),(2),(56)
create table B (id int)
GO

INSERT A(id)  SELECT id from B
GO

Answer 2

It sounds as if this table has a default for the PK column which is a call to this scalar UDF. That design is not very fortunate, because it means that you only can do single-row inserts. So you would need to run a cursor over your Sales table on insert one row at a time.

Or give it a second thought on whether you really want this design...