0

I am copying a table from one database to another, actually from one connection to another. The problem is that the key field is an identity field. In the new database in does not the identity property.

So when I try to insert into the new table I get an error because the field which used to have an identity property cannot be null. I could create a new identity field and then rename it to the original name but then the values would be out of sync with the other tables it is link to.

Thanks in advance for any help.

Bob

Bob Avallone
  • 379
  • 1
  • 10
  • 29

2 Answers2

0

The answer can be found in the second comment. I'll paste it here:

CREATE TABLE dbo.Tmp_Names ( Id int NOT NULL IDENTITY(1, 1), Name varchar(50) NULL ) ON [PRIMARY] go

SET IDENTITY_INSERT dbo.Tmp_Names ON go

IF EXISTS ( SELECT * FROM dbo.Names ) INSERT INTO dbo.Tmp_Names ( Id, Name ) SELECT Id, Name FROM dbo.Names TABLOCKX go

SET IDENTITY_INSERT dbo.Tmp_Names OFF go

Bob Avallone
  • 379
  • 1
  • 10
  • 29
-1

This will create table [db2].[dbo].[YoutableNameYouWantInDb2] in db2 with same structure as in db1 and copy all data from [db1].[dbo].[yourTableName]. 

SELECT * INTO [db2].[dbo].[YoutableNameYouWantInDb2] 
FROM [db1].[dbo].[yourTableName]
Siddique Mahsud
  • 1,453
  • 11
  • 21
  • Thanks for responding. This didn't work though. Everything is fine except that the new table key doesn't not have the identity property. This is mu problem. – Bob Avallone Mar 06 '14 at 16:34