Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

transfer data from one table to another

Posted on 2010-08-29
16
Medium Priority
?
292 Views
Last Modified: 2012-05-10

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
Comment
Question by:zolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 2
  • +2
16 Comments
 
LVL 10

Expert Comment

by:TiberiuGal
ID: 33556114
Hi,
you must also have a table Permission which should be copied to Test prior to PermissionAssignment.

0
 

Author Comment

by:zolf
ID: 33556132

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

Author Comment

by:zolf
ID: 33556247

please help
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 10

Accepted Solution

by:
TiberiuGal earned 800 total points
ID: 33556254
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
 

Expert Comment

by:ahmedoso
ID: 33556304
You should first Stop or remove the forign key constraint as TiberiuGal said
Or just use DTS wizard
0
 

Author Comment

by:zolf
ID: 33556313

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
 

Author Comment

by:zolf
ID: 33556315

>>Or just use DTS wizard
how do i do this
0
 

Author Comment

by:zolf
ID: 33556318

>>3. modify the db references in your code and execute the query
i did no tunderstnad this step
0
 

Assisted Solution

by:ahmedoso
ahmedoso earned 200 total points
ID: 33556323
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
 

Author Comment

by:zolf
ID: 33556326

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
 
LVL 10

Expert Comment

by:TiberiuGal
ID: 33556329
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
 

Author Comment

by:zolf
ID: 33556343

i did what you mentioned and in the query i changed the db name
0
 
LVL 10

Expert Comment

by:TiberiuGal
ID: 33556350
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
 

Author Comment

by:zolf
ID: 33556360

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
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 800 total points
ID: 33556467
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
 
LVL 7

Assisted Solution

by:rashmi_vaghela
rashmi_vaghela earned 200 total points
ID: 33556470
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

610 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