July 9, 2008 at 8:13 am
I create SQL server 2000 databases locally, on my testing server, before publishing them to the live server, when the site is ready to go live. I do not have access to the backup and restore features on the server, so, in order to get my tables to the live server, I must use the import feature. When I do this, all of my identity fields, which had been set to yes, reset to no and it is a complete pain to have to go in to modify each table that I import. Is there a work around for this? I can't believe that Microsoft wouldn't think of this.
I have SQL server 2005 installed on my laptop, but the ISP that I host most of my sites with is still using sql server 2000. I don't believe that sql server 2005 recognizes the identity fields, either, if I remember correctly.
Any ideas?
July 9, 2008 at 9:22 am
What "import" feature are you using? If it's SSMS, the Data Import, it just creates and SSIS package to import things. It doesn't necessarily read your metadata to determine it's an identity field.
You should create the tables on the server, set identity, and then import your data.
July 9, 2008 at 9:34 am
Are you creating the tables as they're imported? Are you using the import/export wizard in SQL 2000 or SQL 2005?
In the the SQL 2000 DTS Wizard, you can choose to "Copy objects and data between SQL Server databases" which will preserve primary keys and identity properties. If you're using the SQL 2005 Wizard, you can modify the CREATE TABLE statement to add the identity property. Click on the "Edit Mappings" button after choosing a table to import then click the "Edit SQL" button on the Column Mappings screen.
Greg
July 9, 2008 at 9:38 am
This is what I'm doing:
1. create the database on the server.
2. click on Tables -> All Tasks -< Import Data.
3. selecting the source and destination.
4. selecting the tables I want to import.
So, the Tools -> Data Transformation Services -> Import Data option will maintain the identity fields?
I've actually used SQL Server 2005 only once and I'm still looking for a good resource for tutorials, so I'm not familiar with that interface at all yet.
July 9, 2008 at 9:50 am
It's the same Wizard no matter how you start it. See my advice about the Copy Objects option. It's one of three choices after you've specified the source and destination databases in the Wizard.
You can also edit the CREATE TABLE in this Wizard. Click on the elipse after selecting a table, then click on the "Edit SQL" button on the next screen.
Greg
July 9, 2008 at 9:56 am
Thanks, everyone. I'll give it a try and post back if I get stuck! Thank you!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply