TURNING OFF IDENTITY TEMPORARILY

I need to transfer a copy of one database into the other.

the database hasnt got that much in it at the moment but to re-enter all the data would be too big a pain.

I only have sql server 2005 express so cannot export the data into a file(or can i?)

so i can create a load of insert statements easily enough using sql....

but i need to maintain all my primary keys etc but im getting:
Cannot insert explicit value for identity column in table 'FIXTURES' when IDENTITY_INSERT is set to OFF.

when i try....

rather than have to change this setting on each table is their a gloabl setting i can change temporairly on my database using sql and then switch it back in once i have run all my inerst statements?

thanks in advance

scm0smlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
SET IDENTITY_INSERT ON
Go
--ur insert Statements
0
Aneesh RetnakaranDatabase AdministratorCommented:
I forgot to mention the tablke name

SET IDENTITY_INSERT Fixtures  ON

0
scm0smlAuthor Commented:
yeah but can this not be done for the database as one setting so i dont have to do it for each table, then turn it back off for each table?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Aneesh RetnakaranDatabase AdministratorCommented:
No, you have to do this manually for each table
0
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT 'SET IDENTITY_INSERT '+TABLE_NAME+' on '
from INformation_schema.Tables

the above will create a list which can be copied and run before you run the insert statement
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
Thanks for the points, with this I crossed 5,000,000 Expert points  :)
0
scm0smlAuthor Commented:
congrats!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.