June 29, 2017 at 1:36 pm
This works fine.. But I like to write this as 2 statements
1.) Create table ( via T-SQL )
2.) After creating table add the constraint ( using T-SQL )
3.) What I need from you is the syntax ( i searched for it but didnt have luck )
CREATE TABLE [dbo].[NPI100](
[NPI] [varchar](10) NOT NULL,
[EntityTypeCode] [varchar](1) NULL,
CONSTRAINT [PK_NPIDATAX100] PRIMARY KEY CLUSTERED
(
[NPI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
June 29, 2017 at 1:42 pm
After removing all the 'fluff', it would look something like this:
CREATE TABLE dbo.NPI100
(
NPI VARCHAR(10) NOT NULL
, EntityTypeCode VARCHAR(1) NULL
);
GO
ALTER TABLE dbo.NPI100
ADD CONSTRAINT PK_NPIDATAX100 PRIMARY KEY CLUSTERED (NPI);
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 29, 2017 at 1:49 pm
Phil Parkin - Thursday, June 29, 2017 1:42 PMAfter removing all the 'fluff', it would look something like this:
CREATE TABLE dbo.NPI100
(
NPI VARCHAR(10) NOT NULL
, EntityTypeCode VARCHAR(1) NULL
);
GOALTER TABLE dbo.NPI100
ADD CONSTRAINT PK_NPIDATAX100 PRIMARY KEY CLUSTERED (NPI);
Cool! But I wanted the stuff inside the WITH statement as well ( particularly the IGNORE_DUP_KEY = ON part , this is very important )
IGNORE_DUP_KEY = ON
June 29, 2017 at 1:56 pm
Did you even try? It's not a great leap from what I posted.CREATE TABLE dbo.NPI100
(
NPI VARCHAR(10) NOT NULL
, EntityTypeCode VARCHAR(1) NULL
);
GO
ALTER TABLE dbo.NPI100
ADD CONSTRAINT PK_NPIDATAX100 PRIMARY KEY CLUSTERED (NPI) WITH (IGNORE_DUP_KEY = ON);
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 29, 2017 at 2:29 pm
I am just curuios why would you want to break that up into 2 statements instead of 1?
I know you can, I'm just not sure what the benefit would be.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 29, 2017 at 2:31 pm
Here is my next question
I have a need to load new data into dbo.NPI100
However before I do that I like to make a backup copy of the table.
Rather than use SELECT * INTO BACKUP_TBL from NPI100 I like to use sp_rename ( Why ? because NPI100 has 6 million rows and it takes time to copy )
Then I like to recreate NPI100 table.
Now I can run a CREATE TABLE NPI100 ( .......
Then I want to add the PRIMARY KEY at run time using the ALTER table statement.
PROBLEM: You need to give the costraint a unique name... This is the issue.
So can you help me with the script below so that this is what I wan to do
MAIN OBJECTIVE: With each run I want to backup the npi100 table with a table that has a unique name ( Ideal to use todays date and time like I shown in my script )
1. Select * INTO tmp1 FROM NPI100 where 1=2 ; -- This will create an empty table later we can use this in step #3
2. Rename table NPI100 with some variable name ( i think I can do that as shown in the script )
3. CREATE NPI100 table and add the primary key ( I don't care what name you give the constraint but it has to be unique )
So we can do this by
Select * INTO NPI100 from tmp1 WHERE 1=2 --- This creates an empty table
4. Help me add the primary key ( I don't care what name you use for the constraint but it should be unique , if you make it a constant it will be a problem since when I run the script next time it will say CONSTRAINT already in use.
So my suggestion: use a variable constraint name ( This is where I need help ) ( I wonder whether we can use SP_EXEC with a variable name for the PK constraint )
Lets see...
final step
DROP TABLE tmp1;
/*
CREATE TABLE dbo.NPI100
(
NPI VARCHAR(10) NOT NULL
, EntityTypeCode VARCHAR(1) NULL
);
GO
ALTER TABLE dbo.NPI100
ADD CONSTRAINT PK_NPIDATAX100 PRIMARY KEY CLUSTERED (NPI);
GO
*/
Declare @PK_KeyName varchar(100);
Declare @BackupTable varchar(100);
Select @PK_KeyName='PK_NPI_' + CONVERT(CHAR(8), GETDATE(), 112 ) + Replace((convert(varchar(20),GETDATE(),108)), ':', '' );
Select @BackupTable = 'NPI100_' + + CONVERT(CHAR(8), GETDATE(), 112 ) + Replace((convert(varchar(20),GETDATE(),108)), ':', '' );
PRINT @BackupTable;
PRINT @PK_KeyName
EXEC sp_rename 'dbo.NPI100' , @BackupTable ; -- This works fine
GO
/* However the following will not work * /
ALTER TABLE @BackupTable
ADD CONSTRAINT @PK_KeyName PRIMARY KEY CLUSTERED (NPI);
June 29, 2017 at 2:34 pm
bmg002 - Thursday, June 29, 2017 2:29 PMI am just curuios why would you want to break that up into 2 statements instead of 1?
I know you can, I'm just not sure what the benefit would be.
Hi bmg, my question below will explain why I need that in 2 parts, I need a script so that with each run it will make a backup table of the NPI100 table and then give me a empty table NPI100 so that I can load new data.
June 29, 2017 at 2:45 pm
Yep, that makes a bit of sense.
As for the section:
/* However the following will not work * /
ALTER TABLE @BackupTable
ADD CONSTRAINT @PK_KeyName PRIMARY KEY CLUSTERED (NPI);
why not use a variable to store that into and use dynamic SQL to work with it?
What I mean is something like:DECLARE @query varchar(8000);
SELECT @query = 'ALTER TABLE ' + @BackupTable + 'ADD CONSTRAINT ' + @PK_KeyName + ' PRIMARY KEY CLUSTERED (NPI);'
EXEC (@query)
That should handle what you are looking for, no?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 29, 2017 at 3:01 pm
Thank you .. In the mean time I figured out...
I dont think we need to give a name to a constraint.. So the script works
IF EXISTS ( Select * FROM SYS.TABLES where NAME = 'tmp' )
BEGIN
DROP TABLE tmp;
PRINT 'Table tmp dropped';
END
;
IF EXISTS ( Select * FROM SYS.TABLES where NAME = 'NPI100' )
BEGIN
Select * INTO tmp FROM NPI100 WHERE 1=2;
PRINT 'Table tmp created';
END
;
Declare @BackupTable varchar(100);
Select @BackupTable = 'NPI100_' + + CONVERT(CHAR(8), GETDATE(), 112 ) + Replace((convert(varchar(20),GETDATE(),108)), ':', '' );
PRINT @BackupTable;
EXEC sp_rename 'dbo.NPI100' , @BackupTable ; -- This works fine
PRINT 'TBL: ' + @BackupTable + ' created ';
IF NOT EXISTS ( Select * FROM SYS.TABLES where NAME = 'NPI100' )
BEGIN
Select * INTO NPI100 FROM tmp WHERE 1=2;
ALTER TABLE NPI100 ADD PRIMARY KEY (NPI);
DROP TABLE TMP;
PRINT 'NPI100 table created';
END
;
June 29, 2017 at 3:05 pm
The command wouldn't work anyway, as you noted, but just to point it out, you wouldn't be trying to add the new primary key to the backup table anyway, since that's just your renamed old table, which already has a primary key.
Dynamic T-SQL is one way to do this, and another is just to rename both the old table and its constraint, like so:
Declare @PK_KeyName varchar(100);
Declare @BackupTable varchar(100);
Select @PK_KeyName='PK_NPI_' + CONVERT(CHAR(8), GETDATE(), 112 ) + Replace((convert(varchar(20),GETDATE(),108)), ':', '' );
Select @BackupTable = 'NPI100_' + + CONVERT(CHAR(8), GETDATE(), 112 ) + Replace((convert(varchar(20),GETDATE(),108)), ':', '' );
PRINT @BackupTable;
PRINT @PK_KeyName
EXEC sp_rename 'dbo.NPI100' , @BackupTable ; -- This works fine
EXEC sp_rename 'dbo.PK_NPIDATAX100',@PK_Keyname;
CREATE TABLE dbo.NPI100
(
NPI VARCHAR(10) NOT NULL
, EntityTypeCode VARCHAR(1) NULL
);
GO
ALTER TABLE dbo.NPI100
ADD CONSTRAINT PK_NPIDATAX100 PRIMARY KEY CLUSTERED (NPI);
GO
The third option is to let SQL Server generate one of its automatic names for you, although I prefer more readable names 🙂
Cheers!
EDIT: Ah, as I was typing you posted that you went the third route.
June 29, 2017 at 3:10 pm
mw112009 - Thursday, June 29, 2017 3:01 PMThank you .. In the mean time I figured out...
I dont think we need to give a name to a constraint.. So the script works
IF EXISTS ( Select * FROM SYS.TABLES where NAME = 'tmp' )
BEGIN
DROP TABLE tmp;
PRINT 'Table tmp dropped';
END
;
IF EXISTS ( Select * FROM SYS.TABLES where NAME = 'NPI100' )
BEGIN
Select * INTO tmp FROM NPI100 WHERE 1=2;
PRINT 'Table tmp created';
END
;Declare @BackupTable varchar(100);
Select @BackupTable = 'NPI100_' + + CONVERT(CHAR(8), GETDATE(), 112 ) + Replace((convert(varchar(20),GETDATE(),108)), ':', '' );
PRINT @BackupTable;EXEC sp_rename 'dbo.NPI100' , @BackupTable ; -- This works fine
PRINT 'TBL: ' + @BackupTable + ' created ';IF NOT EXISTS ( Select * FROM SYS.TABLES where NAME = 'NPI100' )
BEGIN
Select * INTO NPI100 FROM tmp WHERE 1=2;
ALTER TABLE NPI100 ADD PRIMARY KEY (NPI);
DROP TABLE TMP;
PRINT 'NPI100 table created';
END
;
BTW - Can you help me with the CONVERT function, if I use 108 it gives me the HH:MM:SS , if there a way for me to get the MILLISECONDS added as well ?
June 29, 2017 at 3:24 pm
I think 114 is the one you're looking for. It will return hh:mi:ss:mmm(24h).
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles
June 30, 2017 at 6:53 am
Kaye Cahs - Thursday, June 29, 2017 3:24 PMI think 114 is the one you're looking for. It will return hh:mi:ss:mmm(24h).
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles
Yeas that works, Thx. Happy 4th of July Holiday
Select Replace((convert(varchar(20),GETDATE(),114)), ':', '' );--Milliseconds included
Select Replace((convert(varchar(20),GETDATE(),108)), ':', '' );--HHMMSS included
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply