none
How to insert a new row into a table in SQL

    Question

  • Hello team,

    I need to insert a row to a table, I need the SQL of this.

    I appreciate your help.

    GGGGGNNNNN


    GGGGGNNNNN

    Saturday, February 09, 2013 9:55 AM

Answers

  • Ok, this makes a lot more sense, and I should have probably figured out what you wanted to do, except you never mentioned anything about SQL Server until your last post. 

    So, copy the SQL from MS Access and paste it into SQL Server: 

    Copy the SQL you posted above.  Open SQL Server and click the small '+' sign to open the DB you want to work with.  Click the small drop down arrow in the upper left to MAKE SURE you are working with the appropriate DB.  Click New Query.  Paste the SQL here.  Hit F5.  Click the small '+' sign next to Tables; you should see your Table named 'Stores' right there.  If not, right-click Tables, click Refresh, and you will see it. 

    Do the same with the second SQL script you posted.  Copy, and paste into the large white window in SQL Server.  Hit F5.  Right-click on the Table named 'Stores' and click 'Select Top 1000 Rows'.  That's it.

    Please understand SQL is not 100% compatibale b/w MS Access and SQL Server.  Maybe 90% - 95% of the time you can copy the SQL from Access to SQL Server; maybe 80% - 85% of the time you can copy the SQL from SQL Server to Access.  For simple things, it should almost always work.  For really complex things, you would probably be better served by building the query from scratch once you are inside the Access application, or the Server itself.


    Ryan Shuell

    Tuesday, February 12, 2013 1:44 PM

All replies

  • Hello team,

    I need to insert a row to a table, I need the SQL of this.

    I appreciate your help.

    GGGGGNNNNN


    GGGGGNNNNN

    Hi GGGGGNNNNN,

    See your Help on "INSERT INTO"

    Imb.

    Saturday, February 09, 2013 10:36 AM
  • Then it asks for values,

    I have to bring the name of the table, this gives me error.

    This doesn't work,

    GGGGGNNNNN


    GGGGGNNNNN

    Sunday, February 10, 2013 1:56 AM
  • You need to run an INSERT INTO Query.  Of course, the exact SQL will depend on your specific setup, but it will look somewhat like the sample here:

    http://www.blueclaw-db.com/accessquerysql/sql_insert_into.htm

    Perhaps the easiest way to do this from scratch is to create a very simple Select Query.  Right-click > Design View > Append > Choose the Table that you will Insert into > click OK.  Now right click the Query, and click SQL.  You will see the 'Insert Into' right at the top if the SQL statement.  Play around with that for a bit and see how you get along.  Finally, just as a friendly warning, make a backup of your DB before you start fiddling around with it.  When trying new things, you always want to have a backup of the original date, just in case MS Access does something that you won't expect it to do (Access always does exactly what you TELL it to do, but it does not necessarily do what you THINK it will do). 


    Ryan Shuell


    Monday, February 11, 2013 3:49 AM
  • Hello,

    You would say now right click the query, which part of query should I right click on?

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, February 12, 2013 4:37 AM
  • The table for the new - does it exist or not?   If it does not exist then use Insert Into, otherwise use an append query.
    Tuesday, February 12, 2013 4:57 AM
  • Insert Into vs Append:

    Use INSERT when you're loading into an empty table, and APPEND when adding rows to a table that (might) contains data (that you want to keep).

    APPEND will still work if your table is empty.  INSERT might be safer if you're expecting the table to be empty, as it will error if that isn't true, possibly avoiding unexpected results (particularly if you don't notice and don't get other errors like unique index constraint violations) and/or a post-load data cleanse.

    Just play around with it.  You'll see how these things work.


    Ryan Shuell


    • Edited by ryguy72 Tuesday, February 12, 2013 5:07 AM
    Tuesday, February 12, 2013 5:07 AM
  • it exists and I have created the table with SQL in Microsoft SQL 2008,

    This is the code:

    CREATE TABLE Stores
    (
      StoreCode    char(4)      NOT NULL PRIMARY KEY,
      Name         varchar(40)  NOT NULL,
      Address      varchar(40)  NULL,
      City         varchar(20)  NOT NULL,
      State        char(2)      NOT NULL,
      Zip          char(5)      NOT NULL
    );

    I have inserted values:

    INSERT INTO Stores
    (StoreCode, Name, Address, city, State, Zip)
    VALUES
    ('TST2', 'Test Store',' ','HERE', 'NY', '00319')

    Now I want to insert a blank code

    My prof in community college said that when we don't know what the code is, we need to go Microsoft Access and create a query in design view and copy and paste the query into Microsoft SQL.

    I hope this helps.

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, February 12, 2013 5:22 AM
  • Ok, I just ran your SQL (nice job, by the way).  So you create a Table and insert a row of data into the Table.  That works fine.  What seems to be the problem now?

    You said this; 'create a query in design view'.  That makes sense.  You actually did everything in SQL, but you cold have just as well done the same in Design View.  Next you say, 'copy and paste the query into Microsoft SQL'.  That doesn't really make any sense.  What are you actually doing?  What is the ultimat objective here?


    Ryan Shuell

    Tuesday, February 12, 2013 5:29 AM
  • sorry, it might be confusing. I mean copy and paste the SQL code from Microsoft Access to Microsoft SQL server 2008. this is what I mean.

    when I play to get the option "insert/ append in the query window in Microsoft Access, it doesn't give me the option to insert.

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Tuesday, February 12, 2013 5:56 AM
  • Ok, this makes a lot more sense, and I should have probably figured out what you wanted to do, except you never mentioned anything about SQL Server until your last post. 

    So, copy the SQL from MS Access and paste it into SQL Server: 

    Copy the SQL you posted above.  Open SQL Server and click the small '+' sign to open the DB you want to work with.  Click the small drop down arrow in the upper left to MAKE SURE you are working with the appropriate DB.  Click New Query.  Paste the SQL here.  Hit F5.  Click the small '+' sign next to Tables; you should see your Table named 'Stores' right there.  If not, right-click Tables, click Refresh, and you will see it. 

    Do the same with the second SQL script you posted.  Copy, and paste into the large white window in SQL Server.  Hit F5.  Right-click on the Table named 'Stores' and click 'Select Top 1000 Rows'.  That's it.

    Please understand SQL is not 100% compatibale b/w MS Access and SQL Server.  Maybe 90% - 95% of the time you can copy the SQL from Access to SQL Server; maybe 80% - 85% of the time you can copy the SQL from SQL Server to Access.  For simple things, it should almost always work.  For really complex things, you would probably be better served by building the query from scratch once you are inside the Access application, or the Server itself.


    Ryan Shuell

    Tuesday, February 12, 2013 1:44 PM
  • Hello Rayan,

    What does this do? This doesn't insert a new Row.

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Wednesday, February 13, 2013 5:14 AM
  • Oh, my.  OK.  You have to spend some time with this; learn the nuts and bolts.  I gave you VERY detailed instructions about what to do, and you still didn't seem to get it. Everything that you need to do is listed directly above this actual post.

    It's OK, just get a few good books about SQL, SQL Server, MS Access, and the like.  Read through all the books cover to cover; you'll figure it out.  When I was learning this stuff, a long time ago, I read dozens of books on the subject matter, and I tried ALL the examples in all the books.  Reading is one thing; doing in another.  You can't just read, you actually have to practice the stuff, based on the examples in the books.

    Good luck.


    Ryan Shuell


    • Edited by ryguy72 Wednesday, February 13, 2013 5:23 AM
    Wednesday, February 13, 2013 5:20 AM
  • Hello Ryan,

    With selecting the top 1000, can I insert a new row?

    What I need is to insert new and I need SQL for this command.

    Do you think the instruction on the top result in inserting a new row?

    Thank you for all your effort for writing all this to me.

    Regards,

    GGGGGNNNNN


    GGGGGNNNNN

    Thursday, February 14, 2013 5:59 AM
  • Hi GGGGGNNNNN,

    Sorry for the late response.

    Please try the following forum to get better support for this issue:

    Transact-SQL

    Thanks for your understanding and have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 18, 2013 7:50 AM