Click here to Skip to main content
14,869,463 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]
   
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.
Santosh kumar Pithani 7-Sep-18 2:53am
   
Welcome..
give me points and Mark as a solution its will be use to others.
Member 13854008 12-Sep-18 2:20am
   
USE TargetDatabase
GO

INSERT INTO dbo.TargetTable(field1, field2, field3)
SELECT field1, field2, field3
FROM SourceDatabase.dbo.SourceTable
WHERE (some condition)
Santosh kumar Pithani 12-Sep-18 2:58am
   
good job..
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
   
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.
   
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
   
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