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
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Data copy into same database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arv_rehal1381
Starting Member

4 Posts

Posted - 09/28/2013 :  09:13:22  Show Profile  Reply with Quote
I was trying to copy data from two tables to one table in same database by using Import Data functionality under TASK. I am using this view

SELECT dbo.AnnotationBase.ModifiedBy AS PrincipalId, dbo.AnnotationBase.AnnotationId AS ObjectId, 5 AS ObjectTypeCode, 8 AS PrincipalTypeCode, 0 AS AccessRightsMask,
dbo.AnnotationBase.ModifiedOn AS ChangedOn, NEWID() AS PrincipalObjectAccessId, 135069719 AS InheritedAccessRightsMask
FROM dbo.AnnotationBase LEFT OUTER JOIN
dbo.SystemUserBase ON dbo.AnnotationBase.ModifiedBy = dbo.SystemUserBase.SystemUserId


But when I am running this it is giving me this error

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)


Thanks

tkizer
Almighty SQL Goddess

USA
36949 Posts

Posted - 09/28/2013 :  15:09:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
Do this instead:

SELECT dbo.AnnotationBase.ModifiedBy AS PrincipalId, dbo.AnnotationBase.AnnotationId AS ObjectId, 5 AS ObjectTypeCode, 8 AS PrincipalTypeCode, 0 AS AccessRightsMask,
dbo.AnnotationBase.ModifiedOn AS ChangedOn, NEWID() AS PrincipalObjectAccessId, 135069719 AS InheritedAccessRightsMask
INTO NewTableName
FROM dbo.AnnotationBase
LEFT OUTER JOIN dbo.SystemUserBase ON dbo.AnnotationBase.ModifiedBy = dbo.SystemUserBase.SystemUserId


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

arv_rehal1381
Starting Member

4 Posts

Posted - 09/28/2013 :  15:21:19  Show Profile  Reply with Quote
Hi,

Thanks for your reply, Yes, your query worked it can create new table with same column and data but what is the problem in using existing table.

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36949 Posts

Posted - 09/30/2013 :  11:04:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
If you want to copy data to an existing table, then use this instead:

INSERT INTO ExistingTable
SELECT dbo.AnnotationBase.ModifiedBy AS PrincipalId, dbo.AnnotationBase.AnnotationId AS ObjectId, 5 AS ObjectTypeCode, 8 AS PrincipalTypeCode, 0 AS AccessRightsMask,
dbo.AnnotationBase.ModifiedOn AS ChangedOn, NEWID() AS PrincipalObjectAccessId, 135069719 AS InheritedAccessRightsMask
FROM dbo.AnnotationBase
LEFT OUTER JOIN dbo.SystemUserBase ON dbo.AnnotationBase.ModifiedBy = dbo.SystemUserBase.SystemUserId

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
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.08 seconds. Powered By: Snitz Forums 2000