SQL Server Performance Forum – Threads Archive

INSERT INTO error

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
Toni Chaffin
aka Toni

---

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.
Toni Chaffin
aka Toni

---

Thank you for reporting resolution. If I got your explanation right, identity column is the solution for your problem.

---

I think it will solve my problem. I have yet to fully test it but I am hopeful. Regards Toni Chaffin
aka Toni

---

]]>