How to insert table records into another database table

Question

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 6-Sep-18 2:04am

Member 13854008

Updated 12-Sep-18 0:04am

Santosh kumar Pithani

v4

Add a Solution

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.

Patrice T 6-Sep-18 14:12pm

Just apply solution!
Asking questions is a skill[^]
Some guidelines for posting questions in the forums[^]

4 solutions

Solution 3

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.

Posted 11-Sep-18 23:05pm

chandraprakashkabra

Updated 12-Sep-18 1:16am

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!

Solution 4

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

Posted 12-Sep-18 0:04am

Member 13978939

Comments

CHill60 12-Sep-18 8:02am

This adds nothing that has not already been posted and accepted

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Santosh kumar Pithani · Accepted Answer · 2018-09-06T02:27:00

Solution 1

SQL

INSERT INTO db1.[dbo].[insertfam]
select 
--Select ALL Columns except identity column--
from  db2.[dbo].[insertfam]

Posted 6-Sep-18 2:27am

Santosh kumar Pithani

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..

Member 13854008 · Accepted Answer · 2018-09-11T20:20:00

Solution 2

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

Posted 11-Sep-18 20:20pm

Member 13854008

Updated 11-Sep-18 20:57pm

v3