Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 inserting data into new table

Author  Topic 

gagani
Posting Yak Master

112 Posts

Posted - 2013-01-15 : 11:58:05
I have the Table definition as follows

GO
/****** Object: Table [dbo].[PRICING_NEW] Script Date: 01/15/2013 16:53:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PRICING_NEW](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FK_PRODUCT_ID] [int] NOT NULL,
[FK_TYPE_ID] [int] NOT NULL,
[FK_SUBTYPE_ID] [char](10) NULL,
[COST] [money] NOT NULL,
[color_type] [char](1) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

I have added rows to that table; deleted some rows later on and added few other rows.

I want to copy the data of this table into another table of same name of different database.
when I tried to insert through the following query:

insert into pricing_new select *from "testsrv1".database1.dbo.pricing_new


it has come up with identity_insert error and asked to add the columns.

could anyone please let me know what is to be done so that the table i am copying into
should behave in the same way as the original one.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-15 : 12:05:52
You have two choices:
If you want to keep the same ID's, do the following:
SET IDENTITY_INSERT pricing_new ON

INSERT INTO pricing_new
(ID,FK_PRODUCT_ID,FK_TYPE_ID,FK_SUBTYPE_ID,COST,color_type)
SELECT
ID,FK_PRODUCT_ID,FK_TYPE_ID,FK_SUBTYPE_ID,COST,color_type
FROM
database1.dbo.pricing_new
If you want to automatically assign new id values in the pricing_new table, do this

INSERT INTO pricing_new
(FK_PRODUCT_ID,FK_TYPE_ID,FK_SUBTYPE_ID,COST,color_type)
SELECT
FK_PRODUCT_ID,FK_TYPE_ID,FK_SUBTYPE_ID,COST,color_type
FROM
database1.dbo.pricing_new
In either case, you have to explicitly list the columns.
Go to Top of Page
   

- Advertisement -