Need to copy data from one table to another

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

General SQL Server Forums

New to SQL Server Programming

Need to copy data from one table to another

Author

Topic

biogem
Starting Member

13 Posts

Posted - 2009-10-05 : 10:50:14

I'm trying to copy a table from one db to another.
The tables have the same name. I get the following error message: 'Msg 2714, Level 16, State 6, Line 1
There is already an object named 'Tenant' in the database.' How do i get around this, I need the table name to remain the same?

SELECT * INTO DemoCTS.dbo.Tenant
FROM [CustomerTrackingSystem].[dbo].[Tenant]

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-05 : 10:53:38

[code]
insert into DemoCTS.dbo.Tenant( < column names > )
select < column names >
from [CustomerTrackingSystem].[dbo].[Tenant]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-05 : 10:57:44

[code]
USE DemoCTS
GO

IF OBJECT_ID('Tenant_bkp') IS NULL
sp_rename 'Tenant', 'Tenant_bkp'
GO

IF OBJECT_ID('Tenant') IS NULL
SELECT *
INTO [Tenant]
FROM [CustomerTrackingSystem].[dbo].[Tenant]
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-05 : 10:58:23

quote:
Originally posted by khtan

insert into DemoCTS.dbo.Tenant( < column names > )
select < column names >
from [CustomerTrackingSystem].[dbo].[Tenant]

KH
[spoiler]Time is always against us[/spoiler]

Simba...look closer.....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-05 : 11:04:49

Simba ?

KH
[spoiler]Time is always against us[/spoiler]

biogem
Starting Member

13 Posts

Posted - 2009-10-05 : 11:15:36

Now I'm getting a different error. How do I ignore the timestamp?

error: Msg 273, Level 16, State 1, Line 1
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

syntax used:
INSERT INTO DemoCTS.dbo.Tenant
([TenantID]
,[TenantName]
,[Message]
,[Non-Active]
,[LastActivity]
,[PriFax]
,[WebAddress]
,[RemoteHands]
,[Site]
,[Timestamp]
,[TenantType]
,[CreditRestriction]
,[MasterServicesAgreement]
,[PORequired]
,[RestrictedOrDirect]
,[GNISupport]
,[RemoteHandsRestriction]
,[PhysicalAccess]
,[AccountManager]
,[MinCrossConnect]
,[DisableAccess]
,[BillBCMForward]
,[OrderOnlyRestriction]
,[AccountCollectionStatusID]
,[AccountingContact]
,[NoEquipmentRemovals]
,[ShowOnCarrierList]
)
SELECT
[TenantID]
,[TenantName]
,[Message]
,[Non-Active]
,[LastActivity]
,[PriFax]
,[WebAddress]
,[RemoteHands]
,[Site]
,[Timestamp]
,[TenantType]
,[CreditRestriction]
,[MasterServicesAgreement]
,[PORequired]
,[RestrictedOrDirect]
,[GNISupport]
,[RemoteHandsRestriction]
,[PhysicalAccess]
,[AccountManager]
,[MinCrossConnect]
,[DisableAccess]
,[BillBCMForward]
,[OrderOnlyRestriction]
,[AccountCollectionStatusID]
,[AccountingContact]
,[NoEquipmentRemovals]
,[ShowOnCarrierList]
FROM [CustomerTrackingSystem].[dbo].[Tenant]
GO

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-05 : 11:26:40

post the DDL for Tenant....which column is timestamp

Anyone out there really ever use timestamp?

Simba...a character in a Disney Movie..."the Lion King"

Do you need to retain the timestamp values?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources