SWITCH a non-partitioned table to the first empty partition or the last empty partition of a partitioned table
USE MASTER
CREATE DATABASE OYCO
ON PRIMARY
(NAME='OYCO_DAT',FILENAME='C:\DATA\OYCO_DAT.mdf'),
FILEGROUP FG1
(NAME='FG1_DAT',FILENAME='C:\DATA\FG1_DAT.ndf'),
FILEGROUP FG2
(NAME='FG2_DAT',FILENAME='C:\DATA\FG2_DAT.ndf'),
FILEGROUP FG3
(NAME='FG3_DAT',FILENAME='C:\DATA\FG3_DAT.ndf'),
FILEGROUP FG4
(NAME='FG4_DAT',FILENAME='C:\DATA\FG4_DAT.ndf')
LOG ON
(NAME='OYCO_LOG',FILENAME='C:\DATA\OYCO_LOG.ldf');
GO
USE OYCO
CREATE PARTITION FUNCTION pf(INT)
AS RANGE LEFT FOR VALUES(5,10,15)
GO
CREATE PARTITION SCHEME ps AS PARTITION pf
TO (FG1,FG2,FG3,FG4)
GO


CREATE TABLE dbo.customer
(CustomerID INT NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Address1 VARCHAR(50) NULL,
CONSTRAINT pk PRIMARY KEY CLUSTERED (CustomerID)
) ON ps(CustomerID);
GO
SET NOCOUNT ON
DECLARE @count INT
SET @count=1;
WHILE @count<30
BEGIN
INSERT dbo.Customer (CustomerID,FirstName,LastName)
VALUES(@count,'F'+CAST(@count AS CHAR(5)),'L'+CAST(@count AS CHAR(5)))
SET @count=@count+1;
END
GO

--Make sure that the first partition is empty.
DELETE from dbo.customer
WHERE $PARTITION.pf(CustomerID)=1
--Make sure that the last partition is empty
DELETE from dbo.customer
WHERE $PARTITION.pf(CustomerID)=4

--Create a test table

CREATE TABLE dbo.summer
(CustomerID INT NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Address1 VARCHAR(50) NULL,
CONSTRAINT pk1 PRIMARY KEY CLUSTERED (CustomerID),
CONSTRAINT chk CHECK (CustomerID<1)
) ON FG1;
GO

SET NOCOUNT ON
DECLARE @count INT
SET @count=0;
WHILE @count>-5
BEGIN
INSERT dbo.Summer (CustomerID,FirstName,LastName)
VALUES(@count,'F'+CAST(@count AS CHAR(5)),'L'+CAST(@count AS CHAR(5)))
SET @count=@count-1;
END
GO


ALTER TABLE dbo.summer SWITCH to dbo.customer partition 1

--Create another test table

CREATE TABLE dbo.Winter
(CustomerID INT NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Address1 VARCHAR(50) NULL,
CONSTRAINT pk2 PRIMARY KEY CLUSTERED (CustomerID),
CONSTRAINT chk2 CHECK (CustomerID>1000)
) ON FG4;
GO
SET NOCOUNT ON
DECLARE @count INT
SET @count=1001;
WHILE @count<1050
BEGIN
INSERT dbo.Winter (CustomerID,FirstName,LastName)
VALUES(@count,'F'+CAST(@count AS CHAR(5)),'L'+CAST(@count AS CHAR(5)))
SET @count=@count+1;
END
GO


ALTER TABLE dbo.Winter SWITCH to dbo.customer partition 4

You cannot switch a table into the partition 2 or 3.

Pay attention to the CHECK CONSTRAINT in the test table. The check constraint garanttees that the data in the test table will not violate the partitioned table's primary key constraint.

Unsuccessful practice: I tried to use the composite primary key. Even though the value in the test table has no conflict with the partitions definition, the following error shows:

ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'OYCO.dbo.summer' allows values that are not allowed by check constraints or partition function on target table 'OYCO.dbo.customer'.

It is possible that SQL Server could not check the uniqueness of a composite key when swiching operation runs.