How can I move table data from one database to another in SQL Server?

Question

I have two databases: identity2 and myDb.

Can someone help me by telling me how I can move the rows in a table with an identity column (AspNetUsers) from one database to another.

CREATE TABLE [dbo].[AspNetUsers] (
    [Id]                   INT            IDENTITY (1, 1) NOT NULL,
    [FirstName]            NVARCHAR (MAX) NULL,
    [LastName]             NVARCHAR (MAX) NULL,
    [Email]                NVARCHAR (256) NULL,
    [EmailConfirmed]       BIT            NOT NULL,
    [PasswordHash]         NVARCHAR (MAX) NULL,
    [SecurityStamp]        NVARCHAR (MAX) NULL,
    [PhoneNumber]          NVARCHAR (MAX) NULL,
    [PhoneNumberConfirmed] BIT            NOT NULL,
    [TwoFactorEnabled]     BIT            NOT NULL,
    [LockoutEndDateUtc]    DATETIME       NULL,
    [LockoutEnabled]       BIT            NOT NULL,
    [AccessFailedCount]    INT            NOT NULL,
    [UserName]             NVARCHAR (256) NOT NULL,
    [SubjectId]            INT            DEFAULT ((0)) NOT NULL,
    [SubjectIds]           VARCHAR (50)   NULL,
    [OrganizationId]       INT            DEFAULT ((0)) NOT NULL,
    [OrganizationIds]      VARCHAR (50)   NULL,
    [RoleId]               INT            DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
    ON [dbo].[AspNetUsers]([UserName] ASC);

What I want to do is to preserve the Id numbers but I don't know how to do this.

possible duplicate of [SQL Server Copying tables from one database to another](http://stackoverflow.com/questions/17582957/sql-server-copying-tables-from-one-database-to-another) — Codeek, Dec 13 '14 at 09:39

overflowed · Accepted Answer · 2014-12-13T10:46:22.367

2

Create your table like you posted in the question and then do an insert with identity insert

SET IDENTITY_INSERT AspNetUsers ON
INSERT INTO AspNetUsers (
[Id],
[FirstName],
[LastName],
[Email],
[EmailConfirmed],
[PasswordHash],
[SecurityStamp],
[PhoneNumber],
[PhoneNumberConfirmed],
[TwoFactorEnabled],
[LockoutEndDateUtc],
[LockoutEnabled],
[AccessFailedCount],
[UserName],
[SubjectId],
[SubjectIds],
[OrganizationId],
[OrganizationIds],
[RoleId]
)
SELECT * FROM myDB.dbo.AspNetUsers
SET IDENTITY_INSERT AspNetUsers OFF

edited Dec 13 '14 at 10:46

answered Dec 13 '14 at 09:45

overflowed

1,773
10
13

I followed your advice but it gives me: An explicit value for the identity column in table 'AspNetUsers' can only be specified when a column list is used and IDENTITY_INSERT is ON. – Dec 13 '14 at 10:31
yes sorry, forgot about that, have added the column list to my answer – overflowed Dec 13 '14 at 10:46

score 0 · Answer 2 · edited Dec 13 '14 at 12:34

0

select * 
into [targetdatabase].[dbo].[targettable] 
from [sourcedatabase].[dbo].[sourcetable]

edited Dec 13 '14 at 12:34

Pரதீப்

91,748
19
131
172

answered Dec 13 '14 at 09:42

Dhaval

2,341
1
13
16

How can I move table data from one database to another in SQL Server?

2 Answers2