June 12, 2011 at 5:11 am
I've tables as following,
CREATE TABLE [dbo].[PaymentItem](
[idx] [int] IDENTITY(1,1) NOT NULL,
[paymentType] [int] NOT NULL,
[amount] [decimal](10, 2) NOT NULL,
CONSTRAINT [PK_PaymentItem] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[PaymentItemD_zAgro](
[idx] [int] IDENTITY(1,1) NOT NULL,
[paymentItem] [int] NOT NULL,
[agroType] [int] NOT NULL,
[personInfo] [varchar](50) NOT NULL,
CONSTRAINT [PK_PaymentItemD_zAgro] PRIMARY KEY CLUSTERED
(
[idx] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PaymentItemD_zAgro] WITH CHECK ADD CONSTRAINT [FK_PaymentItemD_zAgro_PaymentItem] FOREIGN KEY([paymentItem])
REFERENCES [dbo].[PaymentItem] ([idx])
GO
ALTER TABLE [dbo].[PaymentItemD_zAgro] CHECK CONSTRAINT [FK_PaymentItemD_zAgro_PaymentItem]
GO
CREATE TABLE [dbo].[PaymentItemD_zBiz](
[idx] [int] IDENTITY(1,1) NOT NULL,
[paymentItem] [int] NOT NULL,
[bizInfo] [varchar](100) NOT NULL,
[personInfo] [varchar](50) NOT NULL,
CONSTRAINT [PK_PaymentItemD_zBiz] PRIMARY KEY CLUSTERED
(
[idx] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PaymentItemD_zBiz] WITH CHECK ADD CONSTRAINT [FK_PaymentItemD_zBiz_PaymentItem] FOREIGN KEY([paymentItem])
REFERENCES [dbo].[PaymentItem] ([idx])
GO
ALTER TABLE [dbo].[PaymentItemD_zBiz] CHECK CONSTRAINT [FK_PaymentItemD_zBiz_PaymentItem]
GO
CREATE TABLE [dbo].[PaymentItemD_zKWSP](
[idx] [int] IDENTITY(1,1) NOT NULL,
[paymentItem] [int] NOT NULL,
[kwspType] [int] NOT NULL,
[personInfo] [varchar](50) NOT NULL,
CONSTRAINT [PK_PaymentItemD_zKWSP] PRIMARY KEY CLUSTERED
(
[idx] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PaymentItemD_zKWSP] WITH CHECK ADD CONSTRAINT [FK_PaymentItemD_zKWSP_PaymentItem] FOREIGN KEY([paymentItem])
REFERENCES [dbo].[PaymentItem] ([idx])
GO
ALTER TABLE [dbo].[PaymentItemD_zKWSP] CHECK CONSTRAINT [FK_PaymentItemD_zKWSP_PaymentItem]
GO
I've set of data as following,
declare @data xml
set @data='<data>
<paymentItem><type>agro</type><amount>100.00</amount><info1>11</info1><info2>ironman</info2></paymentItem>
<paymentItem><type>business</type><amount>160.00</amount><info1>thor private limited</info1><info2>green lanten</info2>n</paymentItem>
<paymentItem><type>kwsp</type><amount>250.00</amount><info1>15</info1><info2>superman</info2></paymentItem>
</data>'
I need help to built T-SQL to insert above set of data into PaymentItem, PaymentItemD_zAgro, PaymentItemD_zBiz, and PaymentItemD_zKWSP
My end-result as following,
PaymentItem
idx | paymentType | amount
--------------------------------------
1 10 100.00
2 20 160.00
3 30 250.00
PaymentItemD_zAgro
idx | paymentItem | agroType | personInfo
-----------------------------------------------------
1 1 11 ironman
PaymentItemD_zBiz
idx | paymentItem | bizInfo | personInfo
--------------------------------------------------------------------
1 2 thor private limited green lanten
PaymentItemD_zKWSP
idx | paymentItem | kwspType | personInfo
-----------------------------------------------------
1 3 15 superman
As you can see
1. If Type=agro, it will insert into PaymentItem and PaymentItemD_zAgro
2. If Type=business, it will insert into PaymentItem and PaymentItemD_zBiz
3. If Type=kwsp, it will insert into PaymentItem and PaymentItemD_zKWSP
I'm really stuck
June 12, 2011 at 5:30 pm
It seems simple enough. Do four different insert queries.
1. Insert Into PaymentItems Select (some columns)
2. Insert Into PaymentItemD_zAgro Select (some columns) where Type = 'agro'
3. Insert Into PaymentItemD_zBiz Select (some columns) where Type = 'business'
4. Insert Into PaymentItemD_zKWSP Select (some columns) where Type = 'kwsp'
What am I missing? Are you trying to do this all in one step, or is the problem that you don't understand how to work with XML?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 12, 2011 at 5:56 pm
Have you considered consolidating the PaymentItemD_zAgro,PaymentItemD_zBiz & PaymentItemD_zKWSP Tables into one table and store the payment type value 'agro', 'business' & 'kwsp' in this column?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 12, 2011 at 7:26 pm
To Mr The Dixie Flatline & Mr Welsh Corgi,
Thanks for your technical info. Let me digest it. Will get back to you ASAP
June 12, 2011 at 7:30 pm
idyana (6/12/2011)
To Mr The Dixie Flatline & Mr Welsh Corgi,Thanks for your technical info. Let me digest it. Will get back to you ASAP
Payment Type appears to be an attribute (column) not an entity (table).
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 13, 2011 at 5:20 am
Welsh Corgi (6/12/2011)
Have you considered consolidating the PaymentItemD_zAgro,PaymentItemD_zBiz & PaymentItemD_zKWSP Tables into one table and store the payment type value 'agro', 'business' & 'kwsp' in this column?
Sir Welsh Corgi,
I cannot consolidating the PaymentItemD_zAgro,PaymentItemD_zBiz & PaymentItemD_zKWSP Tables into one table and store the payment type value 'agro', 'business' & 'kwsp' in this column.
It's because
1. PaymentItemD_zAgro(agroType) will get the value from agroType table. That's why it will store 11. This 11 is a Foreign Key to agroType table
2. PaymentItemD_zBiz(bizInfo) never get value from any table. That's why it will store freetext such as thor private limited
3. PaymentItemD_zKWSP(kwspType) will get the value from kwspType table. That's why it will store 15. This 15 is a Foreign Key to kwspType table
Now I'm focusing Mr The Dixie Flatline suggestion. Before start, there's some correction above table design
CREATE TABLE [dbo].[PaymentItem](
[idx] [int] IDENTITY(1,1) NOT NULL,
[paymentType] [varchar](50) NOT NULL,
[amount] [decimal](10, 2) NOT NULL,
CONSTRAINT [PK_PaymentItem] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
So far, my SQL as following,
set @data='<data>
<paymentItem><type>agro</type><amount>100.00</amount>
<info1>11</info1><info2>ironman</info2>
</paymentItem><paymentItem><type>business</type><amount>160.00</amount>
<info1>thor private limited</info1><info2>green lanten</info2>n
</paymentItem><paymentItem><type>kwsp</type><amount>250.00</amount>
<info1>15</info1><info2>superman</info2></paymentItem></data>'
select
a.b.value('type[1]','varchar(20)'),a.b.value('amount[1]','decimal(10,2)'),
a.b.value('info1[1]','varchar(20)'),a.b.value('info2[1]','varchar(50)')
from
@data.nodes('/data/paymentItem') a(b)
I need help do four different insert queries
1. Insert Into PaymentItems Select (some columns)
2. Insert Into PaymentItemD_zAgro Select (some columns) where Type = 'agro'
3. Insert Into PaymentItemD_zBiz Select (some columns) where Type = 'business'
4. Insert Into PaymentItemD_zKWSP Select (some columns) where Type = 'kwsp'
Especially, how PaymentItems(idx) will pass to
1. PaymentItemD_zAgro(paymentItem)
2. PaymentItemD_zBiz(paymentItem)
3. PaymentItemD_zKWSP(paymentItem)
I'm stuck
June 13, 2011 at 8:40 am
I have to admit that I do not like the design.
Where are you getting your data from?
In your code you always want to INSERT into the Payment Item Table.
Then you could use a CASE INSERT to insert into the appropriate table based on the value of the Type Column.
The following is an example
http://www.sqlservercentral.com/Forums/Topic916689-149-1.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 13, 2011 at 8:58 am
It is possible to have more than one person with the same name.
You can define UserName as a unique constraint but if more than one person has the same name than that will be a problem.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply