I can't work out how to easily copy a table through sql enterprise manager including all data structure and the data ... I tried exporting one table then importing it into another but it wont transfer any of the actual data. I don't feel like retyping all 6000 records and I need somehow duplicate this table ... any ideas ???????
Cheers
when doing import/export go to "use a query to specify the data to transfer", it should be the middle option of 3 on the third screen.
you can then select the tables to transfer visually or write the query by hand. And all rows option should be selected by default.
hi
I did exactly what you suggested and it says transfer was a success etc .... but once I open that table that i just imported the data into it's still empty ... I don't get it ... it showed the columns correctly in the query that the wizard created but ... nothing in the new table ... I tried basically different formats as well .. even a basic txt file always the same result ... I can't think of a different way to duplicate a table any ideas ????
Cheers
My experience with import/export wizard is it's finicky... I'm lucky to just get it to work.
Is it possible we can just create a DTS package real quick to do this? I tend to have better luck with these...I use copy sql server object and just run through the steps to create it..
How about running a query like this:
SELECT *
INTO tablecopy
FROM tableoriginal
This will create a new table and also copy the rows from the original table. There's no need to use the enterprise manager either, you can run it from the query analyzer :).
Ok; that was quite nice and very efficient in effect; but what if you want only the structure??? ok you could do something like this:
SELECT * INTO tblCopy FROM tblOriginal WHERE 1=2
but, this operation, like the last one, don't copy the rules of referential integrity, nor the indexes, triggers and things like that... does anybody know how to get this using just SQL language?¡?? :eek:
Ok; that was quite nice and very efficient in effect; but what if you want only the structure??? ok you could do something like this:
SELECT * INTO tblCopy FROM tblOriginal WHERE 1=2
but, this operation, like the last one, don't copy the rules of referential integrity, nor the indexes, triggers and things like that... does anybody know how to get this using just SQL language?¡?? :eek:
If you only want the structure right click on your database name and choose Generate Script. Check out the options to include indexes, triggers, etc. and run the script in query analyzer.
Then if you want the data go to import/export and choose copy all objects with just the data
Nice... it sounds good... but can you do such a thing programatically? :) :confused:
Simlpe way to copy table without data:
SELECT TOP 1 * INTO dest FROM src
TRUNCATE TABLE dest
Maybe it is not optimal, but it works well!
It worked, thank you.
Liz Wiz
How about running a query like this:
SELECT *
INTO tablecopy
FROM tableoriginal
This will create a new table and also copy the rows from the original table. There's no need to use the enterprise manager either, you can run it from the query analyzer :).