INSERT INTO a table with an ID field set to NULL

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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

INSERT INTO a table with an ID field set to NULL

Author

Topic

jim.barber
Starting Member

2 Posts

Posted - 2014-02-19 : 08:21:51

Ok… I didn’t know who could help me on this one so I thought I would call on the experts!
Basically, I need to get data from one table to another. They are identical with the exception of the ID field. The source ID field is a seeded field and the destination ID field is not. The Destination ID field will not allow NULLs. Here’s the error I’m getting and the code follows. Anything you could do would be greatly appreciated to the point of buying you a beer(s)!!!

Thanks in advance

J

________________________________________
Checking identity information: current identity value '32', current column value '16'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
1
Msg 2627, Level 14, State 1, Line 14
Violation of PRIMARY KEY constraint MemberAddress'. Cannot insert duplicate key in object MemberAddress'. The duplicate key value is (1).
The statement has been terminated.

USE DATABASE_dev;
BEGIN

DECLARE @SeededID INT
SET @SeededID = (SELECT COUNT(SBSB_ID) FROM MEMBER_ADDRESSES_STAGING_TEMP)
DBCC CHECKIDENT ('MemberAddress_Temp', RESEED,@SeededID)

DECLARE @idcount INT ;
SET @idcount = ((SELECT COUNT ([ID]) FROM MemberAddress)+1);

PRINT @idcount; -- view variable (debug)

INSERT INTO MemberAddress(

[ID] -- field accepts NO nulls but is not seeded
,[SubscriberID]
,[AddressTypeID]
,[StartDate]
,[StreetAddress]
,[CityName]
,[StateName]
,[Zip]
,[HomePhone]
,[MobilePhone]
,[OtherPhone])

(SELECT

@idcount -- variable to insert into MemberAddress.ID field
,[SubscriberID]
,[AddressTypeID]
,GETDATE()
,[StreetAddress]
,[CityName]
,[StateName]
,[Zip]
,[HomePhone]
,[MobilePhone]
,[OtherPhone]

FROM MemberAddress_Temp)

END

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-02-19 : 09:23:16

Not sure if this will work but it might give you an idea


INSERT INTO MemberAddress(
    [ID] -- field accepts NO nulls but is not seeded
    ,[SubscriberID]
    ,[AddressTypeID]
    ,[StartDate]
    ,[StreetAddress]
    ,[CityName]
    ,[StateName]
    ,[Zip]
    ,[HomePhone]
    ,[MobilePhone]
    ,[OtherPhone])
SELECT 
    myid = ROW_NUMBER() OVER(ORDER BY SubscriberID) + @idcount 
    ,[SubscriberID]
    ,[AddressTypeID]
    ,GETDATE() AS StartDate
    ,[StreetAddress]
    ,[CityName]
    ,[StateName]
    ,[Zip]
    ,[HomePhone]
    ,[MobilePhone]
    ,[OtherPhone]
FROM MemberAddress_Temp

djj

jim.barber
Starting Member

2 Posts

Posted - 2014-02-20 : 08:06:52

Thanks for getting back to me so quick. About an hour after I posted this, I figured it out:

row_number() over (order by ID)+ @maxid

Thanks again for the help.

J

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources