CODEWITHSUNDEEP
sql-servert-sql
user2443476
user2443476

Reputation: 1975

Add column with default value from a previous select

I have a value which I get like this:

select myValue from myTable where id=5

I want to add an nvarchar column to another table where the default value will be the previous one.

But I cannot see how to stock this previous value to use it in my adding column script :

IF NOT EXISTS(SELECT * FROM sys.columns WHERE [name] = N'NewColumn' AND [object_id] = OBJECT_ID(N'MySecondTable'))
BEGIN
   ALTER TABLE MySecondTable
   ADD    NewColumn nvarchar(250) NOT NULL DEFAULT 'my previous value'     
END
GO

Thanks in advance for your help!

Upvotes: 2

Views: 188

Answers (2)

TT.
TT.

Reputation: 16137

Dynamic SQL to the rescue:

DECLARE @defval NVARCHAR(500)=LEFT((SELECT myValue FROM myTable where id=5),250);
SET @defval=REPLACE(@defval,N'''',N''''''); -- double single quotes for dynamic SQL statements
DECLARE @stmt NVARCHAR(MAX)=N'
  IF NOT EXISTS(SELECT * FROM sys.columns WHERE [name]=N''NewColumn'' AND [object_id]=OBJECT_ID(N''MySecondTable''))
  BEGIN
    ALTER TABLE MySecondTable
    ADD NewColumn nvarchar(250) NOT NULL DEFAULT N'''+@defval+'''
  END;
';
EXECUTE sp_executesql @stmt;

Upvotes: 2

Raj
Raj

Reputation: 10843

DECLARE @default nvarchar(250)
DECLARE @SQL nvarchar(max)
SELECT @default = myValue from myTable WHERE id=5

IF NOT EXISTS(SELECT * FROM sys.columns WHERE [name] = N'NewColumn' AND [object_id] = OBJECT_ID(N'MySecondTable'))
BEGIN
    SET @SQL = '
   ALTER TABLE MySecondTable
   ADD    NewColumn nvarchar(250) NOT NULL DEFAULT ''' + @default+''''
   EXEC sp_executesql @SQL     
END
GO

Upvotes: 1

Related Questions