CODEWITHSUNDEEP
sqlsql-serversql-server-2012
BorHunter
BorHunter

Reputation: 923

How to split one table into two

I'm working in sql server 2012 where I have table Students which contain fields: StudentId, Name, City, Username, Password.

I need split table Students into next tables: Students and Users. And table

Users must be have next fields: UserId, Username, Password.

And table Students must be have: StudentId, Name, City, UserId.

My question is: how can I do this by code?

P.S.: I'm new in sql. I know maybe this is dublicate, but I don't understand how to do this.

Upvotes: 0

Views: 579

Answers (3)

Abhishek Chaudhary
Abhishek Chaudhary

Reputation: 807

You can do it in the following steps:

Create Users table. Since you don't have UserID, you can generate it using an identity column.

CREATE TABLE Users (
UserID bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,
Username nvarchar(255),
Password nvarchar(255)
)

Insert data into Users table

INSERT INTO Users (
Username,
Password
)
SELECT
Username,
Password
FROM
[Existing table]

Now, create Students table

CREATE TABLE Students (
StudentID bigint PRIMARY KEY NOT NULL,
Name nvarchar(255),
City nvarchar(255),
UserID bigint FOREIGN KEY REFERENCES User(UserID)
)

and insert data into Students table

INSERT INTO Students (
StudentID,
Name,
City,
UserID
)
SELECT
StudentID,
Name,
City,
UserID
FROM
[Existing table] A INNER JOIN [Users] B
ON A.Username = B.Username

If the original table name is Students, create the above table as Students_New. Then, after creation and insertion, you can use the below script:

DROP TABLE dbo.Students
GO;

EXEC sp_rename 'Students_New', 'Students'

Upvotes: 1

M.Ali
M.Ali

Reputation: 69594

Populate Students Table 1st , since you already have Students table in your table database you cannot have another table called students, so call it students_new inititally.

Add UserID Identity COlumn to yout Original Table

ALTER TABLE Students
ADD UserID INT IDENTITY(1,1)

Students Table

SELECT  StudentId, Name, City, UserId INTO Students_New
FROM Students

Users Table

SELECT UserId, Username, Password INTO Users
FROM Students

Drop original Students Table and Rename New Table

Now you can Drop the actual Students Table and Rename the Students_New Table to Students

DROP TABLE Students
GO

EXECUTE sp_rename 'Students_New','Students';
GO

Upvotes: 2

KumarHarsh
KumarHarsh

Reputation: 5094

i think userid is not there in table. so like @M.ali said first keep one back of old table. also i think userid is auto generated. So also create studentid in user table

first step ,

SELECT studentid, Username, Password INTO Users
FROM Students

now update back student table for inserting userid in student table

update student set userid=u.userid
from student a inner join user u on s.studentid=u.userid

Upvotes: 1

Related Questions