Use stored procedure to insert some data into a table

Question

I am trying to create stored procedure that inserts some data into my table  but I m getting some errors like      Invalid Column name   For all the columns that I specified in my stored procedure  I have an IDENTITY COLUMN called ID which increments by one each time record is inserted  I also have some other columns in the table but they can be null  Here is my stored procedure and cannot figure out what I am doing wrong here     USE MYDB GO  SET ANSI NULLS ON GO  SET QUOTED IDENTIFIER ON GO CREATE PROCEDURE  dbo   sp Test     myID bigInt     myFirstName nvarchar 50      myLastName nvarchar 50      myAddress nvarchar MAX       myPort int  AS  BEGIN    SET NOCOUNT ON      BEGIN   insert into MYDB dbo MainTable  MyID  MyFirstName  MyLastName  MyAddress  MyPort  values  myID   myFirstName   myLastName   myAddress   myPort     END   END GO   Here is the table definition   USE  MYDB  GO          Object   Table  dbo   MainTable     Script Date  01 03 2013 11 17 17         SET ANSI NULLS ON GO  SET QUOTED IDENTIFIER ON GO  CREATE TABLE  dbo   MainTable        ID   bigint  IDENTITY 1 1  NOT NULL       MyID   bigint  NULL       MyFirstName   nvarchar  50  NULL       MyLastName   nvarchar  50  NULL       MyAddress   nvarchar  max  NULL       MyPort   int  NULL       MyZipCode   nchar  10  NULL       CompName   nvarchar  50  NULL   ON  PRIMARY   GO

User · Answer

if you want to populate a table in SQL SERVER you can use while statement as follows:

declare @llenandoTabla INT = 0;
while @llenandoTabla < 10000
begin
insert into employeestable // Name of my table
(ID, FIRSTNAME, LASTNAME, GENDER, SALARY) // Parameters of my table
VALUES 
(555, 'isaias', 'perez', 'male', '12220') //values
set @llenandoTabla = @llenandoTabla + 1;
end

Hope it helps.

User · Answer

If you have the table definition to have an IDENTITY column e g  IDENTITY 1 1  then don t include MyId in your INSERT INTO statement   The point of IDENTITY is it gives it the next unused value as the primary key value   insert into MYDB dbo MainTable  MyFirstName  MyLastName  MyAddress  MyPort  values  myFirstName   myLastName   myAddress   myPort    There is then no need to pass the  MyId parameter into your stored procedure either  So change it to   CREATE PROCEDURE  dbo   sp Test   myFirstName nvarchar 50    myLastName nvarchar 50    myAddress nvarchar MAX    myPort int  AS    If you want to know what the ID of the newly inserted record is add  SELECT   IDENTITY   to the end of your procedure   e g  http   msdn microsoft com en-us library ms187342 aspx  You will then be able to pick this up in which ever way you are calling it be it SQL or  NET   P s  a better way to show you table definision would have been to script the table and paste the text into your stackoverflow browser window because your screen shot is missing the column properties part where IDENTITY is set via the GUI   To do that right click the table  Script Table as  --   CREATE to  --  Clipboard   You can also do File or New Query Editor Window  all self explanitory  experient and see what you get

User · Answer

If you are trying to return back the ID within the scope  using the SCOPE IDENTITY   would be a better approach  I would not advice to use   IDENTITY  as this can return any ID   CREATE PROC  dbo   sp Test       myID int output     myFirstName nvarchar 50      myLastName nvarchar 50      myAddress nvarchar 50      myPort int   AS BEGIN     INSERT INTO Dvds  myFirstName  myLastName  myAddress  myPort      VALUES   myFirstName   myLastName   myAddress   myPort        SET  myID   SCOPE IDENTITY    END GO

[sql-server] Use stored procedure to insert some data into a table

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures