Scripting error. - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Scripting error.

Author

Topic

simonb256
Starting Member

2 Posts

Posted - 2008-01-16 : 06:49:07

I currently have an odd requirement esp as its my first ever SQL script.

At present ignoring all else there are two tables:

USE [Live2]

GO

/****** Object:  Table [dbo].[Person]    Script Date: 01/16/2008 10:08:05 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Person](

      [Pers_PersonId] [int] NOT NULL,

      [Pers_CompanyId] [int] NULL,

      [Pers_PrimaryAddressId] [int] NULL,

      [Pers_PrimaryUserId] [int] NULL,

      [Pers_Salutation] [nchar](10) NULL,

      [Pers_FirstName] [nchar](30) NULL,

      [Pers_LastName] [nchar](40) NULL,

      [Pers_MiddleName] [nchar](30) NULL,

      [Pers_Suffix] [nchar](20) NULL,

      [Pers_Gender] [nchar](6) NULL,

      [Pers_Title] [nchar](40) NULL,

      [Pers_TitleCode] [nchar](40) NULL,

      [Pers_Department] [nchar](30) NULL,

      [Pers_Status] [nchar](40) NULL,

      [Pers_Source] [nchar](40) NULL,

      [Pers_Territory] [nchar](40) NULL,

      [Pers_WebSite] [nchar](40) NULL,

      [Pers_MailRestriction] [nchar](40) NULL,

      [Pers_PhoneCountryCode] [nchar](5) NULL,

      [Pers_PhoneAreaCode] [nchar](20) NULL,

      [Pers_PhoneNumber] [nchar](30) NULL,

      [Pers_EmailAddress] [nchar](255) NULL,

      [Pers_FaxCountryCode] [nchar](5) NULL,

      [Pers_FaxAreaCode] [nchar](20) NULL,

      [Pers_FaxNumber] [nchar](30) NULL,

      [Pers_CreatedBy] [int] NULL,

      [Pers_CreatedDate] [datetime] NULL,

      [Pers_UpdatedBy] [int] NULL,

      [Pers_UpdatedDate] [datetime] NULL,

      [Pers_TimeStamp] [datetime] NULL,

      [Pers_Deleted] [tinyint] NULL,

      [Pers_LibraryDir] [nvarchar](255) NULL,

      [Pers_SegmentID] [int] NULL,

      [Pers_ChannelID] [int] NULL,

      [Pers_UploadDate] [datetime] NULL,

      [pers_SecTerr] [int] NULL,

      [Pers_WorkflowId] [int] NULL,

      [pers_Dear] [nchar](40) NULL,

      [pers_DMCCoID] [nchar](60) NULL,

      [pers_Legacy_Contact] [nchar](119) NULL,

      [pers_Accommodation] [nvarchar](40) NULL,

      [pers_Group] [nchar](119) NULL,

      [pers_Recruitment] [nvarchar](40) NULL,

      [pers_Training] [nvarchar](40) NULL,

      [pers_dob] [nchar](8) NULL,

      [pers_origin] [nvarchar](40) NULL,

      [pers_OCR] [numeric](24, 6) NULL,

      [pers_level] [nvarchar](40) NULL,

      [pers_booked] [datetime] NULL,

      [pers_advisor] [int] NULL,

      [pers_learnertype] [nvarchar](40) NULL,

      [pers_qualification] [nvarchar](40) NULL,

      [pers_SecondNVQ] [nvarchar](40) NULL,

      [pers_learnerNo] [numeric](24, 6) NULL,

      [pers_inductdate] [datetime] NULL,

      [pers_initial_iag] [datetime] NULL,

      [pers_MidPt_iag] [datetime] NULL,

      [pers_exit_iag] [datetime] NULL,

      [pers_lmt_advisor] [int] NULL,

      [pers_Type_c] [nvarchar](40) NULL,

      [pers_fund_type] [nvarchar](40) NULL,

      [pers_inductionpack] [datetime] NULL

) ON [PRIMARY]

Also:


 

USE [Live2]

GO

/****** Object:  Table [dbo].[Person_Link]    Script Date: 01/16/2008 10:00:31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Person_Link](

      [PeLi_PersonLinkId] [int] NOT NULL,

      [PeLi_PersonId] [int] NOT NULL,

      [PeLi_CompanyID] [int] NULL,

      [PeLi_Type] [nchar](40) NULL,

      [PeLi_CreatedBy] [int] NULL,

      [PeLi_CreatedDate] [datetime] NULL,

      [PeLi_UpdatedBy] [int] NULL,

      [PeLi_UpdatedDate] [datetime] NULL,

      [PeLi_TimeStamp] [datetime] NULL,

      [PeLi_Deleted] [tinyint] NULL

) ON [PRIMARY]

My current script is:


INSERT INTO Person_Link
                      (PeLi_PersonLinkId, PeLi_PersonId, PeLi_CompanyID, PeLi_Type, PeLi_CreatedBy, PeLi_CreatedDate, PeLi_UpdatedBy, PeLi_UpdatedDate, 
                      PeLi_TimeStamp, PeLi_Deleted)
SELECT     MAX(pl.PeLi_PersonLinkId) + 1 AS Expr1, pr.Pers_PersonId, '92929' AS Expr2, NULL AS Expr3, '1' AS Expr4, NULL AS Expr5, '1' AS Expr6, NULL 
                      AS Expr7, NULL AS Expr8, NULL AS Expr9
FROM         Person AS pr INNER JOIN
                      Person_Link AS pl ON pr.Pers_PersonId = pl.PeLi_PersonId
WHERE     (pr.Pers_CompanyId IS NULL)
GROUP BY pr.Pers_PersonId

The goal is for every row in the person table which does not have a companyID insert a row to the PersonLink table with a company ID of 92929.
Also the colum PersonLinkID in the PersonLink table does not auto ascend.

Can someone kindly point the errors of my ways?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 07:08:29

[code]DECLARE @MaxPersonLinkID int
SELECT @MaxPersonLinkID =MAX(PeLi_PersonLinkId)
FROM Person_Link
SELECT @MaxPersonLinkID=ISNULL(@MaxPersonLinkID,0)[/code]
once you get this, you can use two methods depending on whether you use SQL 2000 or 2005
sql 2005:-
[code]INSERT INTO Person_Link
(PeLi_PersonLinkId, PeLi_PersonId, PeLi_CompanyID, PeLi_Type, PeLi_CreatedBy, PeLi_CreatedDate, PeLi_UpdatedBy, PeLi_UpdatedDate,
PeLi_TimeStamp, PeLi_Deleted)
SELECT @MaxPersonLinkID + t.RowNo AS Expr1, t.Pers_PersonId, '92929' AS Expr2, NULL AS Expr3, '1' AS Expr4, NULL AS Expr5, '1' AS Expr6, NULL
AS Expr7, NULL AS Expr8, NULL AS Expr9
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY pr.Pers_PersonId) AS RowNo,
pr.Pers_PersonId FROM
Person AS pr
WHERE (pr.Pers_CompanyId IS NULL)
GROUP BY pr.Pers_PersonId)t[/code]

sql 2000:-
[code]CREATE TABLE #Temp
(
RowNo int IDENTITY(1,1),
PersonID int)

INSERT INTO #Temp
SELECT pr.Pers_PersonId FROM
Person AS pr
WHERE (pr.Pers_CompanyId IS NULL)
GROUP BY pr.Pers_PersonId

INSERT INTO Person_Link
(PeLi_PersonLinkId, PeLi_PersonId, PeLi_CompanyID, PeLi_Type, PeLi_CreatedBy, PeLi_CreatedDate, PeLi_UpdatedBy, PeLi_UpdatedDate,
PeLi_TimeStamp, PeLi_Deleted)
SELECT @MaxPersonLinkID + t.RowNo AS Expr1, t.PersonId, '92929' AS Expr2, NULL AS Expr3, '1' AS Expr4, NULL AS Expr5, '1' AS Expr6, NULL
AS Expr7, NULL AS Expr8, NULL AS Expr9
FROM #Temp t[/code]

simonb256
Starting Member

2 Posts

Posted - 2008-01-16 : 07:20:44

Thanks for that.

The way I worked round the problem was to temporarily change the table to "auto increment" on the pers.PersonLinkId field and then use the following:


INSERT INTO Person_Link
                      (PeLi_PersonId, PeLi_CompanyID, PeLi_Type, PeLi_CreatedBy, PeLi_CreatedDate, PeLi_UpdatedBy, PeLi_UpdatedDate, PeLi_TimeStamp, 
                      PeLi_Deleted)
SELECT     Pers_PersonId, '92929' AS Expr2, NULL AS Expr3, '1' AS Expr4, NULL AS Expr5, '1' AS Expr6, NULL AS Expr7, NULL AS Expr8, NULL AS Expr9
FROM         Person AS pr
WHERE     (Pers_CompanyId IS NULL)

Then changed it back to non auto incrementing.

I came back to update and found your reply. Thank you greatly for your assistance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources