• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

How to insert records from one database into a table in a different database

I've coded the Insert using fully qualified names, but still no luck.
1 Solution
In SQL SQL Server Management Studio has an tool named Import/export. by which you can export data from one table to another
see the link

Also you can do this by SQL query like below

Insert Into db1..Table1 select * from db2..Table2;

fieldb1Author Commented:
I've coded the statement with the fully qualified names, but it's throwing an error stating that String or Binary data would be truncated. This is my problem, and I don't know how to resolve it.
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Kevin CrossChief Technology OfficerCommented:
What is the structure of both tables? It sounds like you have a column that is larger in one table than in another. If I remember correctly say a TEXT column that only had 100 characters of actual data, you would get that error message going to a VARCHAR(100) column. Memory is a little fuzzy on that as I do not use TEXT columns purposefully, but recall that from past experience importing from Access Memo fields and it was something like that. Anyway, check your data types and data in general. If you know which column is the issue, you can check using LEN() or DATALENGTH() functions to see what the size of each column is. Use MAX() and you will see if you have any rows beyond the bounds of the destination column. If you don't, look for a data type changes ...
Can you send me your query and table structure So i can give you perfect solution
fieldb1Author Commented:
No, I deleted it. But it went like this:

INSERT INTO CRM.dbo.Accounts (column1, column2, ...)
SELECT (column1a., column2a, column3a, ...)
FROM Warehouse.dbo.tblCustomers

I've been working on the client side app, but I'll try to use the Import / Export command in the studio today.
if CRM and Warehouse are two database name and
Accounts is a table into CRM
tblCustomers  is a table into Warehouse

then you can write the query like below

INSERT INTO CRM..Accounts (column1, column2, ...)
SELECT column1a, column2a, column3a, ...
FROM Warehouse..tblCustomers

here ".." is present between Database name and table name
Also both Field type must be same
like datatype of "column1" is equal to "column1a"
The reason for error "String or Binary data would be truncated" is the length(size) of a column in destination table is smaller than the size of a corresponding column in source table. So please confirm that the both the tables save identical structure.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now