rgb192
asked on
table1 in database1 to table1 in database2
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
I want to copy it to
table1 in database1
or export all the values stored in it
and insert into table1 in database1
presuming they are on the same serverinsert into database1.dbo.table1 select * from database2.dbo.table1
The above query should work. If you have different schema than 'dbo' change it to yours.
-- All fields
INSERT INTO database1.yourschema.table 1 SELECT * FROM database2.yourschema.table 1
-- Custom fields
INSERT INTO database1.yourschema.table 1 (Col1, Col2) SELECT Col1, Col2 FROM database2.yourschema.table 1
Raj
Raj
-- All fields
INSERT INTO database1.yourschema.table
-- Custom fields
INSERT INTO database1.yourschema.table
Raj
Raj
ASKER
INSERT INTO ow1222.dbo.productimagespd SELECT * FROM ow1222b.dbo.productimagesp d
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
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
Compare the datalength of the two columns and change the length for the column which have lesser than the other.
Raj
ASKER
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
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_
[place] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_
[manu] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Compare this script for both tables
Raj
Raj
ASKER
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'ow1222.dbo.productimagesp d' can only be specified when a column list is used and IDENTITY_INSERT is ON.
An explicit value for the identity column in table 'ow1222.dbo.productimagesp
Your table have primary key field with IDENTITY property.
Modify your Select query by specifying the columns excluding the primary key.
Raj
Modify your Select query by specifying the columns excluding the primary key.
Raj
Something like
INSERT INTO database1.[dbo].[productim agespd] (productid, name, place, manu)
SELECT productid, name, place, manu FROM database2.[dbo].[productim agespd]
This excludes primary key / identity column
Raj
INSERT INTO database1.[dbo].[productim
SELECT productid, name, place, manu FROM database2.[dbo].[productim
This excludes primary key / identity column
Raj
ASKER
INSERT INTO ow1222.dbo.productimagespd (productimagesid, productid,name,place,manu) SELECT productimagesid, productid,name,place,manu FROM ow1222b.dbo.productimagesp d
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks