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
This question is related to
sql-server
tsql
stored-procedures
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.
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
Source: Stackoverflow.com