Basically I have a two databases on SQL Server 2005.
I want to take the table data from one database and copy it to another database's table.
I tried this:
SELECT * INTO dbo.DB1.TempTable FROM dbo.DB2.TempTable This didn't work.
I don't want to use a restore to avoid data loss...
Any ideas?
SELECT ... INTO creates a new table. You'll need to use INSERT. Also, you have the database and owner names reversed.
INSERT INTO DB1.dbo.TempTable SELECT * FROM DB2.dbo.TempTable 
SELECT * INTO requires that the destination table not exist.
Try this.
INSERT INTO db1.dbo.TempTable (List of columns here) SELECT (Same list of columns here) FROM db2.dbo.TempTable It's db1.dbo.TempTable and db2.dbo.TempTable
The four-part naming scheme goes:
ServerName.DatabaseName.Schema.Object
Hard to say without any idea what you mean by "it didn't work." There are a whole lot of things that can go wrong and any advice we give in troubleshooting one of those paths may lead you further and further from finding a solution, which may be really simple.
Here's a something I would look for though,
Identity Insert must be on on the table you are importing into if that table contains an identity field and you are manually supplying it. Identity Insert can also only be enabled for 1 table at a time in a database, so you must remember to enable it for the table, then disable it immediately after you are done importing.
Also, try listing out all your fields
INSERT INTO db1.user.MyTable (Col1, Col2, Col3) SELECT Col1, COl2, Col3 FROM db2.user.MyTable We can three part naming like database_name..object_name
The below query will create the table into our database(with out constraints)
SELECT * INTO DestinationDB..MyDestinationTable FROM SourceDB..MySourceTable Alternatively you could:
INSERT INTO DestinationDB..MyDestinationTable SELECT * FROM SourceDB..MySourceTable If your destination table exists and is empty.
Don't forget to insert SET IDENTITY_INSERT MobileApplication1 ON to the top, else you will get an error. This is for SQL Server
SET IDENTITY_INSERT MOB.MobileApplication1 ON INSERT INTO [SERVER1].DB.MOB.MobileApplication1 m (m.MobileApplicationDetailId, m.MobilePlatformId) SELECT ma.MobileApplicationId, ma.MobilePlatformId FROM [SERVER2].DB.MOB.MobileApplication2 ma Im prefer this one.
INSERT INTO 'DB_NAME' (SELECT * from 'DB_NAME@DB_LINK') MINUS (SELECT * FROM 'DB_NAME'); Which means will insert whatsoever that not included on DB_NAME but included at DB_NAME@DB_LINK. Hope this help.
INSERT INTO DB1.dbo.TempTable SELECT * FROM DB2.dbo.TempTable If we use this query it will return Primary key error.... So better to choose which columns need to be moved, like
INSERT INTO db1.dbo.TempTable // (List of columns here) SELECT (Same list of columns here) FROM db2.dbo.TempTable Try this
INSERT INTO dbo.DB1.TempTable (COLUMNS) SELECT COLUMNS_IN_SAME_ORDER FROM dbo.DB2.TempTable This will only fail if an item in dbo.DB2.TempTable is in already in dbo.DB1.TempTable.
This works successfully.
INSERT INTO DestinationDB.dbo.DestinationTable (col1,col1) SELECT Src-col1,Src-col2 FROM SourceDB.dbo.SourceTable You can copy one table to other db table even with some additional columns.
insert into [SchoolDb1].[dbo].Student(Col1, Col2,Col3, CreationTime, IsDeleted) select Col1, Col2,Col3,,getdate(),0 from [SchoolDb2].[dbo].Student These are additional columns: (CreationTime is datatime and IsDeleted is boolean)
select * from DBA1.TABLENAMEA; create table TABLENAMEA as (select * from DBA1.TABLENAMEA); These manual way provides more flexibility, but at the same time, works for table whose size is smaller to few thousands.
Do select * from <table name> from DB, once whole table is displayed, scroll till it's bottom.
Right click and do Export table as Insert statement, provide the name of the destination table and export the table as .sql file.
Use any text editor to further do regular find and replace operation to include more column names etc.
Use the INSERT statement in destination DB.
