Create backup of table with indexes and constraints

PHOTO EMBED

Mon Sep 26 2022 16:56:33 GMT+0000 (UTC)

Saved by @dhfinch #sql #sqlserver

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

content_copyCOPY