/*
Inserting the data from one table to another table in SQL Server.
Inserting the data from one table to another table in SQL Server.
We have 2 scenarios under this.
1. Inserting
the data from TableA to existing TableB
2. Inserting
the data from TableA to TableB while creating it.
Let’s look at both the ways of
inserting the data.
Method 1: Inserting the data from
TableA to existing TableB
Let’s Create TableA and name it
LocationDetails
*/
*/
--Create LocationDetails table.
USE Performance
GO
IF OBJECT_ID('LocationDetails','U') IS NOT NULL
DROP TABLE dbo.LocationDetails
CREATE TABLE dbo.LocationDetails
( City Varchar(20),
ZipCode Varchar(10),
[State] Varchar(10)
)
--Inserting the sample records into the
LocationDetails
INSERT INTO dbo.LocationDetails(City, ZipCode, [State])
VALUES ('Abilene', '79606', 'TX'),
('Adkins', '78101', 'TX'),
('Beaumont', '77707', 'TX'),
('Borden', '78962', 'TX'),
('Denton', '76207', 'TX')
--Check the Data in LocationDetails
SELECT * FROM dbo.LocationDetails
/*
Let’s Create TableB and name it Location.
*/
----Create Location
IF OBJECT_ID('Location','U') IS NOT NULL
DROP TABLE dbo.Location
CREATE TABLE dbo.Location (City VARCHAR(20), ZipCode VARCHAR(20))
/*
Now we have LocationDetails which is our TableA and Location which is our TableB.
Now we have LocationDetails which is our TableA and Location which is our TableB.
We would like to insert the records
into Location using LocationDetails.
*/
*/
--INSERT INTO Location from
LocationDetails
INSERT INTO dbo.Location(City, ZipCode)
SELECT City, ZipCode
FROM dbo.LocationDetails
WHERE [State] = 'TX'
/*In the above method, data types
should match in both source and target tables.*/
--Check the data in the table Location
SELECT * FROM dbo.Location
/*Method 2: Inserting the data from TableA to TableB while creating it.
We will consider the same table names
as above.
ie., lets create Location table while
inserting the data into it.*/
USE Performance
GO
IF OBJECT_ID('Location','U') IS NOT NULL
DROP TABLE dbo.Location
SELECT City, ZipCode
INTO dbo.Location
FROM dbo.LocationDetails
WHERE [State] = 'TX'
/*
Using the above query we would get the exact data types of the source table and all the records will get inserted into the newly created table satisfying the where condition.
Using the above query we would get the exact data types of the source table and all the records will get inserted into the newly created table satisfying the where condition.
If you don’t need any data but just the
structure of the table then use below query
*/
*/
IF OBJECT_ID('Location','U') IS NOT NULL
DROP TABLE dbo.Location
SELECT City, ZipCode
INTO dbo.Location
FROM LocationDetails
WHERE 1 = 2
--Check the data in the table Location
SELECT * FROM dbo.Location
