CODEWITHSUNDEEP
sqlsql-server
Travis Heeter
Travis Heeter

Reputation: 14084

SQL Server: Inserting from various sources

The simple version

What is the correct syntax of this?

INSERT INTO foo(IP, referer)
VALUES(SELECT bin FROM dbo.bar("foobar"),"www.foobar.com/test/")

I am getting syntax errors near 'SELECT' and ')'

The long version: I want to insert using a Function and a string (this is simplified, in reality there will be a few other values including datetime, ints, etc to insert along with the function result).

I have a function, itvfBinaryIPv4, which was set up to convert IPs to a binary(4) datatype to allow for easy indexing, I used this as a reference: Datatype for storing ip address in SQL Server.

So this is what I am trying to accomplish:

INSERT INTO foo (IP, referer)
VALUES(SELECT bin FROM dbo.itvfBinaryIPv4("192.65.68.201"), "www.foobar.com/test/")

However, I get syntax errors near 'SELECT' and ')'. What is the correct syntax to insert with function results and direct data?

Upvotes: 1

Views: 69

Answers (3)

marc_s
marc_s

Reputation: 755157

The INSERT command comes in two flavors:

(1) either you have all your values available, as literals or SQL Server variables - in that case, you can use the INSERT .. VALUES() approach:

INSERT INTO dbo.YourTable(Col1, Col2, ...., ColN)
VALUES(Value1, Value2, @Variable3, @Variable4, ...., ValueN)

Note: I would recommend to always explicitly specify the list of column to insert data into - that way, you won't have any nasty surprises if suddenly your table has an extra column, or if your tables has an IDENTITY or computed column. Yes - it's a tiny bit more work - once - but then you have your INSERT statement as solid as it can be and you won't have to constantly fiddle around with it if your table changes.

(2) if you don't have all your values as literals and/or variables, but instead you want to rely on another table, multiple tables, or views, to provide the values, then you can use the INSERT ... SELECT ... approach:

INSERT INTO dbo.YourTable(Col1, Col2, ...., ColN)
   SELECT
       SourceColumn1, SourceColumn2, @Variable3, @Variable4, ...., SourceColumnN
   FROM
       dbo.YourProvidingTableOrView

Here, you must define exactly as many items in the SELECT as your INSERT expects - and those can be columns from the table(s) (or view(s)), or those can be literals or variables. Again: explicitly provide the list of columns to insert into - see above.

You can use one or the other - but you cannot mix the two - you cannot use VALUES(...) and then have a SELECT query in the middle of your list of values - pick one of the two - stick with it.

Upvotes: 1

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

It should be like this -

INSERT INTO foo (IP, referer)
   SELECT bin, 'www.foobar.com/test/' 
   FROM dbo.itvfBinaryIPv4('192.65.68.201')

here assume dbo.itvfBinaryIPv4("192.65.68.201") is table valued function.

Upvotes: 1

Khadim Ali
Khadim Ali

Reputation: 2598

Haven't checked it, but the correct syntax would be:

INSERT INTO foo (IP, referer)
SELECT bin,  "www.foobar.com/test/" FROM dbo.itvfBinaryIPv4("192.65.68.201")

Upvotes: 0

Related Questions