I actually want it to replicate the primary key parameter on my "id" field. But as you can see below, it actually replicates it as a constraint rather than simply a "PRIMARY KEY" attribute on the table.

AquaData, 

When I right click on a table and select “Script object to windows as CREATE”, I get a table that looks like this: 

CREATE TABLE [dbo].[FR_DataPoints] ( 
[id] bigint IDENTITY(1,1) NOT NULL, 
[SeriesCode] varchar(128) NULL, 
[SeriesTitle] varchar(256) NULL, 
[ObservationDate] datetime NULL, 
[ObservationValue] decimal(38,8) NULL, 
[SeriesUnits] varchar(128) NULL, 
[ReleaseSourceName] varchar(256) NULL, 
[ReleaseSourceLink] varchar(1024) NULL, 
[LoadDate] date NULL DEFAULT (CONVERT([date],CONVERT([varchar],getdate(),(101)),0)), 
[LoadTime] varchar(25) NULL DEFAULT (getdate()), 
CONSTRAINT [PK__FR_DataP__3213E83F0D44F85C] PRIMARY KEY CLUSTERED([id]) 
ON [PRIMARY]) 
GO 

Next, 

If I run this directly on a new table (notice how I appended a “2” to the table name to test it out): 


CREATE TABLE [dbo].[FR_DataPoints2] ( 
[id] bigint IDENTITY(1,1) NOT NULL, 
[SeriesCode] varchar(128) NULL, 
[SeriesTitle] varchar(256) NULL, 
[ObservationDate] datetime NULL, 
[ObservationValue] decimal(38,8) NULL, 
[SeriesUnits] varchar(128) NULL, 
[ReleaseSourceName] varchar(256) NULL, 
[ReleaseSourceLink] varchar(1024) NULL, 
[LoadDate] date NULL DEFAULT (CONVERT([date],CONVERT([varchar],getdate(),(101)),0)), 
[LoadTime] varchar(25) NULL DEFAULT (getdate()), 
CONSTRAINT [PK__FR_DataP__3213E83F0D44F85C] PRIMARY KEY CLUSTERED([id]) 
ON [PRIMARY]) 
GO 

I get this error: 

>[Error] Script lines: 22-34 ------------------------ 
Id 2714, Level 16, State 5, Line 1 
There is already an object named 'PK__FR_DataP__3213E83F0D44F85C' in the database. 
Msg: 2714, Level: 16, State: 5, Procedure: , Line: 1 
________________________________ 

>[Error] Script lines: 22-34 ------------------------ 
Id 1750, Level 16, State 0, Line 1 
Could not create constraint. See previous errors. 
Msg: 1750, Level: 16, State: 0, Procedure: , Line: 1 
________________________________ 

[Executed: 11/28/2018 11:33:55 AM] [Execution: 77ms] 



What I really wanted to do was have it general SQL that looks like this: 



CREATE TABLE [dbo].[FR_DataPoints] ( 
[id] bigint IDENTITY(1,1) NOT NULL PRIMARY KEY, 
[SeriesCode] varchar(128) NULL, 
[SeriesTitle] varchar(256) NULL, 
[ObservationDate] datetime NULL, 
[ObservationValue] decimal(38,8) NULL, 
[SeriesUnits] varchar(128) NULL, 
[ReleaseSourceName] varchar(256) NULL, 
[ReleaseSourceLink] varchar(1024) NULL, 
[LoadDate] date NULL DEFAULT (CONVERT([date],CONVERT([varchar],getdate(),(101)),0)), 
[LoadTime] varchar(25) NULL DEFAULT (getdate()) 
) 
GO 

I’m not sure if the on [PRIMARY] indicator is necessary, so I removed it. 

Do you have any idea how to do this? It’s a useful feature to aid with production migration, but it’s tedious as I have to always modify and fix the SQL before executing it in my production environment. 

Please let me know how to do this correctly in ADS.