sql server and website

michaeldean99
michaeldean99 used Ask the Experts™
on
hi

I recently moved my website to new hosting. At that point I turned off all membership login etc because the site security needed improving and have not added or updated the customer tables from that point.
I am using a local copy of 2012 express to access the hosted database
I now want to turn the customer processing on.
There is now a problem with the customer table. It has all the existing values from before but the CustomerID (the primary key) does not have the identity key and values set so I am unable to add new customers.
I can change the settings to automatically create a new customer table value but I cannot save them back to the table because I need to drop the table before saving the values I have set.  
What do I have to do to drop the table.
thanks
Michael
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AshokSr. Software Engineer
Commented:
make sure you have exported your cust table data into text file before attempting this.

select * into tmp_hold_cust from myCust;

above will make a copy of your table with data.

then

drop table myCust;

above will drop data and table structure.

then

create table with auto id primary key column and all other columns from myCust table.

then

insert into myCust (col1, col2, col3, col4, col5) select col1, col2, col3, col4, col5 from tmp_hold_cust;

HTH
Ashok

Author

Commented:
thanks Ashok
Do you know WHY I would need to drop the table to make this change? When we moved the database I created a .bak file and sent this to the hosting company. Does the problem with this table suggest that they did not install the database properly?
Is it a simple thing for them to do? I have a fair chance of making a mistake with the steps you suggest.
michael
AshokSr. Software Engineer
Commented:
You can add primary key without dropping the table.  (it's tested on SQL Server 2008)

alter table myCust ADD Id_Cust INT IDENTITY (1,1) NOT NULL;
GO

then

alter table myCust ADD constraint pk_cust_id PRIMARY KEY(Id_Cust);
GO

HTH
Ashok
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

AshokSr. Software Engineer

Commented:
I do not think you can make existing column (CustomerID) as identity key.
Wayne BarronAuthor, Web Developer
Top Expert 2009
Commented:
See if this will help you.
If so, then you will need to perform this across all tables.
http://kb.cffcs.com/Main.asp?irid=100&Type=Article

Good Luck
Carrzkiss

Author

Commented:
Hi carrzkiss

What I am getting with my attempts and following your guide is the message:

Saving changes is not permitted.
The changes require table to be dropped and recreated.

(their version is a bit longer)

I don't know how to drop the table and (sorry Ashok) I don't fully understand  your process to drop the table.
Wayne BarronAuthor, Web Developer
Top Expert 2009

Commented:
That is weird.
I did not have that issue when I changed hosting providers, and moved my database.

When you moved your database, did your back it up and then restore it to the new location?
Or what did you do
Sr. Software Engineer
Commented:
In my process

first copy the data (with same table structure)

then create new table with one additional column
and make it a Primary Key

then bring data back to new Table (with new table structure)

Once the data is properly stored (after verifying), DROP the temporary table.

HTH
Ashok

Author

Commented:
thanks for your help. It was useful but I had to get the hosting site to drop the table. I was not confident enough to do it myself.
Wayne BarronAuthor, Web Developer
Top Expert 2009

Commented:
I just do not understand why the table had to be dropped in order to perform what I provided.
I have done it a LOT and have never had that issue.
It sounds to me that the hosting provider does not have the server properly setup.

Good Luck.
Carrzkiss
Top Expert 2012

Commented:
In order to change a column so it becomes an IDENTITY column, the table has to be rebuilt.  In other words you have to copy all the data to a temporary with the correct structure and then drop the old table and rename the temporary table to the old table name.  Of course if you do this and you have other constraints, those may have to be first dropped and then recreated at the end.

Incidentally this is exactly what SSMS does.
Wayne BarronAuthor, Web Developer
Top Expert 2009

Commented:
OK, you are right AC.
I misunderstood the IDENTITY part of it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial