SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 inserting data into new table		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gagani
Yak Posting Veteran

84 Posts
Posted - 01/15/2013 :  11:58:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1511 Posts
Posted - 01/15/2013 :  12:05:52  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000