INSERTING DATA TO MASTER TABLE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

INSERTING DATA TO MASTER TABLE

Author

Topic

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2012-05-18 : 02:00:22

Hi All,

i have writen a sp for inserting the records for all master tables using one SP.

now same table has been changed in my AUDIT.

the normal table example is as below:

CREATE TABLE [dbo].[PNET_EMP_MASTER_STATUS](
[STATUSID] [int] IDENTITY(1,1) NOT NULL,
[STATUS] [varchar](50) NOT NULL,
[SHORT_NAME] [varchar](5) NOT NULL,
PRIMARY KEY CLUSTERED
(
[STATUSID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[STATUS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[SHORT_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

The changed master table in audit is as below:

CREATE TABLE [dbo].[PNET_EMP_MASTER_STATUS](
[STATUSID] [int] IDENTITY(1,1) NOT NULL,
[STATUS] [varchar](50) NOT NULL,
[SHORT_NAME] [varchar](5) NOT NULL,
[DataVersion] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [nvarchar](200) NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](200) NOT NULL,
[MODIFIED_USERID] [varchar](256) NULL,
[IPADDRESS] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[STATUSID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[STATUS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[SHORT_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

THE SP is as below:

CREATE PROCEDURE [dbo].[SP_MASTERTABLE_ADD]
-- The below are the input arguments for calling the procedure

@MASTER_TABLE_NAME VARCHAR(150),
@MASTER_NAME VARCHAR(100),
@MASTER_SHORTNAME VARCHAR (20),
@MASTER_APPRAISER BIT=NULL,
@MASTER_PARENT_NAME VARCHAR(150)=NULL,
@USERID VARCHAR(256),
@IPADDRESS VARCHAR(20)

AS

DECLARE @MASTER_FIELDNAME VARCHAR(150)
DECLARE @SQL VARCHAR(2000)
DECLARE @MASTER_PARENT_ID INT = NULL

SET @MASTER_TABLE_NAME = UPPER(@MASTER_TABLE_NAME)

IF (@MASTER_PARENT_NAME IS NOT NULL)
BEGIN
SELECT @MASTER_PARENT_ID = FUNCTION_DEPARTMENTID FROM PNET_MASTER_FUNCTION_DEPARTMENT WHERE UPPER(FUNCTION_DEPARTMENT) = UPPER(@MASTER_PARENT_NAME)
END

SELECT
@MASTER_FIELDNAME = COALESCE(@MASTER_FIELDNAME + ', ','') + NAME
FROM
SYS.COLUMNS
WHERE
NAME NOT LIKE '%ID%'
AND OBJECT_ID = (SELECT OBJECT_ID FROM SYS.TABLES WHERE NAME = @MASTER_TABLE_NAME)

SELECT @SQL = 'INSERT INTO ' + @MASTER_TABLE_NAME + '( ' + @MASTER_FIELDNAME + ' ) VALUES (''' + UPPER(@MASTER_NAME) + ''',''' + UPPER(@MASTER_SHORTNAME) + ''''

IF (@MASTER_TABLE_NAME LIKE '%POSITION%')
BEGIN
SELECT @SQL = @SQL + ' , ' + CAST(@MASTER_APPRAISER AS VARCHAR(3))
END
IF(@MASTER_TABLE_NAME LIKE 'CHECKLIST%')
BEGIN
SELECT @SQL = @SQL + ' , ' + CAST(@MASTER_APPRAISER AS VARCHAR(3))
END
IF (@MASTER_TABLE_NAME LIKE '%FUNCTION%' AND @MASTER_PARENT_ID IS NOT NULL)
BEGIN
SELECT @SQL = @SQL + ' , ' + CAST(@MASTER_PARENT_ID AS VARCHAR(3))
END

IF (@MASTER_TABLE_NAME LIKE '%FUNCTION%' AND @MASTER_PARENT_ID IS NULL)
BEGIN
SELECT @SQL = @SQL + ' , NULL '
END
SELECT @SQL = @SQL + ')'
EXEC (@SQL)

-- SP Ends here

if i use same sp for inserting the records it is throwing error saying there are fewer columns in the insert statement when compare to the values clause.

the modified insert statement is as below:

SELECT @SQL = 'INSERT INTO ' + @MASTER_TABLE_NAME + '( ' + @MASTER_FIELDNAME + ' ) VALUES (''' + UPPER(@MASTER_NAME) + ''',''' + UPPER(@MASTER_SHORTNAME) + ''',''' + UPPER(1) + ''',''' + CONVERT(VARCHAR(20),GETDATE()) +''',''' + UPPER(@USERID) + ''',''' + CONVERT(VARCHAR(20),GETDATE()) +''',''' + UPPER(@USERID)+''','''+ UPPER(@USERID)+''',''' + UPPER(@IPADDRESS) +''''

Thanks in Advance
Kiran Murali

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-18 : 07:10:25

Your "master" table has 3 columns, your audit table has 10. Trying to use 1 SP to do auditing for ALL tables does not sound wise to me. Why not use a trigger on each table instead?

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources