Click here to Skip to main content
15,943,477 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I want to copy and paste a table from one database to another.
one database(db1) has all details about data but db2 has no data .Db2 has all fields and table as of db1 but not datas .so i want to copy and paste that one table data from db1 to db2. so i make table as a script ( right click the table- script- create to - new query edit window).copy that script and past in db2 but error came

What I have tried:

please help.
Posted
Updated 12-Sep-18 0:04am
v4
Comments
Santosh kumar Pithani 6-Sep-18 8:32am    
you know how to alter existed table?
Member 13854008 6-Sep-18 22:52pm    
No sir, that's why some error shown there
Member 13854008 6-Sep-18 22:55pm    
No sir that's why some errors shown there.

SQL
INSERT INTO db1.[dbo].[insertfam]
select 
--Select ALL Columns except identity column--
from  db2.[dbo].[insertfam]
 
Share this answer
 
Comments
Member 13854008 6-Sep-18 22:54pm    
sir, I want to get the datas also that enter inthe table not only the fields.
Santosh kumar Pithani 6-Sep-18 23:56pm    
datas means records;Have you implemented solution query?put one example that what you expecting.
Member 13854008 7-Sep-18 1:22am    
sir , data means records that enter in table. actually i want to transfer the data from one table(db1) to another existing table in db2
Santosh kumar Pithani 7-Sep-18 1:33am    
i think you may not understood simple solution, i give right solution to you problem.Execute above solution and let me know if any error!
EX:INSERT INTO Dbname.schemaname.tablename(columnnames except identity columns)
select 'columnnames except identity columns ' from Dbname.schemaname.tablename
Member 13854008 7-Sep-18 2:41am    
tanku very much sir ,i got it.
SQL
USE
  TargetDatabase
GO

INSERT INTO dbo.TargetTable(field1, field2, field3)
   SELECT field1, field2, field3
     FROM SourceDatabase.dbo.SourceTable  WHERE (some condition)

this will transfer the details from one to another
 
Share this answer
 
v3
If table is not exist in database then use this solution

SELECT * INTO TargetDB.dbo.TableName   
    FROM [SourceDb].[dbo].[TableName]


By using this query you can do.
Here you have to replace TargetDb and SourceDb and TableName according to your database name.

Else table is exists in database then use this one
INSERT INTO [TargetDB].[dbo].[Department]
([Title],[Credits])
SELECT 
[Title],[Credits]
FROM [SourceDb].[dbo].[Department]

Let me know if you still face any issue.
 
Share this answer
 
v2
Comments
Santosh kumar Pithani 12-Sep-18 5:19am    
Hello ,is it possible to insert records in existed table "TargetDB.dbo.TableName " by your solution?
chandraprakashkabra 12-Sep-18 5:41am    
Hi Santosh kumar
for the existed table it will not work.
For that this query will work.

INSERT INTO [TargetDB].[dbo].[Department]
([Title],[Credits])
SELECT
[Title],[Credits]
FROM [SourceDb].[dbo].[Department]
Santosh kumar Pithani 12-Sep-18 6:34am    
yes, now your solution is correct so improve your solution with new query!
insert into Db2.dbo.tablename
--(all columns with comma separator except identity column)
select
--select all columns with comma separator except identity column
from Db1.dbo.tablename
 
Share this answer
 
Comments
CHill60 12-Sep-18 8:02am    
This adds nothing that has not already been posted and accepted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900