Importing tables - SQL 2008 R2

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

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Importing tables - SQL 2008 R2

Author

Topic

Awirer
Starting Member

3 Posts

Posted - 2012-05-18 : 11:13:26

Hello,

I'm new to this forum, and 'new' to SQL in general. A small introduction behind the reason why I'm looking for help:

I'm currently running a private server, mostly for fun and as a small project, due to being interested in gaming in general. This private server is using a SQL Database + ODBC. But this is not the reason why I'm looking for help.

I've currently been working on a new database, with new items/files/regions etc and it's ready HOWEVER I got some big issues.

I have about 16500 accounts + characters to transfer, the accounts side is fine + characters, but when it comes to their gear (items) and similar I got BIG BIG BIG issues.

I'm trying to transfer the following tables from my old database:

_Items
_Inventory
_InventoryForAvatar
_InventoryForLinkedStorage
_ItemPool
_ItemQuotation
_InvCOS
_LatestItemSerials

However, the keys/constraints for the tables does not follow with during transfer, which is needed.

I've tried dropping table/re-create but getting errors
I've tried generating scripts by right clicking on database using "schema" and "Schema and data" (Does not work for Items/Inventory due to big tables)
i've tried re-creating keys/constraints without success..

Now I don't know what to do...

Screenshots:

http://188.165.198.91/pics/SQL1.png
http://188.165.198.91/pics/SQL3.png

When I try to re-create some of the keys it says they already excists even though I dropped tables etc...

Awirer
Starting Member

3 Posts

Posted - 2012-05-18 : 19:03:09

#Update:

If i use the following query:

SET IDENTITY_INSERT [SRO_VT_SHARD].[dbo].[_Items] ON
INSERT INTO [SRO_VT_SHARD].[dbo].[_Items] (ID64, RefItemID, OptLevel, Variance, Data, CreaterName, MagParamNum, MagParam1, MagParam2, MagParam3,MagParam4,MagParam5,MagParam6,MagParam7,MagParam8,MagParam9,MagParam10,MagParam11,MagParam12,Serial64)
SELECT ID64, RefItemID, OptLevel, Variance, Data, CreaterName, MagParamNum, MagParam1, MagParam2, MagParam3,MagParam4,MagParam5,MagParam6,MagParam7,MagParam8,MagParam9,MagParam10,MagParam11,MagParam12,Serial64
FROM [SRO_VT_SHARD_OLD].[dbo].[_Items]
SET IDENTITY_INSERT [SRO_VT_SHARD].[dbo].[_Items] OFF

I get the following errors:

quote:
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__Itemss'. Cannot insert duplicate key in object 'dbo._Items'.
The statement has been terminated.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-05-18 : 19:57:48

SELECT COUNT(*), ID64
FROM [SRO_VT_SHARD_OLD].[dbo].[_Items]
GROUP BY ID64
HAVING COUNT(*) > 1

if this returns any values then you have dups on the [SRO_VT_SHARD_OLD].[dbo].[_Items] table

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Awirer
Starting Member

3 Posts

Posted - 2012-05-19 : 09:09:49

Thanks yosiasz, thanks a lot!

I solved this problem last night though.

I generated a script, using schema only, and selceted full table and just copied the data inside it instead, took much longer time, but it solved the issue atleast. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources