SQL to insert data (Syntax)

Posted on 2014-07-10
Last Modified: 2014-07-14
I need to insert data to table:  HomepageIcons

The problem is with the first item ("FirmId") , which its value comes from the result of the following sql:

Select firmid from HomepageIcons where IconName = 'Cases'  --- this value should be inserted in the table row along with the rest of the values as listed below (note that 'image' value has quotes in there, is the syntax right ?  


Firmid = ?  (This is the value that comes from the result of the sql above)
IconName = 'Basic Training'
Linkto = 'frames.asp?leftframe=leftframe.asp&mainframe=BT/Basictraining.asp'
row = '4'
iconorder = '99'
superuseravailable = '1'
adminavailable = '1'
image = '<img src="../Images/iconusersguide.gif" border=0>'
Linktoleftframe = 'BT/Basictraining.asp'

What I need is the full sql to insert all this values  :)

Thx for the help :)
Question by:amucinobluedot
    LVL 4

    Accepted Solution

    Your SQL SELECT statement looks correct, but it retrieves values from a table.  It does not put them into a table.

    If you need to insert these values, then you need to use the INSERT statement, like this:

    INSERT INTO HomepageIcons
    VALUES (firmid, iconname, linkto, row, icondorder, superuseravailable, adminavailable, image, linktoleftframe);
    LVL 1

    Assisted Solution

    by:Pooja Katiyar Verma
    Insert into HomepageIcons  
     Linktoleftframe )
    Firmid ,
    'Basic Training',
    '<img src="../Images/iconusersguide.gif" border=0>',
    from HomepageIcons where HomepageIcons ;
    LVL 32

    Assisted Solution

    by:Big Monty
    i would use a parameterized query and a stored procedure:

            set cmd = Server.CreateObject("ADODB.Command")
            sql = "exec sp_insertHomePageIcons ?, ?, ?, ?, ?, ?, ?, ?"
            with cmd
    		    .ActiveConnection = conn      '-- make sure you explicitly open your connection somewhere beforehand
    		    .CommandText = sql
    		    .Parameters.Append  .CreateParameter( "iconName", adVarchar, adParamInput, 50, iconName )
    		    .Parameters.Append  .CreateParameter( "linkTo", adVarchar, adParamInput, 50, linkTo )
    		    .Parameters.Append  .CreateParameter( "row", adInteger, adParamInput, , row )
    		    .Parameters.Append  .CreateParameter( "iconOrder", adVarchar, adParamInput, 50, iconOrder )
    		    .Parameters.Append  .CreateParameter( "superUser", adInteger, adParamInput, , superUserAvailable )
    		    .Parameters.Append  .CreateParameter( "admin", adInteger, adParamInput, , adminAvailable )
    		    .Parameters.Append  .CreateParameter( "image", adVarchar, adParamInput, 50, image )
    		    .Parameters.Append  .CreateParameter( "linkToLeftFrame", adVarchar, adParamInput, 50, linkToLeftFrame )
    	    end with

    Open in new window

    and your stored procedure would look like the following:

    CREATE   PROCEDURE [dbo].[sp_insertHomePageIcons] 
    	@iconName varchar( 50 ),
    	@linkName varchar( 50 ), 
    	@row int,
    	@iconOrder varchar( 50 ),
    	@superUser int,
    	@admin int,
    	@image varchar( 50 ),
    	@linkToLeftFrame varchar( 50 ),
    	set nocount on;
    	declare @firmID int;
    	select @firmID = firmid from HomepageIcons where IconName = 'Cases';
    	Insert into HomepageIcons ( Firmid, IconName, Linkto, row, iconorder, superuseravailable, adminavailable, image, Linktoleftframe )
    	values( @iconName, @linkName, @row, @iconOrder, @superUser, @admin, @image, @linkToLeftFrame );

    Open in new window

    you should perform checks on each value passed to the stored procedure, make sure the variables are have valid data, otherwise you'll get an error. you'll also need to download and include the file, which you can get here:

    finally, make sure your column types match up to the stored procedure, otherwise you'll again get errors. you'll need to specify the the correct data types and sizes in order for it work. I STRONGLY recommend having a look at this EE article that explains parameterized queries:

    Author Comment

    Thanks .. I will check it out tomorrow  morning !

    Author Comment

    I am trying the following:


    INSERT  INTO HomepageIcons
            ( Firmid ,
              IconName ,
              Linkto ,
              row ,
              iconorder ,
              superuseravailable ,
              adminavailable ,
              image ,
    VALUES  ( '1' ,
              'Basic Training' ,
              'frames.asp?leftframe=leftframe.asp&mainframe=BT/Basictraining.asp' ,
              '4' ,
              '99' ,
              '1' ,
              '1' ,
              '<img src='../Images/iconusersguide.gif' border=0>' ,


    I get an error on the code that has the '  it breaks the code, how can I insert a :  '  ?

    line:             '<img src='../Images/iconusersguide.gif' border=0>' ,

    Author Comment

    Tired this but didn't work:

     '<img src='''../Images/iconusersguide.gif''' border=0>' ,

    Tried this didn't work:

     '''<img src='../Images/iconusersguide.gif' border=0>''' ,

    Author Closing Comment

    LVL 32

    Expert Comment

    by:Big Monty
    if you use the code examples i gave, you do not need to worry about single quotes or escaping any other charactors

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now