Copying data ... -- sql server new users

sql server new users : Copying data ...

Neven Klofutar
8/31/2006 12:00:00 AM

hi,

I hope this is the right place to post this question ...

My employer wants me to copy some data from 2 tables, put them into 2 new
temp tables, and eventually put those data back. Those 2 tables contain some
customer data and since DB is very badly designed, there is no field like
"active", so I can't make those clients inactive. I can't just create that
field and make them inactive either because that would mean that I have to
rewrite the code that is handling those clients.
First thing that comes to my mind is to copy-paste data into 2 new tables,
but the problem (as I can see it) is that SQL server doesn't retain primary
key values if I just copy-past data from one table into the other.

Can you please suggest me what should I do.

thank you, Neven

Neven Klofutar
8/31/2006 12:00:00 AM

I realized that I didn't write something clear ...

It seems that SQL Server does not retain primary key values IF that field is
set to auto increment.

[quoted text, click to view]

Neven Klofutar
8/31/2006 12:00:00 AM

I did some more testing and there is a trick I could use ...

When creating new, temp tables, primary key field MUST NOT have auto
increment turned on.
Copy the data, and when retrieving the data back to original tables, turn
auto increment field off, paste data back, and turn original table auto
increment field back to on.
Of course, this should be done when no one is working with DB.

any thoughts ?

Neven

[quoted text, click to view]

Sue Hoegemeier
8/31/2006 12:09:44 PM

Neven,
Are you doing this in SQL Server? There is no auto increment
in SQL Server. There is an identity property if that's what
you are referring to.
I think I understand what you are trying to do and you can
do it all with SQL statements which may work better. Is this
a process your boss wants you to do to scrub data? Why is it
that you need to put the data into two other tables and then
put it back? What's being done? What are you trying to
accomplish with the other tables?
Probably need to know what you are doing with the other new
two tables for us to offer you some other suggestions.
You can keep identity values if needed. When inserting the
data, you need to use
SET IDENTITY_INSERT YourTableName ON

-Sue

On Thu, 31 Aug 2006 09:10:04 +0200, "Neven Klofutar"
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn