Please try this:

SELECT *

INTO dbo.newtable

FROM dbo.oldtable_with_identity

UNION ALL

SELECT TOP (1) *

FROM dbo.oldtable_with_identity

WHERE 1 = 0

The identity property should be left off the column in the new table :-).

CELKO (12/27/2012)

---

The term for this is "union compatible" and it...

You could also add an "in-use" column to each row which is set when someone accesses it, and unset when they close it and/or after a certain period of time...

CELKO (12/31/2012)

---

Yep, you can. And thanks to identity theft this is becoming more...

MS provides "sp_getapplock", and the corresponding "sp_releaseapplock", to help you do this. With them, you can leverage SQL's already-built-and-tested locking mechanisms for your own logical locking. You also...

Jeff Moden (12/29/2012)

---

Wouldn't truncating a table in QA violate the idea of keeping all 3 environments in sync? Presumably, keeping all 3 enviroments in sync really means keeping Dev...

John Mitchell-245523 (12/31/2012)

---

That's how we...

Jeff Moden (12/28/2012)

---

While it is true that a...

Jeff Moden (12/28/2012)

---

wannalearn (12/27/2012)

---

Thank you all for your responses.

We can not use Log Shipping because Our databases do not store transactions. We load data everynight from different sources ....

CELKO (12/28/2012)

---

I have been at this for 25+ years now and I...

CELKO (12/28/2012)

For a reasonable level of trust, I use the email address + password, like every website on earth. Seems to work well enough for Paypal, Amazon, et al. People...

Sean Lange (12/28/2012)

---

TRY/CATCH sounds like the exact thing you need.

begin transaction

begin try

-- PART 1 --

Insert into first table

Delete from first table

-- PART 2 --

Insert into a second table

Update my second...

You could use an AFTER INSERT trigger to insure that the parent id got set to the identity column values ... but you still couldn't define the parent id as...

I would never try to re-run the prod processes to get the data for other environments. That would be a nightmare to keep clean.

One possibility is to mix differential...

As noted, heaps are great for "work" / "staging" tables.

Also, I guess in theory a heap could be more efficient for certain types of tables with multiple nonclust indexes, particularly...