Adding both constraints in one statement wasn&#39;t as easy as I thought it would be and there didn&#39;t seem to be many examples out there (at least I wasn&#39;t able to find any very easily), so I thought I&#39;d share how I did it here and maybe someone can suggest a better way?



    ALTER TABLE [table name] ADD
        [New Column Name] [Column Type] 
        CONSTRAINT [constraint name] DEFAULT ([default value]) NOT NULL,
        CONSTRAINT [constraint name] FOREIGN KEY ([New Column Name]) 
        REFERENCES [Other Table] ([Foreign ID])
	
Example:

    ALTER TABLE tableA ADD
        myNewColumn BIGINT 
        CONSTRAINT myNamedConstraint_df default (1) NOT NULL,
        CONSTRAINT myNamedConstraint_fk FOREIGN KEY (myNewColumn)
        REFERENCES tableB (tableBPrimaryKeyID)


I am willing to bet that this is a really simple answer as I am a noob to SQL.

table 1 has column 1 (criteria 1) column 2 (criteria 2) column 3 (metric 1)

table 2 has column 1 (criteria 1) column 2 (criteria 2) column 3 (metric 2 specific to table2.criteria2)

There can be anywhere from 1 - 5 values of criteria 2 for each criteria 1 on the table.

when I use the join statement here (assuming I identify table 1 as One prior to this):
    
    Select WeddingTable, TableSeat, TableSeatID, Name, Two.Meal
    FROM table1 as One
    inner join table2 as Two
    on One.WeddingTable = Two.WeddingTable and One.TableSeat = Two.TableSeat

I only get one of the criteria 1/criteria 2 combinations even when I know for a fact that there are 3 or 4.  How do I get all combinations?

Take the situation where there is a wedding where table 1 is basically a seating chart, and table 2 is the meal option that each table/seat has chosen. Table 1 has the convenient TableSeatID, but Table 2 does not have a comparable ID.

**Sample Data:**

![enter image description here][1]


The results needs to show all 4 lines, being all 3 seats at WeddingTable 001 and the one seat at WeddingTable 002.

**Desired Results:**

![enter image description here][2]


  [1]: http://i.stack.imgur.com/I5IRy.jpg
  [2]: http://i.stack.imgur.com/c1dr2.png

How to do join on multiple criteria, returning all combinations of both criteria

I was wondering, can you create a function with an optional parameter.

Example:

    function parameterTest(test)
    {
       if exists(test)
       {
         alert(&#39;the parameter exists...&#39;);
       }
       else
       {
         alert(&#39;The parameter doesn\&#39;t exist...&#39;);
       }
    }

So if you call `parameterTest()` then the result would be a message &quot;The parameter doesn&#39;t exist...&quot;. And if you call `parameterTest(true)` then it would return &quot;the parameter exists...&quot;. 

Is this possible?

How to test if a parameter is provided to a function?

I have two existing tables (TableA and TableB) and I need to add a new column to TableA that has a foreign key to TableB and has a default value that is not null... and both these constraints need to be named. How can I do this?

How to alter table add column with named default constraint and named foreign key constraint?

<p>I have two existing tables (TableA and TableB) and I need to add a new column to TableA that has a foreign key to TableB and has a default value that is not null... and both these constraints need to be named. How can I do this?</p>


I use the following to search for strings within the stored procedures of a specific database:

    USE DBname
    SELECT Name
    FROM sys.procedures
    WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE &#39;%xxx%&#39;

Is it easy to amend the above so that it searches Table names in a specific db &quot;DBname&quot; ?



Search of table names

I want to write some C# code to initialize my database with some seed data.  Clearly, this is going to require the ability to be able to set the values of various Identity columns when inserting.  I&#39;m using a code-first approach.  By default, `DbContext` handles the database connection and so you can&#39;t `SET IDENTITY_INSERT [dbo].[MyTable] ON`.  So, what I&#39;ve done so far is use the `DbContext` constructor that lets me specify a DB connection to be used.  Then, I set `IDENTITY_INSERT` to `ON` in that DB connection, and then try to insert my records using entity framework.  Here&#39;s an example of what I&#39;ve got so far:

    public class MyUserSeeder : IEntitySeeder {
        public void InitializeEntities(AssessmentSystemContext context, SqlConnection connection) {
            context.MyUsers.Add(new MyUser { MyUserId = 106, ConceptPersonId = 520476, Salutation = &quot;Mrs&quot;, Firstname = &quot;Novelette&quot;, Surname = &quot;Aldred&quot;, Email = null, LoginId = &quot;520476&quot;, Password=&quot;28c923d21b68fdf129b46de949b9f7e0d03f6ced8e9404066f4f3a75e115147489c9f68195c2128e320ca9018cd711df&quot;, IsEnabled = true, SpecialRequirements = null });
            try {
                connection.Open();
                SqlCommand cmd = new SqlCommand(&quot;SET IDENTITY_INSERT [dbo].[MyUser] ON&quot;, connection);
                int retVal = cmd.ExecuteNonQuery();
                context.SaveChanges();
            }
            finally {
                connection.Close();
            }
        }
    }

So close and yet so far - because, although `cmd.ExecuteNonQuery()` works fine, when I then run `context.SaveChanges()`, I&#39;m informed that &quot;Explicit value must be specified for identity column in table &#39;MyUser&#39; either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.&quot;

Presumably, because MyUserId (which is the Identity column in the MyUser table) is the primary key, entity framework doesn&#39;t try to set it when I call `context.SaveChanges()`, even though I gave the `MyUser` entity a value for the `MyUserId` property.

Is there a way to force entity framework to try and insert even primary key values for an entity, then?  Or maybe a way to temporarily mark `MyUserId` as not being a primary key value, so EF tries to insert it?

How can I force entity framework to insert identity columns?

I need to run a select statement that returns all rows where the value of a column is not distinct (e.g. EmailAddress).

For example, if the table looks like below:

    CustomerName     EmailAddress
    Aaron            aaron@gmail.com
    Christy          aaron@gmail.com
    Jason            jason@gmail.com
    Eric             eric@gmail.com
    John             aaron@gmail.com

I need the query to return:

    Aaron            aaron@gmail.com
    Christy          aaron@gmail.com
    John             aaron@gmail.com

I have read many posts and tried different queries to no avail.  The query that I believe should work is below.  Can someone suggest an alternative or tell me what may be wrong with my query?

    select EmailAddress, CustomerName from Customers
    group by EmailAddress, CustomerName
    having COUNT(distinct(EmailAddress)) &gt; 1

How to Select Every Row Where Column Value is NOT Distinct

I&#39;m performing an `UPDATE` with `OUTPUT` query:

    UPDATE BatchReports
    SET IsProcessed = 1
    OUTPUT inserted.BatchFileXml, inserted.ResponseFileXml, deleted.ProcessedDate
    WHERE BatchReports.BatchReportGUID = @someGuid

This statement is well and fine; until a trigger is defined on the table. Then my `UPDATE` statement will get the error [334](http://msdn.microsoft.com/en-us/library/aa937592(v=sql.80).aspx):

&gt; The target table &#39;BatchReports&#39; of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause


Now this problem is explained in a blog post by the SQL Server team -- [UPDATE with OUTPUT clause – Triggers – and SQLMoreResults](https://techcommunity.microsoft.com/t5/sql-server/update-with-output-clause-8211-triggers-8211-and-sqlmoreresults/ba-p/383457):

&gt; The error message is self-explanatory

And they also give solutions:

&gt; The application was changed to utilize the INTO clause

Except I cannot make head nor tail of the entirety of the blog post.

So let me ask my question: What should I change my `UPDATE` to so that it works?

See also
----------

- [UPDATE with OUTPUT clause – Triggers – and SQLMoreResults](http://blogs.msdn.com/b/sqlprogrammability/archive/2008/07/11/update-with-output-clause-triggers-and-sqlmoreresults.aspx)
- https://stackoverflow.com/questions/6504173/why-is-the-target-table-of-a-merge-statement-not-allowed-to-have-enabled-rules
- https://stackoverflow.com/questions/12537653/statement-contains-an-output-clause-without-into-clause-error




Content Type	Original Author	Original Content on Stackoverflow
Question	Steve	View Question on Stackoverflow
Solution 1 - Sql Server	Steve	View Answer on Stackoverflow

How to alter table add column with named default constraint and named foreign key constraint?

Sql Server Problem Overview

Sql Server Solutions

Solution 1 - Sql Server

How to test if a parameter is provided to a function?

How to do join on multiple criteria, returning all combinations of both criteria

Attributions