February 3, 2010 at 1:40 pm
I need to write a stored procedure that will insert the data into 'Accounts' table from the 'company' table in a specified pattern from the query shown below.
WITH Repeats AS (
SELECT 6 AS Cnt UNION ALL
SELECT 10 AS Cnt UNION ALL
SELECT 14 AS Cnt
)
SELECT
LEFT(S.Accountnumber, R.Cnt) As leafaccntnum,
S
.CompanyName,
S
.accountType,
CASE WHEN RIGHT(S.Accountnumber, 8) = '00000000' THEN 1
WHEN RIGHT(s.Accountnumber, 4) = '0000' THEN 2
ELSE 3
END As nodeDepth
FROM
Company S
CROSS
JOIN Repeats R
order by CompanyName
----------------------
Source table structure
----------------------
CREATE TABLE Company(
[id] [int] IDENTITY(1,1) NOT NULL,
[accountNumber] [varchar](14) NOT NULL,
[companyName] [varchar](30) NOT NULL,
[emailAddress] [varchar](30) NULL,
[accountType] [char](1) NOT NULL,
[logo] [varchar](3) NULL,
) ON [PRIMARY]
------------------
Sample data
------------------
insert into Company (accountNumber,companyName,emailAddress,accountType,logo)
values ('GMILAB10320164','GENERAL Merchandise Inc','rhib@gmerchandise.com','V','GML')
---------------------------
Destination Table structure
---------------------------
CREATE TABLE Accounts(
[id] [int] IDENTITY(1,1) NOT NULL,
[accountNumber] [varchar](14) NULL,
[currlevel] [tinyint] NOT NULL,
[name] [varchar](50) NULL,
[nodeDepth] [int] NOT NULL,
[emailAddress] [varchar] (50) null,
[accountType] [char](1) NOT NULL,
[parentId] [int] NULL,
[LabelId] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] 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
ALTER TABLE Accounts WITH NOCHECK ADD CONSTRAINT [Accounts_parent_id] FOREIGN KEY([parentId])
REFERENCES Accounts ([id])
GO
ALTER TABLE Accounts CHECK CONSTRAINT [Accounts_parent_id]
GO
ALTER TABLE Accounts WITH NOCHECK ADD CONSTRAINT [Accounts_Label_id] FOREIGN KEY([LabelId])
REFERENCES [dbo].[Label] ([id])
GO
ALTER TABLE Accounts CHECK CONSTRAINT [Accounts_Label_id]
GO
-- FK Table
CREATE TABLE [dbo].[Label](
[id] [int] IDENTITY(1,1) NOT NULL,
[varchar](6) NOT NULL,
[name] [varchar] (30) null,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
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
-----------------
Sample Input Data
-----------------
insert into dbo.label(code,name) select Logo,companyname from dbo.companys
--------------------------------
Desired Output (Accounts table)
--------------------------------
Id leafaccntnum companyname Type nodeDepth labelId parentId
1 GMILAB GENERAL Merchandise Inc V 3 1 1
2 GMILAB1032 GENERAL Merchandise Inc V 3 1 2
3 GMILAB10320164GENERAL Merchandise Inc V 3 1 3
Thanks in advance....
February 3, 2010 at 2:02 pm
I'm not sure why the values of your column [parentId] are identical to the values of the ID column... Looks redundant, which is obviously not the purpose.
Please provide more details what the value of [parentId] is based on.
February 3, 2010 at 2:09 pm
I have edited the output to show the relationship between parentkeyId and id as below
Id leafaccntnum companyname Type nodeDepth labelId parentId
1 GMILAB GENERAL Merchandise Inc V 3 1 null
2 GMILAB1032 GENERAL Merchandise Inc V 3 1 1
3 GMILAB10320164GENERAL Merchandise Inc V 3 1 2
Basically, the parentkeyId of a given accountnumbers 10 digits is the id of the 6 digit number and the parentkey of the leafaccntnumber 14 digits is the id of the 10 digit accountnumber.
February 3, 2010 at 2:20 pm
Some more data as sample output to explain parentId
Id leafaccntnum companyname Type nodeDepth labelId parentId
1 GMILAB GENERAL Merchandise Inc V 3 1 null
2 GMILAB1032 GENERAL Merchandise Inc V 3 1 1
3 GMILAB10320164GENERAL Merchandise Inc V 3 1 2
4 ITILAB IT laboratories T 1 12 null
5 ITILAB2000 IT laboratories T 1 12 4
6 ITILAB20000000IT laboratories T 1 12 5
7 CGILAB Cintra Global V 2 4 null
8 CGILAB1001 Cintra Global V 2 4 7
9 CGILAB20010000Cintra Global V 2 4 8
February 3, 2010 at 3:03 pm
A few things up front:
#1: You did a good job the way you provided table defs and sample data!! 🙂 Made it easy to work on. And I'm sure you figured why I had to ask for clarification regarding the parentId column, right? 😉
#2: I would highly recommend to reevaluate your data model. You should store the id value of the Company table together with the accountNumber split instead of all company details...
#3 If you want to have the ParentId column holding NULL values you cannot use a constraint to the id column. I had to remove it to get the results you requested:
ALTER TABLE accounts DROP CONSTRAINT [Accounts_parent_id]
Now to my solution:
The major issue is to get the value for parent id in reference to the id of the row that has just been inserted.
What I'd do is to use as persisted column to get the value for parentid as id-1:
ALTER TABLE accounts ADD[parentIdNew] AS (CASE WHEN [parentid]=(1) THEN NULL ELSE [id]-(1) END) PERSISTED
This concept will require to be wrapped into a transaction and to be checked for any insert errors to make sure the data will all be inserted or none to avoid gaps within a sequence per CompanyName.
And here's the code I'd use:
;WITH Repeats AS (
SELECT 1 AS parent, 6 AS Cnt UNION ALL
SELECT 2,10 AS Cnt UNION ALL
SELECT 3,14 AS Cnt
)
INSERT INTO Accounts (
[accountNumber] ,
[name] ,
[nodeDepth],
[accountType] ,
[parentId],
[LabelId],
currlevel )
SELECT
LEFT(S.Accountnumber, R.Cnt) AS leafaccntnum,
S.CompanyName,
CASE WHEN RIGHT(S.Accountnumber, 8) = '00000000' THEN 1
WHEN RIGHT(s.Accountnumber, 4) = '0000' THEN 2
ELSE 3
END AS nodeDepth,
S.accountType,
parent AS parentid,
l.id AS LabelID,
0 AS currlevel
FROM Company S
INNER JOIN label l ON s.logo=l.code
CROSS JOIN Repeats R
ORDER BY CompanyName,parent
If you cannot guarantee the sequence of IDs per CompanyName during insert for whatever reason, then you might have to add the parent value with a second update or you'd need to create your own sequence id (making sure this will ALWAYS work...).
February 3, 2010 at 3:23 pm
Yes, I figured why you asked me for the ParentId definition. Apparently, we had a lot of ambiguity in defining it.
Also, I had already conveyed my thoughts about dropping the constraint on parentId as it holds null values.
As far the design is concerned, we are still not sure if this design is good enough, as you mentioned..so we might make some enhancements to it.
So I just wrap it in the form of a Stored Procedure right?
Thanks again...
February 3, 2010 at 3:42 pm
Yes, I'd wrap it into a procedure with proper transaction and error handling (TRY ... CATCH).
But the major question remains and needs to be considered: How can it be guaranteed to have sequential Id's during insert.
One factor will be how often this proc will be used and another one if there is any kind of a risk of having parallel processing.
February 3, 2010 at 7:02 pm
Should I then just include an update statement to get the parentId from the previously inserted Id value?
Also, this process needs to run every 30 mins, and a company record if not existing will have to be inserted to account and updated if it already exists.
Thanks...
February 4, 2010 at 8:34 am
For any given account, the parentId references the Id value of that accounts 6 digits number. This is just to create a Tree structure.
All 10 digit account numbers will have parentId's to be the ID of the First 6 digit entry that has the same values. And all 14 digit account numbers will have the parentId's as the Id of the first 10 digit entry, the hierarchy looks something like below
ID ACCTNUMBERPARENTID
10ITLAB1 NULL
11ITLAB1000110
12ITLAB10001000111
13ITLAB1 NULL
14ITLAB1000110
15ITLAB10001000211
16ITLAB1 NULL
17ITLAB1000210
18ITLAB10002000117
19ITLAB1 NULL
20ITLAB1000310
21ITLAB10003000120
22ITLAB1 NULL
23ITLAB1000310
24ITLAB10003000223
Can this be done during run time with each new insert for a company record, or will it need an update???
Thanks...
February 4, 2010 at 11:22 am
Would you mind providing some more test data to play with?
I'm thinking of a solution that might work but I'd like to test it a little further...
February 4, 2010 at 1:39 pm
Some more sample data
insert into dbo.companys(accountNumber,companyName,emailAddress,accountType,logo)
values ('HRS00100000000','HR Staffing Company','info@hrs.com','V','HRS'),
('HRS00100010000','HR Staffing North America','info@hrsna.com','V','HRS'),
('HRS00100010001','HR Staffing US','info@hrsus.com','V','HRS'),
('HRS00100020000','HR Staffing Europe','info@hrseu.com','A','HRS'),
('ABC00000000000','ABC Shipping Company','sales@abc.com','A','ABC')
insert into dbo.Label (code,name) values ('ABC','ABC Shipping Company'),('HRS','HR Staffing Company')
-------------------------------------------------------
Desired output of Accounts table after the above inserts
-------------------------------------------------------
id leafaccntnum CompanyName nodeDepth accountType parentid LabelID
----------- -------------- ------------------------------ ----------- ----------- ----------- -----------
1 ABC000 ABC Shipping Company 1 A null 2
2 ABC0000000 ABC Shipping Company 1 A 1 2
3 ABC00000000000 ABC Shipping Company 1 A 2 2
4 GMILAB GENERAL Merchandise Inc 3 V null 1
5 GMILAB1032 GENERAL Merchandise Inc 3 V 4 1
6 GMILAB10320164 GENERAL Merchandise Inc 3 V 5 1
7 HRS001 HR Staffing Company 1 V null 3
8 HRS0010000 HR Staffing Company 1 V 7 3
9 HRS00100000000 HR Staffing Company 1 V 8 3
10 HRS001 HR Staffing Europe 2 A null 3
11 HRS0010002 HR Staffing Europe 2 A 7 3
12 HRS00100020000 HR Staffing Europe 2 A 11 3
13 HRS001 HR Staffing North America 2 V null 3
14 HRS0010001 HR Staffing North America 2 V 7 3
15 HRS00100010000 HR Staffing North America 2 V 14 3
16 HRS001 HR Staffing US 3 V null 3
17 HRS0010001 HR Staffing US 3 V 7 3
18 HRS00100010001 HR Staffing US 3 V 17 3
Thanks again..
February 4, 2010 at 1:46 pm
Your post, as I see it, starts with "Some more sample data".
Is/was there anything that I am missing?
What exactly is the question you ask?
February 4, 2010 at 2:04 pm
The exact question is on this link http://qa.sqlservercentral.com/Forums/Topic859006-392-1.aspx
I provided sample data to help understand the output format needed for this..
February 4, 2010 at 2:42 pm
OK; now it makes sense.
I will leave it to Imu92 for the time being, he already has a good grasp of the problem and he asked for the additional sample.
February 6, 2010 at 12:46 pm
Did you find a working solution yet?
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply