Help creating a Transact SQL Insert Statement | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Help creating a Transact SQL Insert Statement

Hi there can anyone help me to create a SQL Insert Statement. I dont know SQL at all. To explain, I have two web pages. search.asp and results.asp. search.asp has the following. Form Name: searchForm
Set to: Post
Action: results.asp
Text Field: Keyword
Drop Down: CategoryTable
Drop Down: Location
UserName Session Variable: MM_UserName results.asp has the following SQL which pulls all the results. SELECT SupplierName, Location, ShortDescription, TimberSpecies, CategoryTitle, Country, CustomerType
FROM Query1
WHERE TimberSpecies LIKE ‘%MMColParam%’ AND CategoryTitle LIKE ‘%MMColParam2%’ AND Location LIKE ‘%MMColParam3%’ AND CustomerType = ‘Trade/Retail’
ORDER BY CategoryTitle, TimberSpecies ASC The database & form I want to insert into. tblSearcheResults
idSearch (AutoNumber)
location (Text) "Want to insert the ‘Location’ result here"
category (Text) "Want to insert the ‘CategoryTable’ result here"
user (Text) "Want to insert the UserName Session Variable result here"
result (Text) "Want to insert the ‘Keyword’ result here" Please ask if u need more info. Mally
Declare sql varchar(1000)
Set SQL =
"Insert into [TableName] (Location,category ,user ,result )
SELECT Location, CategoryTitle,’ + SessionVariable ‘,”KeyWord”
FROM Query1
WHERE TimberSpecies LIKE ”%MMColParam%” AND CategoryTitle LIKE ”%MMColParam2%” AND Location LIKE ”%MMColParam3%” AND CustomerType = ”Trade/Retail”’
Execute SQL Hope I answered your question
—————————————-
http://dineshasanka.blogspot.com/

Yes you did, can you explain what this line is for. Have not seen it before.. "Declare sql varchar(1000)" Thanks for your help.
Above Dinesh has declare a variable of datatype variant with length 1000. You can achive the above by another method too. Its by using "sp_executeSQL". This helps to execute parameterized query too. And it has better performance as compared to "execute". But the parameter should be a unicode string containing a Transact-SQL statement or batch. Therefore "SQL" variable must be either a Unicode constant or a variable that can be implicitly converted to ntext. Regards
Sachin
It’s mixing t-sql with asp.
If it was t-sql it would be Declare @sql varchar(1000)
Set @sql =
‘Insert into [TableName] (Location,category ,user ,result )
SELECT Location, CategoryTitle,’ + @SessionVariable + ‘,”KeyWord”
FROM Query1
WHERE TimberSpecies LIKE ”%MMColParam%” AND CategoryTitle LIKE ”%MMColParam2%” AND Location LIKE ”%MMColParam3%” AND CustomerType = ”Trade/Retail”’
Execute (@sql) This is the sort of sql statement you would have in a stored procedure. It’s not very clear what you want though. It looks like you want to embed all the sql in asp (not a good idea). You talk about an insert but it looks like you want to populate a page with the results of a query?
malhyp,
If you like to know more about SQL T-SQL Statement, you can refer to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_5cl0.asp for basic understanding
"I dont know SQL at all." Learn SQL.
http://www.w3schools.com/sql/default.asp
http://www.sqlcourse.com/
http://sqlzoo.net/
KH
I hav been told that the Insert statement should not have a where clause, so I have been playing with this insert statement… Any suggestions on how to fix/trouble shoot the error? <%
set conn = Server.CreateObject("ADODB.Connection")
conn.Open MM_connSeek_STRING
SQL = "INSERT INTO tblSearchResults (result, category, location, user) " _
& " VALUES(‘" & Keyword & "’, ‘" & CategoryTable & "’, ‘" & Location & "’, ‘" & MM_UserName & "’)"
conn.Execute SQL
%> Adding this to the code, Response.Write "DEBUG SQL: " & SQL & "<HR>". Gives me a result of this. DEBUG SQL: INSERT INTO tblSearchResults (result, category, location, user) VALUES(”, ”, ”, ”)
——————————————————————————– Microsoft JET Database Engine error ‘80040e14’
Syntax error in INSERT INTO statement.
/html/results.asp, line 34
The following is the end result of the code above, just needs a little tweeking. The following insert statement enters the search results of people searching a database. The problem I have with it is that it creates an entry for each search. This causes long lists that are hard to sort through. This list can be as long as 500 lines long. Example: Spotted Gum, Furniture Timber, Vicotira
Spotted Gum, Furniture Timber, Vicotira
Spotted Gum, Furniture Timber, Victoria
Spotted Gum, Framing Timber, Victoria
Merbau, Framing Timber, Queensland. Is it possible to change or add something to the insert statement so that if it finds a duplicate search result, it just inserts a (1), so that it increases the value. End result would look like this. Spotted Gum, Furniture Timber, Vicotira 3
Spotted Gum, Framing Timber, Victoria 1
Merbau, Framing Timber, Queensland 1 I have inserted an extra row in the database called "searched". Meaning, "searched" 3 times. If I have to I am willing to get rid of the username insert. So the code below would need something added to it saying, insert ("Keyword"), ("CategoryTable"), ("Location"). If you find these results, just add a 1 in the "searched" column. <%
Keyword = request.form("Keyword")
CategoryTable = request.form("CategoryTable")
Location = request.form("Location")
User = Session("MM_UserName")
%>
<%
set conn = Server.CreateObject("ADODB.Connection")
conn.Open MM_connSeek_STRING
SQL = "INSERT INTO tblSearchResults ([result], [category], [location], [user]) " _
& " VALUES(‘" & Keyword & "’, ‘" & CategoryTable & "’, ‘" & Location & "’, ‘" & User & "’)"
conn.Execute SQL
%> Please ask if you need more info. Mally.
]]>