I believe this would be somewhere close.

    INSERT INTO Files
    (FileId, FileData)
    SELECT 1, * FROM OPENROWSET(BULK N&#39;C:\Image.jpg&#39;, SINGLE_BLOB) rs

Something to note, the above runs in SQL Server 2005 and SQL Server 2008 with the data type as `varbinary(max)`. It was not tested with image as data type.


If you mean using a literal, you simply have to create a binary string:

    insert into Files (FileId, FileData) values (1, 0x010203040506)

And you will have a record with a six byte value for the FileData field.

---

You indicate in the comments that you want to just specify the file name, which you can&#39;t do with SQL Server 2000 (or any other version that I am aware of).

You would need a CLR stored procedure to do this in SQL Server 2005/2008 or an extended stored procedure (but I&#39;d avoid that at all costs unless you have to) which takes the filename and then inserts the data (or returns the byte string, but that can possibly be quite long).

---

In regards to the question of only being able to get data from a SP/query, I would say the answer is yes, because if you give SQL Server the ability to read files from the file system, what do you do when you aren&#39;t connected through Windows Authentication, what user is used to determine the rights?  If you are running the service as an admin (God forbid) then you can have an elevation of rights which shouldn&#39;t be allowed.

I know the syntax:

    ALTER TABLE [TheTable] DROP CONSTRAINT [TheDefaultConstraint]

but how to I drop the default constraint when I don&#39;t know its name? (That is, it was autogenerated at `CREATE TABLE` time.)




How do you drop a default value or similar constraint in T-SQL?

Trying to include a source-file into my latex document using the listings package, i got problems with german umlauts inside of the comments in the code.
Using

    \lstset{
	extendedchars=\true,
	inputencoding=utf8x
    }

Umlauts in the source files (encoded in UTF-8 without BOM) are processed, but they are somehow moved to the beginning of the word they are contained in.
So

    // die Gr&#246;&#223;e muss ber&#252;cksichtigt werden

in the input source file, becomes

    // die &#246;&#223;Gre muss &#252;bercksichtigt werden

in the output file.

NOTE: since i found errors in my initial setup, i heavily edited this question

Listings in Latex with UTF-8 (or at least german umlauts)

I have a SQL Server 2000 with a table containing an `image` column. 

How do I insert the binary data of a file into that column by specifying the path of the file?

    CREATE TABLE Files
    (
      FileId int,
      FileData image
    )

How can I insert binary file data into a binary SQL field using a simple insert statement?

I have a SQL Server 2000 with a table containing an <code>image</code> column.
How do I insert the binary data of a file into that column by specifying the path of the file?
<pre><code class="hljs language-sql">CREATE TABLE Files
(
 FileId int,
 FileData image
)
</code></pre>

My best google result was [this][1]:

 * below 11 are warnings, not errors
 * 11-16 are available for use
 * above 16 are system errors
 * there is no behavioral difference among 11-16

But, from BOL, &quot;Severity levels from 0 through 18 can be specified by any user.&quot;

In my particular stored procedure, I want the error returned to a .Net client application, so it looks like any severity level between 11-18 would do the trick.  Does anyone have any authoritative information about what each of the levels mean, and how they should be used?


  [1]: http://www.xaprb.com/blog/2005/11/23/be-consistent-with-raiserror-severitites/


What do the different RAISERROR severity levels mean?

*I thought this would be trivial, but it isn&#39;t... I&#39;m sure there is a simple way to do it but I can&#39;t manage to find it. Shame on me.*

I want to import/export the database itself, the tables, the constraints (foreign keys and so on). I&#39;d rather not get the data with it, but I can get rid of it after if there&#39;s no other way.

So... how do you export a database using MS SQL Server Management Studio ? How do you import it?

The only solution I found was right click on the tables and &quot;script to Create&quot;, but I have something like 100 tables, so I&#39;d rather avoid this.

Thanks!


Import / Export database with SQL Server Server Management Studio

I am just confused with the execution sequence of a SQL query when we use `GROUP BY` and `HAVING` with a `WHERE` clause. Which one gets executed first?  What is the sequence?



Execution sequence of Group By, Having and Where clause in SQL Server?

I have recently been running into many different areas of SQL Server that I normally don&#39;t mess with. One of them that has me confused is the area of Logins and Users. Seems like it should be a pretty simple topic...

It appears that each login can only have 1 user and each user can only have 1 login. 

A login can be associated to multiple tables thus associating that user to many tables. 

So my question is why even have a login and a user? they seem to be pretty much one in the same. What are the differences, or what is it that I seem to be missing?

Difference between a User and a Login in SQL Server

I need to implement the following query in SQL Server:

    select *
    from table1
    WHERE  (CM_PLAN_ID,Individual_ID)
    IN
    (
     Select CM_PLAN_ID, Individual_ID
     From CRM_VCM_CURRENT_LEAD_STATUS
     Where Lead_Key = :_Lead_Key
    )

But the WHERE..IN clause allows only 1 column. How can I compare 2 or more columns with another inner SELECT?

SQL WHERE.. IN clause multiple columns

What is the main purpose of using [CROSS APPLY][1]?

I have read (vaguely, through posts on the Internet) that `cross apply` can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind)

I also know that `CROSS APPLY` doesn&#39;t require a UDF as the right-table.

In most `INNER JOIN` queries (one-to-many relationships), I could rewrite them to use `CROSS APPLY`, but they always give me equivalent execution plans. 

Can anyone give me a good example of when `CROSS APPLY` makes a difference in those cases where `INNER JOIN` will work as well?


---

**Edit:**

Here&#39;s a trivial example, where the execution plans are exactly the same.  (Show me one where they differ and where `cross apply` is faster/more efficient)

    create table Company (
    	companyId int identity(1,1)
    ,	companyName varchar(100)
    ,	zipcode varchar(10) 
    ,	constraint PK_Company primary key (companyId)
    )
    GO
    
    create table Person (
    	personId int identity(1,1)
    ,	personName varchar(100)
    ,	companyId int
    ,	constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)
    ,	constraint PK_Person primary key (personId)
    )
    GO
    
    insert Company
    select &#39;ABC Company&#39;, &#39;19808&#39; union
    select &#39;XYZ Company&#39;, &#39;08534&#39; union
    select &#39;123 Company&#39;, &#39;10016&#39;
    
    
    insert Person
    select &#39;Alan&#39;, 1 union
    select &#39;Bobby&#39;, 1 union
    select &#39;Chris&#39;, 1 union
    select &#39;Xavier&#39;, 2 union
    select &#39;Yoshi&#39;, 2 union
    select &#39;Zambrano&#39;, 2 union
    select &#39;Player 1&#39;, 3 union
    select &#39;Player 2&#39;, 3 union
    select &#39;Player 3&#39;, 3 
    
    
    /* using CROSS APPLY */
    select *
    from Person p
    cross apply (
    	select *
    	from Company c
    	where p.companyid = c.companyId
    ) Czip
    
    /* the equivalent query using INNER JOIN */
    select *
    from Person p
    inner join Company c on p.companyid = c.companyId


  [1]: http://technet.microsoft.com/en-us/library/ms175156.aspx

When should I use CROSS APPLY over INNER JOIN?

How can I select data in the same query from two different databases that are on two different servers in SQL Server?

Selecting data from two different servers in SQL Server

I want to move a table into a specific Schema using T-SQL? I am using SQL Server 2008.

How do I move a table into a schema in T-SQL

In the SQL Server, I am trying to insert values from one table to another by using the below query:

    delete from tblTable1
    
    insert into tblTable1 select * from tblTable1_Link

I am getting the following error:

&gt; Column name or number of supplied values does not match table definition.


I am sure that both the tables have the same structure, same column names and same data types.
 

Column name or number of supplied values does not match table definition

I am creating a new table in Microsoft SQL server 2000 by writing the code instead of using the GUI, I am trying to learn how to do it &quot;the manual way&quot;.

This is the code I am actually using, and it works fine:

	CREATE TABLE &quot;attachments&quot;
	(
		&quot;attachment_id&quot; INT NOT NULL,
		&quot;load_date&quot; SMALLDATETIME NOT NULL,
		&quot;user&quot; VARCHAR(25) NOT NULL,
		&quot;file_name&quot; VARCHAR(50) NOT NULL,
		CONSTRAINT &quot;pk_attachments&quot; PRIMARY KEY (&quot;attachment_id&quot;),
		CONSTRAINT &quot;fk_users&quot; FOREIGN KEY (&quot;user&quot;) REFERENCES &quot;users&quot; (&quot;user&quot;),
		CONSTRAINT &quot;ch_load_date&quot; CHECK (&quot;load_date&quot; &lt; GETDATE())
	)

I have specified the primary key, foreign key and check constraints on their own because in this way I can define a name for them, otherwise declaring them inline would make SQL Server generate a random name, and I do not &quot;like&quot; it.

The problem arose when I tried to declare the default value constraint: looking at the informations on the internet and how Microsoft SLQ Server Management Studio creates it, I understood that it can be created both inline and on its own:

	&quot;load_date&quot; SMALLDATETIME NOT NULL DEFAULT GETDATE()

or

	CONSTRAINT &quot;df_load_date&quot; DEFAULT GETDATE() FOR &quot;load_date&quot;

The inline method works fine, but it generates as usual a random name for the constaint, the stand alone method throws an error, saying `Incorrect syntax near &#39;FOR&#39;.`.

Also, if I create the table and then `ALTER` it, the command works:

	ALTER TABLE &quot;attachments&quot;
	ADD CONSTRAINT &quot;df_load_date&quot; DEFAULT GETDATE() FOR &quot;load_date&quot;

&lt;br /&gt;
As a reference, here is the full code I am trying to execute:

	CREATE TABLE &quot;attachments&quot;
	(
		&quot;attachment_id&quot; INT NOT NULL,
		&quot;load_date&quot; SMALLDATETIME NOT NULL,
		&quot;user&quot; VARCHAR(25) NOT NULL,
		&quot;file_name&quot; VARCHAR(50) NOT NULL,
		CONSTRAINT &quot;pk_attachments&quot; PRIMARY KEY (&quot;attachment_id&quot;),
		CONSTRAINT &quot;fk_users&quot; FOREIGN KEY (&quot;user&quot;) REFERENCES &quot;users&quot; (&quot;user&quot;),
		CONSTRAINT &quot;ch_load_date&quot; CHECK (&quot;load_date&quot; &lt; GETDATE()),
		CONSTRAINT &quot;df_load_date&quot; DEFAULT GETDATE() FOR &quot;load_date&quot;
	)

&lt;br /&gt;
&lt;br /&gt;
I&#39;m totally at loss here, is what I am trying to do not possible, or I am doing something wrong?

&lt;br /&gt;
**Edit:**

David M showed how to add a named default constraint using the inline syntax, I am still looking to understand if the stand alone syntax is completely wrong or it is my fault.

Declaring a default constraint when creating a table

How can I exit in the middle of a stored procedure?

I have a stored procedure where I  want to bail out early (while trying to debug it). I&#39;ve tried calling `RETURN` and `RAISERROR`, and the sp keeps on running:

    CREATE PROCEDURE dbo.Archive_Session @SessionGUID uniqueidentifier AS

        print &#39;before raiserror&#39;
        raiserror(&#39;this is a raised error&#39;, 18, 1)
        print &#39;before return&#39;
        return -1
        print &#39;after return&#39;

    [snip]

I know it keeps running because I encounter an error further down. I don&#39;t see any of my **prints**. If I comment out the bulk of the stored procedure:

    CREATE PROCEDURE dbo.Archive_Session @SessionGUID uniqueidentifier AS

        print &#39;before raiserror&#39;
        raiserror(&#39;this is a raised error&#39;, 18, 1)
        print &#39;before return&#39;
        return -1
        print &#39;after return&#39;

       /*
         [snip]
       */

Then I don&#39;t get my error, and I see the results:

    before raiserror
    Server: Msg 50000, Level 18, State 1, Procedure Archive_Session, Line 5
    this is a raised error
    before return
    

So the question is: how do I bail out of a stored procedure in SQL Server?

SQL Server 2000: How to exit a stored procedure?

I&#39;ll use a concrete, but hypothetical, example.

Each **Order** normally has only one **line item**:

**Orders:**

&lt;!-- language: lang-none --&gt;

    OrderGUID   OrderNumber
    =========   ============
    {FFB2...}   STL-7442-1      
    {3EC6...}   MPT-9931-8A

**LineItems:**

&lt;!-- language: lang-none --&gt;

    LineItemGUID   Order ID Quantity   Description
    ============   ======== ========   =================================
    {098FBE3...}   1        7          prefabulated amulite
    {1609B09...}   2        32         spurving bearing

But occasionally there will be an order with two line items:

&lt;!-- language: lang-none --&gt;

    LineItemID   Order ID    Quantity   Description
    ==========   ========    ========   =================================
    {A58A1...}   6,784,329   5          pentametric fan
    {0E9BC...}   6,784,329   5          differential girdlespring 

Normally when showing the orders to the user:

&lt;!-- language: lang-sql--&gt;

    SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
    FROM Orders
        INNER JOIN LineItems 
        ON Orders.OrderID = LineItems.OrderID

I want to show the single item on the order. But with this occasional order containing two (or more) items, the orders would *appear* be **duplicated**:

&lt;!-- language: lang-none --&gt;

    OrderNumber   Quantity   Description
    ===========   ========   ====================
    STL-7442-1    7          prefabulated amulite
    MPT-9931-8A   32         spurving bearing
    KSG-0619-81   5          panametric fan
    KSG-0619-81   5          differential girdlespring

What I really want is to have SQL Server *just pick one*, as it will be *good enough*:

&lt;!-- language: lang-none --&gt;

    OrderNumber   Quantity   Description
    ===========   ========   ====================
    STL-7442-1    7          prefabulated amulite
    MPT-9931-8A   32         differential girdlespring
    KSG-0619-81   5          panametric fan

If I get adventurous, I might show the user, an ellipsis to indicate that there&#39;s more than one:

&lt;!-- language: lang-none --&gt;

    OrderNumber   Quantity   Description
    ===========   ========   ====================
    STL-7442-1    7          prefabulated amulite
    MPT-9931-8A   32         differential girdlespring
    KSG-0619-81   5          panametric fan, ...

So the question is how to either
 
 - eliminate &quot;duplicate&quot; rows
 - only join to one of the rows, to avoid duplication


## First attempt ##
My first naive attempt was to only join to the &quot;**TOP 1**&quot; line items:

&lt;!-- language: lang-sql --&gt;

    SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
    FROM Orders
        INNER JOIN (
           SELECT TOP 1 LineItems.Quantity, LineItems.Description
           FROM LineItems
           WHERE LineItems.OrderID = Orders.OrderID) LineItems2
        ON 1=1

But that gives the error:

&gt; The column or prefix &#39;Orders&#39; does not  
&gt; match with a table name or alias name  
&gt; used in the query.  

Presumably because the inner select doesn&#39;t see the outer table.


How to Join to first row

I know this must be simple, but how do I preface the creation of a function with a check to see if it already exists? If it exists, I want to drop and re-create it. 

How do I drop a function if it already exists?

I have two tables

Table A:

    ID
    1
    2
    3
    4

Table B:

    ID
    1
    2
    3

I have two requests:

 * I want to select all rows in table A that table B doesn&#39;t have, which in this case is row 4.
 * I want to delete all rows that table B doesn&#39;t have.

I am using SQL Server 2000.

Content Type	Original Author	Original Content on Stackoverflow
Question	scottm	View Question on Stackoverflow
Solution 1 - Sql Server	Blithe	View Answer on Stackoverflow
Solution 2 - Sql Server	casperOne	View Answer on Stackoverflow

How can I insert binary file data into a binary SQL field using a simple insert statement?

Sql Server Problem Overview

Sql Server Solutions

Solution 1 - Sql Server

Solution 2 - Sql Server

Listings in Latex with UTF-8 (or at least german umlauts)

How do you drop a default value or similar constraint in T-SQL?

Attributions