INSERTING MULTIPLE ROWS W/ Primary Key

Last Post 02 Apr 2009 11:55 AM by sschmid4. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
sschmid4
New Member
New Member

--
01 Apr 2009 10:02 AM
I have a table (table_1) that has a Primary Key column of type INT but the field is not an identity field.
CREATE TABLE [dbo].[table_1](
[prog_id] [int] NOT NULL,
[name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[add_user_id] [int] NOT NULL,
[add_date] [datetime] NOT NULL,
[chg_user_id] [int] NOT NULL,
[chg_date] [datetime] NOT NULL,
CONSTRAINT [PK_CsOmProgram] PRIMARY KEY NONCLUSTERED
(
[prog_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I will be receiving back 1,000+ records than need to be written to table_1. I would prefer using INSERT...SELECT functionality but how do I assign a new (valid) primary key value to each row as I insert it? I am hoping there is a set theory-based solution which avoids cursors but any solution/suggestion is appreciated.
sschmid4
New Member
New Member

--
02 Apr 2009 11:55 AM
The last id value is kept in a 1 row table. This table is then referenced by the application to increment a counter by 1 and the load the new row with the new id value. The application does not allow multi-row inserts.


Acceptable Use Policy
---