SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 INSERT INTO a table with an ID field set to NULL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jim.barber
Starting Member

2 Posts

Posted - 02/19/2014 :  08:21:51  Show Profile  Reply with Quote
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

Edited by - jim.barber on 02/19/2014 08:44:33

djj55
Constraint Violating Yak Guru

USA
333 Posts

Posted - 02/19/2014 :  09:23:16  Show Profile  Reply with Quote
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
Go to Top of Page

jim.barber
Starting Member

2 Posts

Posted - 02/20/2014 :  08:06:52  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000