Copy a Table

In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?
Copy is in the popup menu if you right click on a table but there's no PasteOriginally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

Copy is in the popup menu if you right click on a table but there's no Paste|||[QUOTE][SIZE=1]Originally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

You can do this:

SELECT *
INTO B
FROM A

Notes:
B = TABLE NAME of COPY
A = TABLE NAME of ORIGINAL|||Originally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

Copy is in the popup menu if you right click on a table but there's no Paste

You can do paste in QA (shift+Insert or Cntrl+V)|||To copy only the structure with no data (minimally logged)

select * into newtable from oldtable where 0=1

To copy only the structure with data (minimally logged)

select * into newtable from oldtable

To copy data into an existing table (Fully Logged)

insert into newtable select * from oldtable

To Copy data into an existing table where it doesn't already exist (logged)

insert into newtable select * from oldtable where PK not in (select * from newtable)

HTH|||rhigdon: don't do NOT IN, you'll kill everybody else and possibly the server as well. do left outer join on key_field where key_field is null|||I've heard that before, I'll have to test the IO difference. Have you tested it?|||Right click the database then alltasks->export data
A wizard will appear...
Select the table, put the correct source and destination and at the end choose to drop the existing destination object and not append the data.

It's easy and simple. You can also select to mantain permissions, indexes, etc

Paulo

Originally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

Copy is in the popup menu if you right click on a table but there's no Paste|||yup, many times, plus evidence presented by others, plus ms white papers.|||I'm pretty sure you are right, going to do some testing but if you could share any of those white papers I would appreciate it.|||Well, you can't use:

where PK not in (select * from newtable)

anyway.

I'm sure you meant either:
where PK not in (select PK from newtable)
or:
where not exists (select * from oldtable where old.PK = new.PK)

...both of which generate the same optimizer plan, and oddly enough they ran slightly (insignificantly even) faster than the left outer join method, which threw in an extra step for filtering. Maybe because my test table only had about 20,000 rows.

I've use all three methods and never killed a database with them, but I would be interested in reading any MS white papers as well.

blindman|||I believe you are right, Blindman. MS has been tinkering with this part of the query optimizer, and I think the query plans are largely the same. Imagine my surprise when I was about to storm into a developer's cube to have him re-write such a query, when my own re-write did nothing to help.|||Well, the left join method did use a slightly different plan than the other two, so it could potentially be more (or less) efficient for larger tables. If anybody feels the urge to experiment please post the results.

blindman|||Yes BM, I meant

where PK not in (select PK from newtable)

Well, I am a little confused as I rewrote a query that did a NOT IN query to a 1.4 million row table from a 1000 row table and it cost me a total of 3014 logical reads, when I rewrote it in a left outer join it cost me 3126 logical reads so either I am doing something wrong or the difference is insignificant.

I have searched pretty hard but have been unable to find any MS whitepapers discussing this (although I did find an interesting book in my search that I have now ordered!)|||white papers, articles...this issue goes all the way to 6.0 times where non-clustered field was challenged with IN/NOT IN and the optimizer would revert to table scan in both outer and inner queries. it is possible that in the case of the PK, unless it is non-clustered, not in and let outer join would yield the same result, or close to be the same. this is not the point. the point is that IN clause is less preferrable than exists or left outer join. any more requests for articles?|||I agree with Mcrowley in that I beleive this used to be the case but is no longer true. Not sure what you mean by "any more requests for articles" but I take it to mean you don't want to continue this discussion so guess we'll leave it at that...|||I agree with Mcrowley in that I beleive this used to be the case but is no longer true. Not sure what you mean by "any more requests for articles" but I take it to mean you don't want to continue this discussion so guess we'll leave it at that...