how do I add a variable in add constraint in sql

Question

Hi all, I am trying to create a primary key dynamically on the target table. I am trying to write a code to do this. However I am getting an error message when I try to run this code. How do I specify the variable next to add constraint. How do I declare a variable inside a dynamic slq code?

declare @sourcetablename nvarchar(max) = N'[dbo].[AAG00200]'
declare @targettablename nvarchar(max) = N'[dbo].[AAG00200_Test]'

declare @sql nvarchar(max)

set @sql = N'

IF OBJECT_ID(''tempdb.dbo.#TempTable'', ''U'') IS NOT NULL
  DROP TABLE #TempTableName

select * into #TempTable from '+@sourcetablename+'


select * from #TempTable

declare @PKname nvarchar(max) = select Index_name from #TempTable where is_primary_key = 1

ALTER TABLE '+@targettablename+'
ADD CONSTRAINT  ' +@PKname + '   PRIMARY KEY (ACTINDX)

drop table #TempTable'
EXEC sp_executesql @sql

Thanks Nish

Answer 1

Hi @Nishan Ratna Try this query:

declare @sourcetablename nvarchar(max) = N'[dbo].[AAG00200]'
declare @targettablename nvarchar(max) = N'[dbo].[AAG00200_Test]'
declare @PKname nvarchar(max)
declare @sql nvarchar(max)

set @sql = N'
IF OBJECT_ID(''tempdb.dbo.#TempTable'', ''U'') IS NOT NULL
DROP TABLE #TempTableName
select * into #TempTable from '+@sourcetablename+'
SELECT @PKname = Index_name FROM #TempTable where is_primary_key = 1'
EXEC sp_executesql @sql,N'@PKname nvarchar(max) OUTPUT',@PKname = @PKname OUTPUT

SET @sql=N'ALTER TABLE '+@targettablename+' ADD CONSTRAINT '+@PKname+' PRIMARY KEY (ACTINDX)'
EXEC sp_executesql @sql

Best regards, Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

A primary key serves as the unique identifier for each row of data in a table. It may be composed of a single column or multiple columns from the table. ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (col1, col2); The statement above adds a primary key constraint to the table table_name, and defines that key as being the data in col1 and col2 of each row. It also names that primary key constraint constraint_name – this is optional. We could run the statement above without constraint_name (as it is written earlier in this article) and it would execute correctly. A default name would be generated and assigned to the constraint.