How To: Make A Copy Of A SQL Server Database Table

I’ve been working on a couple of projects recently where I’ve had to retain legacy databases and integrate them into new websites.

In order to do this without damaging the original tables, I find it useful to make a copy of the original database table and use that for the development work. Since I do all of my bespoke CMS development on hosted Microsoft SQL Server databases, I had to hunt down a quick method to copy an existing database table into a new one.

Let’s say we have an old table (oldnews) containing news items for a website. The structure is generally sound, but we don’t want to risk any damage to the original table. The following syntax copies the data from oldnews to the new table newnews:

INSERT INTO newnews SELECT * FROM oldnews

Apparently, this will also copy data into an existing table, so if newnews already exists the data will be placed there. If the table doesn’t already exist, it will be created. At least, that’s how it worked for me!

You can even copy table data from a different (local?) database by specifying the full database path:

INSERT INTO newnews SELECT * FROM olddb.dbo.oldnews

If you’ve got any more insight into table copying techniques, drop your wisdom in the comments!

8 comments

  1. Hi All,
    It appears when it creates a new table for you, it looses it keys and indexes. You should be a ware of this.Testen on ms sql 2005
    Cheers, Harry

  2. SQL Noob hereHi Everyone.Is it possible to copy a table and save it in a USB or something? I just need the specific table.Thanks.

    • I think it might be possible to export the table to a file for importing into another database later, if that’s what you’re looking for. You probably need the SQL Server Management Console though.

  3. This is exactly what I need to do however the tables are on differnt databases and I am not sure the path to specify.
    Source is an ODBC to SAGE 2008 STOCK table
    Destination is SQL Table.
    Thanks.

  4. Just a slight twist on copying a table from one DB to the other…
    If you wish to copy the table records in at the same time you could use the following command.
    Select * INTO newnews FROM olddb.dbo.oldnews
    Hope this helps someone.Wayne

    • But Guys, be careful with PK’s and calculated fields. They will not be moved from old table to new. And you should set them up manually.

  5. Hi everyone,I have been working on this subject and the idea from Wayne Cater is good but what if you have 30 tables !? Will you do this for everyone of them?I’ve developed a code routine that tries to copy all tables from a DB to another inside the same server, I would like for it to be able to copy across servers, suggestions !? It also has the problem of not copying any keys that exist, linking the data, refered by Harry.My sugestion is, by knowing the correct procedure you just have to change the sql query and it will work. I have left you with the code ;)USE dwind_dev– Declaration of all variables that will be usedDECLARE @name nvarchar(50)DECLARE @sql nvarchar(200)DECLARE Pointless_cursor CURSOR FOR    — This area is used to allocate the data query for the temporary table    SELECT OBJECT_NAME(ID) as name    FROM sysobjects    WHERE (xtype = ‘U’)    order by XTYPE, NAME DESCOPEN Pointless_cursorFETCH NEXT FROM Pointless_cursorINTO @nameWHILE @@FETCH_STATUS = 0BEGIN    SET @sql = ‘Select * INTO dwind_qual.dbo.’+@name+’ FROM dwind_dev.dbo.’+@name    EXEC (@sql)    FETCH NEXT FROM Pointless_cursor    INTO @nameENDCLOSE Pointless_cursorDEALLOCATE Pointless_cursorPRINT (‘– ENDED –‘) Best regards,   

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s