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

transfer data from one table to another


Hello there,

i am trying to trasnfer datafrom one table to another. with the help of EE cyberkiwi. but now when i try to trasnfer anther table i get a new error

Msg 547, Level 16, State 0, Line 9
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ID_PERMISSION_PRMSSNASSGNMNT". The conflict occurred in database "test", table "dbo.Permission", column 'id'.
The statement has been terminated.
INSERT [test].[dbo].[PermissionAssignment]
           ([created]
           ,[lastUpdated]
           ,[createdBy]
           ,[updatedBy]
           ,[groupId]
           ,[permissionId])
SELECT [created]
      ,[lastUpdated]
      ,[createdBy]
      ,[updatedBy]
      ,[groupId]
      ,[permissionId]
  FROM [pohtest].[dbo].[PermissionAssignment]

Open in new window

0
zolf
Asked:
zolf
  • 8
  • 4
  • 2
  • +2
4 Solutions
 
TiberiuGalCommented:
Hi,
you must also have a table Permission which should be copied to Test prior to PermissionAssignment.

0
 
zolfAuthor Commented:

i did that,now when i try to trasnfer this table i get this error.please help
0
 
zolfAuthor Commented:

please help
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
TiberiuGalCommented:
Try to recreate your foreign keys:
1 . Open Mangement Studio and navigate to your test .. table select keys , right click on FK_ID_PERMISSION_PRMSSNASSGNMNT and delete it
2. Now go to your pohtest .. tables and select the key right click and chose script constraint as create to / new query
3. modify the db references in your code and execute the query
4. try inserting data again
0
 
ahmedosoCommented:
You should first Stop or remove the forign key constraint as TiberiuGal said
Or just use DTS wizard
0
 
zolfAuthor Commented:

after i finish the step 1 and 2.should i run the query to trasnfer the data and when should i run the query in step 2 you mentioned.
0
 
zolfAuthor Commented:

>>Or just use DTS wizard
how do i do this
0
 
zolfAuthor Commented:

>>3. modify the db references in your code and execute the query
i did no tunderstnad this step
0
 
ahmedosoCommented:
Right click the Database which has the data you want , choose Export Data from the menu , follow the wizard
it will ask you for the destination , tables , and so on
0
 
zolfAuthor Commented:

i deleted the FK constrain from the test table and then i wan able to run the trasnfer query,but now i cannot create those FH contraint i get error

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_ID_PERMISSION_PRMSSNASSGNMNT". The conflict occurred in database "test", table "dbo.Permission", column 'id'.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_ID_PERMISSION_PRMSSNASSGNMNT' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.


USE [test]
GO
ALTER TABLE [dbo].[PermissionAssignment]  WITH CHECK ADD  CONSTRAINT [FK_ID_PERMISSION_PRMSSNASSGNMNT] FOREIGN KEY([permissionId])
REFERENCES [dbo].[Permission] ([id])
GO
ALTER TABLE [dbo].[PermissionAssignment] CHECK CONSTRAINT [FK_ID_PERMISSION_PRMSSNASSGNMNT]

Open in new window

0
 
TiberiuGalCommented:
your generated sql query for your foreign key should look something like

[use pohtest]

alter table [photest].[dbo].[PermissionAssignment] ...


you should just replace pohtest with test
I'd say you recreate your foreign keys before you transer data, but you could just drop your FK, transefer de data and recreate FK ...
0
 
zolfAuthor Commented:

i did what you mentioned and in the query i changed the db name
0
 
TiberiuGalCommented:
drop all your data from test.dbo.Permission and retransfer the data, I suspect you duplicated the data in that table.

truncate table test.dbo.Permission;
go

set identity_insert test.dbo.Permission on;
go
insert into test.dbo.Permission
select * from photest.dbo.Permission
go
set identity_insert test.dbo.Permission off;

now try to transfer PermissionAssignment again

0
 
zolfAuthor Commented:

i get this error when i try to run the this query

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'test.dbo.Permission' can only be specified when a column list is used and IDENTITY_INSERT is ON.


set identity_insert test.dbo.Permission on;
go
insert into test.dbo.Permission
select * from photest.dbo.Permission
go
set identity_insert test.dbo.Permission off;
0
 
QlemoC++ DeveloperCommented:
As the message says, you need to specify the columns, at least in the INSERT, best in both INSERT and SELECT. This is a special requirement of IDENTITY_INSERT to make sure you are not exchanging columns accidentally.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now