March 28, 2007 at 10:52 am
try putting double quotes (") around the troublesome column name.
Carlos
March 28, 2007 at 11:11 am
Thanks so much! It worked. One more thing. I ran this query against an empty table. Now that the table has data in it I can't append duplicate primary keys if I want to run this query again with different data. How can I get around this?
March 28, 2007 at 11:17 am
There are some options, none of them pretty.
1. create a hole for your data. That would involve updating all the identity numbers for the current data to something higher than the highest number in your old data. Ugly, I know. Specially if there are tables which join on the identity column.
2. run a command to change the identity numbers in the old data to something higher than the last entry in the new table.
set identity_insert table on
update table set identity_column_name = identity_column_name + some_number
set identity_insert table off
At this time, I can't think of any other solution.
Carlos
March 28, 2007 at 12:12 pm
I'll try this. Thanks for all your help. This forum and people like you are a lifesaver!
March 28, 2007 at 1:02 pm
I opt to try your second suggestion:
set
identity_insert dbo.query on
update
dbo.query set identity signon = identity_signon + 112126
set
identity_insert dbo.query off
I got this error.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'identity_signon'.
my column name is "signon"
March 28, 2007 at 1:08 pm
there's a space on your SQL for the column "identity signon" The query should be:
set identity_insert dbo.query on
update dbo.query set identity_signon = identity_signon + 112126
set
identity_insert dbo.query off
assuming that your column name is identity_signon
Carlos
March 28, 2007 at 1:12 pm
The pk column in question is named "signon"; not "identity_signon" I tried without the word identity and received this error:
Table 'dbo.query' does not have the identity property. Cannot perform SET operation.
March 28, 2007 at 1:13 pm
I just read your post more carefully. With column name "signon" then the proper syntax is:
set identity_insert dbo.query on
update dbo.query set signon = signon + 112126
set
identity_insert dbo.query off
Carlos
March 28, 2007 at 1:21 pm
Sorry, I steered you wrong on the update of the identity column, it can't be done. If you send me the list of columns for your table, I'll send you code (using a cursor) to insert the rows.
Carlos
March 28, 2007 at 1:30 pm
I was thinking I could modify the identity column and change the starting number but when I modify and change it won't save. Here's a list of my columns in my table. Thanks
SignonID, BranchID,emplID, Signin_Date, Member_Name, Member_No, Gender, empl_Pickup_Time, empl_Complete_Time, Reason1, Reason2, Reason3,Remarks, "Appointment?", Appointment_emplID
March 28, 2007 at 2:02 pm
Ok, before I create the code, please try the following on the table.
update query set signon = signon + 125000 (or change 125000 to any number you prefer)
Let me know the result.
Carlos
March 28, 2007 at 2:07 pm
I just thought of an easier way.
insert into newtable
(BranchID,emplID, Signin_Date, Member_Name, Member_No, Gender, empl_Pickup_Time, empl_Complete_Time, Reason1, Reason2, Reason3,Remarks, "Appointment?", Appointment_emplID)
select
BranchID,emplID, Signin_Date, Member_Name, Member_No, Gender, empl_Pickup_Time, empl_Complete_Time, Reason1, Reason2, Reason3,Remarks, "Appointment?", Appointment_emplID
from oldtable
This will generate new signonid on the target table.
If this is not clear, and you want to call me: 650 743-5097
Carlos
March 28, 2007 at 2:20 pm
Thank you! You're a life saver.
March 28, 2007 at 2:23 pm
Here's the cursor code in case you need it (you shouldn't) Change the table names in the code from newtable and oldtable to the real names.
declare @signonid int
declare c cursor for select signonid from newtable
open c
fetch c into @signonid
while @@fetch_status = 0
begin
set identity_insert query on
insert into query (SignonID, BranchID,emplID, Signin_Date, Member_Name, Member_No, Gender, empl_Pickup_Time, empl_Complete_Time, Reason1, Reason2, Reason3,Remarks, "Appointment?", Appointment_emplID)
select signonid + 125000, BranchID,emplID, Signin_Date, Member_Name, Member_No, Gender, empl_Pickup_Time, empl_Complete_Time, Reason1, Reason2, Reason3,Remarks, "Appointment?", Appointment_emplID
from oldtable where signonid = @signonid
set identity_insert query off
fetch c into @signonid
end
close c
deallocate c
Carlos
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply