May 10, 2006 at 11:36 pm
Greetings,
I have a INSERT sp that just copies a few lines from one table into another. The problem I am hitting is that one of the fields in the target table is an ID field (this is from when the table started its life in MS Access and used Autonumber). Obviously this field does not accept NULL values. How can I get the import process to automatically add the next sequential number in the ID field as part of this stored procedure?
USE
[DashboardSQL-2K5]
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER
PROCEDURE [dbo].[spAppendDataDebtors]
AS
INSERT INTO dbo.src_tbl_debt
(site_ref, estate_name, amount, src_date)
SELECT
site_ref, estate_name, amount, src_date
FROM
dbo.Debtors$
Thank you in advance
May 11, 2006 at 1:28 am
Think I might have solved this by leaving the field as an 'int' and set 'identity', 'identity seed', and 'identity increment'.
I hope that this works and will solve my issue.
May 11, 2006 at 12:07 pm
Is one of the fields in your query there the identity field? If so, that won't work unless you have the IDENTITY INSERT set to ON.
If it's not, then SQL Server should be automatically updating the identity field. That is, after all, its purpose.
May 11, 2006 at 11:13 pm
Thank you for that.
Regards
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply