Problem inserting multiple records in one INSERT

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

General SQL Server Forums

New to SQL Server Programming

Problem inserting multiple records in one INSERT

Author

Topic

61753
Starting Member

2 Posts

Posted - 2010-06-08 : 06:06:13

I need to insert one or many rows in one INSERT query (with a SELECT).
I use SQL Server 2005.

My problem is that I have a a column [Number], and that number must be unique and calculated thanks to a function.
I followed this article [url]http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server[/url] which helped me, but it seems to be appropriate for one record at a time.

Example :


 INSERT INTO Invoicing.Invoices
 (
    Number,
    OrderID
 )
 SELECT
    Invoicing.GetInvoiceNumber(OrderID),
    OrderID
 FROM Invoicing.Order

[Invoicing].[Invoices] table has a PK ([InvoiceID]) with auto increment
So my problem is when my SELECT returns more than one order, the field [Number] is the same for every record inserted in [Invoicing].[Invoices].

I tried to make the [Number] column has computed, but I want the value to be persisted and the function called is not deterministic.

PS : fot this project it is preferable not to use cursors to insert each record separatly.

Thanks, hoping someone can help me with this.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-08 : 06:15:00

generally if you need to autonumber a column like this you would make that column have a default that calls the function. Then you wouldn't insert directly into the column -- just the other columns.

We need more info to help you though.

If you post what you data looks like now -- what data you need to add and how you need it to look at the end.

Also -- post the code for your function.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

61753
Starting Member

2 Posts

Posted - 2010-06-09 : 05:29:33

I found a solution.
When I call my function, I add the row number of the select :


 INSERT INTO Invoicing.Invoices
 (
    Number,
    OrderID
 )
 SELECT
    Invoicing.GetInvoiceNumber(OrderID) + ROW_NUMBER() over (order by CorporationID) - 1,
    OrderID
 FROM Invoicing.Order

It works, I just have to add this for each query which can insert multiple records from a select.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-06-09 : 05:55:04

quote:
It works,

You had better stress test this with multiple concurrent users.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources