Just when you feel you have learned all the basics you come across something so simple.  Copy a table and its contents from one database to another is as simple as the code below:

SELECT * INTO database2.dbo.tblTest FROM database1.dbo.tblTest

Note that this is done on the same sql server and that it does not copy indexes. Anything else I should know?

Related Entries:

• Instantly Speed Up SQL Query by Using Conditional In Your Joins

| View count: 791

7 responses to “Simple T-SQL to copy table from one database to another”

1. ike Says:
   Nov 5, 2008 at 8:08 PM I think it may also not copy defaults or identity specs. I'm not certain of that though.
2. Brad Wood Says:
   Nov 6, 2008 at 1:01 AM I'm pretty sure the approach you showed assumes the columns in both tables are in the same order. If a DBA adds a new column and only adds it to one table or adds them in a different place your insert will break. I always explicitly list out the column names unless I am going a one-time copy and I KNOW the tables are identical. INSERT INTO table1 (col1, col2, col3) (SELECT col1, col2, col3 FROM table2) Also, as far as what it will copy-- it will copy all data, and no structure. This is as expected. The contents of the columns will get copied, but creating indexes, constraints, defaults, column Meta data like comments, and triggers are not data and would not be moved. You would need an alter table statement for most of those. Note however, that if the same index exists on both tables, the index will in fact be built on the second table as you insert into it.
3. John Gag Says:
   Nov 6, 2008 at 11:02 AM @Brad - That method works as well and you know the table structure. Under the method I used it creates the table for you but I am not sure what parts of the structure it does not copy.
4. Frans Says:
   Dec 11, 2008 at 12:23 PM Below you find a stored procedure to copy data from one database to another. It looks for identical table names and when found, looks for identical columnNames. When find, a query is created and data is copied USE [Tele] --the name of the database I'm in GO /****** this stored procedure will copy data from one database to another it will not copy structure. you can use it for backup purposes it will check the columns and order and create its own insert statement it also does not check for violation of keys. Frans.eilering@gmx.net 12/11/2008 06:05:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[__KopieerRecords] AS BEGIN SET NOCOUNT ON; --this SP must be stored in the destination database DECLARE @DatabaseSource nvarchar(100) DECLARE @DatabaseDestination nvarchar(100) --variables needed for creating a copy query DECLARE @StartInsertQuery nvarchar(100) DECLARE @ValuesInsertQuery nvarchar(MAX) DECLARE @ColumnNamesINsertQuery nvarchar(MAX) DECLARE @InsertQuery nvarchar(max) DECLARE @ROWCOUNT INT --the databases to be copied set @DatabaseSource = 'RegistFinal' set @DatabaseDestination = 'Tele' declare @TableName sysname declare @TableNameSource sysname declare @ColumnName sysname DECLARE @SQLString nvarchar(max) --find out which tables you have in the source database SET @SQLString=N'declare CursorListTablesFrom cursor for select TABLE_NAME FROM '+@DatabaseSource+'.INFORMATION_SCHEMA.TABLES' EXECUTE sp_executesql @SQLString --now you have a cursor containing all the table names in the database @DatabaseSource begin OPEN CursorListTablesFrom --you can walk through all the table names in this cursor fetch CursorListTablesFrom into @TableNameSource --get the first table name WHILE @@FETCH_STATUS = 0 begin print 'Working with table '+@TableNameSource --if you want to change the table names, you can do it here. --set @TableName = @TableNameSource ----The source table looks like Yentel_tabel ----and the destination table looks like tabel. It does not have this Yentel_ in front ----so just take the right piece of Yentel_tabel --set @TableName = right(@TableNameSource,len(@TableNameSource)-7) --first check if the table exists in the @DatabaseDestination (the database where this SP is stored) If EXISTS( select TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_name = ''+@TableName+'' ) BEGIN --it exists print ' table '+@TableName + ' EXISTS in destination database' --then get all the column names in table @TableNameSource --Put the column names in a cursor CursorListColumnsSource SET @SQLString=N'declare CursorListColumnsSource cursor for select COLUMN_NAME from '+@DatabaseSource+'.INFORMATION_SCHEMA.COLUMNS WHERE table_name = '''+@TableNameSource+'''' EXECUTE sp_executesql @SQLString --now I want to go through all the columns and check if they exsist in the @DatabaseDestination --If the column exists, add it to the InsertQuery SET @ColumnNamesINsertQuery='' SET @ValuesInsertQuery='' OPEN CursorListColumnsSource fetch CursorListColumnsSource into @ColumnName --get the first column name WHILE @@FETCH_STATUS = 0 begin print ' looking for column '+@ColumnName --check if the column name exists in the destination database. If exists (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''+@TableName+'' AND column_name=''+@ColumnName+'') begin print ' column '+@ColumnName +' exists' --now I want to copy the data from one column to the other --I need a query like /* insert into I_Roles (Roles) select Roles FROM [Regist].[dbo].[Yentel_I_Roles] */ --just for the comma separator IF @ColumnNamesINsertQuery='' BEGIN SET @ColumnNamesINsertQuery=@ColumnName END ELSE BEGIN SET @ColumnNamesINsertQuery=@ColumnNamesINsertQuery+','+@ColumnName END End FETCH NEXT FROM CursorListColumnsSource INTO @ColumnName end Close CursorListColumnsSource DEALLOCATE CursorListColumnsSource --create the insert query set @InsertQuery='INSERT INTO '+@TableName+'(' set @InsertQuery=@InsertQuery+@ColumnNamesINsertQuery set @InsertQuery=@InsertQuery+') select '+@ColumnNamesINsertQuery set @InsertQuery=@InsertQuery+' FROM ['+@DatabaseSource+'].[dbo].['+@TableNameSource+']' PRINT '' print 'data is being copied using ' print @InsertQuery PRINT '' EXECUTE sp_executesql @InsertQuery END FETCH NEXT FROM CursorListTablesFrom INTO @TableNameSource end end Close CursorListTablesFrom DEALLOCATE CursorListTablesFrom END
5. tri Says:
   Feb 4, 2009 at 3:45 AM how to execute this procedure
6. Changdev Says:
   Apr 10, 2009 at 6:59 AM Very well query for insert data from one database to another database.
7. Mohd Ejaz Says:
   May 24, 2009 at 6:56 AM Pls Don't Give Such Respond

Leave a Reply