How to write dynamic insert statement in sql server?

Question

I have a Student table and Department table. Student Table contains Three columns

StudentId  |  DeptId  | StudentName

Department Table contains

DeptId  |  DeptName

It may be the case that DeptTable sometimes doesnot exists. That is I am deploying script with two scenarios sometimes with department and sometimes without. In the case without department , departmentId will not be there in student table So when Department table exists I have to insert value of DeptId otherwise I have to write insert statement without departmentId

IF (EXISTS(
        SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'Department'
            AND column_name = 'DeptId'
    ))
DECLARE @DeptId UNIQUEIDENTIFIER;
SET @DeptId = Select DeptId From Department Where DeptName = 'Computer'

INSERT INTO Student ([DeptId], [StudentName])
VALUES (@DeptId, 'TBAG')

But as DeptId column sometimes not present I need to create dynamic Sql.

So I created Select statement

DECLARE @sqlCommand nvarchar(1000)
DECLARE @DeptName varchar(75)
declare @DeptId uniqueIdentifier
SET @DeptName = 'Computer'
SET @sqlCommand = 'SELECT @dept=DeptId FROM customers WHERE DeptName = @DeptName'
EXECUTE sp_executesql @sqlCommand, N'@DeptName varchar(50),@dept uniqueIdentifier  OUTPUT', @DeptName = @DeptName, @dept=@DeptId OUTPUT

But how to write Insert statement I am not getting

Devart · Accepted Answer · 2016-01-21 11:19:38Z

1

CREATE TABLE dbo.Student
(
    DeptId UNIQUEIDENTIFIER,
    StudentName VARCHAR(20)
)
GO

DECLARE @DeptId UNIQUEIDENTIFIER

IF COL_LENGTH('dbo.Department', 'DeptId') IS NOT NULL BEGIN
    SELECT @DeptId = DeptId
    FROM dbo.Department
    WHERE DeptName = 'Computer'
END

INSERT INTO dbo.Student(DeptId, StudentName)
SELECT @DeptId /* null is when DeptId is not exists */, 'TBAG'

Output -

(1 row(s) affected)

edited Jan 21, 2016 at 11:19

answered Jan 21, 2016 at 11:00

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Manjay_TBAG Over a year ago

SELECT @DeptId = DeptId FROM dbo.Department as sometime department table itself will not present so this will become invalid.

Devart Over a year ago

@TBAG please provide your error message in this case

Manjay_TBAG Over a year ago

Invalid column name 'DeptId'. As thr table itself is not present. I mentioned in question It may be the case that DeptTable sometimes doesnot exists

Manjay_TBAG Over a year ago

Apology!! I have missed something in question. Actually case is I have a post deployment scripts. I deploy database sometimes with deptId and sometimes without deptId. In both case I want to use same post deployment scrupt. In the case when deptId will not be there. It will not be there even in Student Table. Sorry Again. In this case script is not working. I have updated question.

StackUser · Accepted Answer · 2016-01-21 14:02:57Z

As per my understanding you want to prepare dynamic insert statement for student table with two cases (with deptid or without deptid)

Please refer the code below.

DECLARE @STUDENTID   INT='',--MENTION YOUR STUDENT ID VALUE AND USE THE SAME IN THE ELSE PART COMMENT OUT IF IT HAS IDENTITY
            @STUDENTNAME VARCHAR(100)=''--MENTION YOUR STUDENT NAME
    IF Col_length('dbo.Student', 'DeptId') IS NOT NULL
      BEGIN
          INSERT INTO dbo.Student
                      (DeptId,
                       StudentName)
          SELECT DeptId,
                 'TBAG'
          FROM   dbo.Department
          WHERE  DeptName = 'Computer'
      END
    ELSE
      BEGIN
          INSERT INTO dbo.Student
                      (StudentName)
          SELECT @STUDENTNAME
      END

Collectives™ on Stack Overflow

How to write dynamic insert statement in sql server?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related