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 — Codeek
– Codeek, Commented Dec 13, 2014 at 9:39

overflowed · Accepted Answer · 2014-12-13 10:46:22Z

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, 2014 at 10:46

answered Dec 13, 2014 at 9:45

overflowed

1,83812 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3568783 Over a year ago

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.

overflowed Over a year ago

yes sorry, forgot about that, have added the column list to my answer

Pரதீப் · Accepted Answer · 2014-12-13 12:34:55Z

0

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

edited Dec 13, 2014 at 12:34

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

answered Dec 13, 2014 at 9:42

Dhaval

2,3791 gold badge15 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related