Add Primary Key to existing table

-- First backup the database and Log.

-- Then set Recovery Mode to Simple
ALTER DATABASE MOI_20161206024518612 SET RECOVERY SIMPLE
Go

-- Then Shrink the Database Log to (almost) nothing
DBCC SHRINKDATABASE (MOI_20161206024518612)
go

CREATE NONCLUSTERED INDEX [IX_Environmental_dtDateTime]
ON [Environmental] ( [dtDateTime] asc )

ALTER TABLE Environmental
ADD CONSTRAINT PK_Environmental_nID PRIMARY KEY (nID[, optionalcolumns]);

select schema_name(tab.schema_id) as [schema_name], 
    tab.[name] as table_name
from sys.tables tab
    left outer join sys.indexes pk
        on tab.object_id = pk.object_id 
        and pk.is_primary_key = 1
where pk.object_id is null
order by schema_name(tab.schema_id),
    tab.[name]

SELECT 
     schemaname = OBJECT_SCHEMA_NAME(o.object_id)
    ,tablename = o.NAME
FROM sys.objects o
INNER JOIN sys.indexes i ON i.OBJECT_ID = o.OBJECT_ID
-- tables that are heaps without any nonclustered indexes
WHERE (
        o.type = 'U'
        AND o.OBJECT_ID NOT IN (
            SELECT OBJECT_ID
            FROM sys.indexes
            WHERE index_id > 0
            )
        )
        --    OR
        -- table that have a clustered index without any nonclustered indexes
        --(o.type='U' 
        --        AND o.OBJECT_ID NOT IN (
        --    SELECT OBJECT_ID 
        --        FROM sys.indexes 
        --        WHERE index_id>1))  

-- ==================================================================
-- sys.indexes.type 0 => heap - i.e. no Primary Key 
--					1 => clustered index
--					2 => non-clustered index
-- ==================================================================

SELECT	o.object_id
        , schemaname = OBJECT_SCHEMA_NAME(o.object_id)
		, tablename = o.NAME
		, PrimaryKey = CASE WHEN pk.object_id IS NULL THEN 'No Primary Key' ELSE '' END 
		, ClusteredIndexes = CASE WHEN ci.object_id IS NULL THEN 'No Clustered Index' ELSE '' END
		, NonClusteredIndexes = CASE WHEN nci.object_id IS NULL THEN 'No Non-Clustered Index' ELSE ''  END
		, [RowCount] = (SELECT SUM(p.Rows) from sys.partitions AS p where p.object_id=o.object_id) / (1+COUNT(ci.object_id)+COUNT(nci.object_id))
		, [IndexCount] = COUNT(ci.object_id)+COUNT(nci.object_id)
FROM sys.objects o
LEFT OUTER JOIN sys.indexes nci ON nci.object_id = o.object_id and nci.type=2
LEFT OUTER JOIN sys.indexes ci ON ci.object_id = o.object_id and ci.type=1
LEFT OUTER JOIN sys.indexes pk ON o.object_id = pk.object_id AND pk.is_primary_key = 1
WHERE o.Type='U' AND ((pk.object_id IS NULL) OR (ci.object_id IS NULL) OR (nci.object_id IS NULL))

GROUP BY o.object_id,
         OBJECT_SCHEMA_NAME(o.object_id), 
         o.NAME, 
		 CASE WHEN pk.object_id IS NULL THEN 'No Primary Key' ELSE '' END, 
		 CASE WHEN nci.object_id IS NULL THEN 'No Non-Clustered Index' ELSE '' END,
		 CASE WHEN ci.object_id IS NULL THEN 'No Clustered Index' ELSE '' END

ORDER BY TableName ASC

DECLARE @RowsOfPage AS INT
DECLARE @PageNumber AS INT
-- DECLARE @MaxTablePage AS FLOAT 
SET @PageNumber=1
SET @RowsOfPage=10
Select MaintenanceRecord.*, 
PM_StationsList.StationName, PM_StationsList.StationDescription, PM_StationsList.StationType,
LookupLists.ItemDescription, LookupLists.IntValue AS Repeat, LookupLists.strValue as RepeatUnit 
FROM [MaintenanceRecord] 
LEFT OUTER JOIN PM_StationsList ON PM_StationsList.FactoryStationID=MaintenanceRecord.StationID
LEFT OUTER JOIN LookupLists ON ( LookupLists.ListName='MaintenanceItem' AND LookupLists.ItemLookup=MaintenanceRecord.ItemCode)
ORDER BY ItemDueDate DESC
-- =====================================
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
-- SET @PageNumber = @PageNumber + 1
-- =====================================

SELECT tablo.columnName FROM 
(SELECT ROW_NUMBER()OVER(Order By columnName)indexer, 
 * FROM tabloName)tablo WHERE tablo.indexer = @sayac

BULK INSERT ManualImport
FROM 'C:\temp\Second_Data-2022-09-15.csv'
WITH
(
    FIRSTROW = 2, -- as 1st one is header
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

SELECT MIN(DT) as MinDate, MAX(DT) as MaxDate, Count(DT) as [RowCount]
FROM ManualImport

delete
FROM ManImp
WHERE ID NOT IN
(
    SELECT MAX(ID)
	FROM ManImp
	GROUP BY DT
)

SELECT DT as TheDate, Count(*) as TheCount
From ManImp
Group By DT 
Having count(*)>1
Order by TheCount DESC

drop table ManImp_002_working

select [ID], [DT], 
  CASE WHEN WS='NA' THEN NULL ELSE CAST(WS AS FLOAT) END AS [WS], 
  CASE WHEN WD='NA' THEN NULL ELSE CAST(WD AS FLOAT) END AS [WD], 
  CASE WHEN AT='NA' THEN NULL ELSE CAST([AT] AS FLOAT) END AS [AT],  
  CASE WHEN RH='NA' THEN NULL ELSE CAST(RH AS FLOAT) END AS [RH], 
  [SampleType], [RecCount], [DT_End] into ManImp_002_working 
from ManImp_002

ALTER TABLE [dbo].ManImp_002_working ADD  CONSTRAINT [PK_MI2_ID] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_MI2_DT] ON [dbo].[ManImp_002_working]
(
	[DT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


ALTER TABLE [dbo].ManImp_002_working ADD  CONSTRAINT [CNSTRT_MI2_SampleType]  DEFAULT ('Seconds') FOR [SampleType]
GO

ALTER TABLE [dbo].ManImp_002_working ADD  CONSTRAINT [CNSTRT_MI2_RecCount]  DEFAULT ((1)) FOR [RecCount]
GO

SELECT CAST(dtDateTime as Date) As TheDate, count(*) as TotalRecs, 
COUNT(CASE WHEN nDBWTS_EffluentFlow<0 THEN 1 END) AS CountNegatives, 
Min(nDBWTS_EffluentFlow) as MinFlow, Max(nDBWTS_EffluentFlow) as MaxFlow, 
SUM(nDBWTS_EffluentFlow) as TotAllFlow, 
AVG(nDBWTS_EffluentFlow) as AvgAllFlow, 
AVG(nDBWTS_EffluentFlow)*0.001 * 60 * 1440 as AllEnvirSum, 
SUM(CASE WHEN nDBWTS_EffluentFlow>=0 THEN nDBWTS_EffluentFlow END) as TotPosFlow,
AVG(CASE WHEN nDBWTS_EffluentFlow>=0 THEN nDBWTS_EffluentFlow END) AS AvgPosFlow,
AVG(CASE WHEN nDBWTS_EffluentFlow>=0 THEN nDBWTS_EffluentFlow END)*0.001 * 60 * 1440  AS PosEnvirSum
FROM [EnvolvData].[dbo].[Environmental]
WHERE CAST(dtDateTime AS Date) BETWEEN '2022-09-01' AND '2022-09-20'
GROUP BY CAST(dtDateTime as Date)
ORDER BY CAST(dtDateTime as Date) ASC

UPDATE 
    t1
SET 
    t1.c1 = t2.c2,
    t1.c2 = expression,
    ...   
FROM 
    t1
    [INNER | LEFT] JOIN t2 ON join_predicate
WHERE 
    where_predicate;