Solved

table1 in database1 to table1 in database2

Posted on 2010-08-18
12
440 Views
Last Modified: 2012-08-13
i have table1 in database2

I want to copy it to
table1 in database1


or export all the values stored in it
and insert into table1 in database1
0
Comment
Question by:rgb192
  • 6
  • 5
12 Comments
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 33466332
presuming they are on the same serverinsert into database1.dbo.table1 select * from database2.dbo.table1
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33466532
The above query should work. If you have different schema than 'dbo' change it to yours.

-- All fields
INSERT INTO database1.yourschema.table1 SELECT * FROM database2.yourschema.table1

-- Custom fields
INSERT INTO database1.yourschema.table1 (Col1, Col2) SELECT Col1, Col2 FROM database2.yourschema.table1

Raj


Raj
0
 

Author Comment

by:rgb192
ID: 33467327
INSERT INTO ow1222.dbo.productimagespd SELECT * FROM ow1222b.dbo.productimagespd

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33467370
This is because of the difference in data length of some columns. If you are trying to insert / update values into a columns that have more length that the column allows, this error happens.

Compare the datalength of the two columns and change the length for the column which have lesser than the other.

Raj
0
 

Author Comment

by:rgb192
ID: 33467408
USE [ow1222]
GO
/****** Object:  Table [dbo].[productimagespd]    Script Date: 08/18/2010 13:21:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[productimagespd](
      [productimagesid] [int] IDENTITY(1,1) NOT NULL,
      [productid] [int] NOT NULL,
      [name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [place] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [manu] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33467434
Compare this script for both tables

Raj
0
 

Author Comment

by:rgb192
ID: 33467698
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'ow1222.dbo.productimagespd' can only be specified when a column list is used and IDENTITY_INSERT is ON.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33467758
Your table have primary key field with IDENTITY property.

Modify your Select query by specifying the columns excluding the primary key.

Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33467826
Something like

INSERT INTO database1.[dbo].[productimagespd] (productid, name, place, manu)
   SELECT productid, name, place, manu FROM database2.[dbo].[productimagespd]

This excludes primary key / identity column
Raj

0
 

Author Comment

by:rgb192
ID: 33467893
INSERT INTO ow1222.dbo.productimagespd (productimagesid, productid,name,place,manu) SELECT productimagesid, productid,name,place,manu FROM ow1222b.dbo.productimagespd


Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'productimagespd' when IDENTITY_INSERT is set to OFF.
0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 500 total points
ID: 33467972
Exclude primary key / identity column - check my query I posted above.

Since 'productimagesid' is the primary key with IDENTITY property enabled, this insertion may cause duplicate values - that's causing this error.

Raj
0
 

Author Closing Comment

by:rgb192
ID: 33468115
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 61
Strange msg in the SSMS pane 13 55
SQL Server 2005 - Comparing Fields' Contents 6 32
How can i use WITH CTE for checking exist value? 3 30
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now