June 11, 2009 at 8:26 pm
Hi Let me know if you have any feedback or alternative other than below,
Posting a sample scenario code below.
--Code Starting
create table T1
(
T1_Id int not null identity(1,1),
CreatedDate datetime not null,
MonthID AS (datepart(month,[CreatedDate])) PERSISTED NOT NULL
CONSTRAINT [pk_T1] PRIMARY KEY CLUSTERED
(
[T1_id] ASC
)
)
GO
create table T2
(
T2_Id int not null identity(1,1),
T1_Id int not null,
MonthId int not null,
FirstName varchar(50),
LastName varchar(50),
CONSTRAINT [pk_T2] PRIMARY KEY CLUSTERED
(
[T2_Id] ASC
)
)
GO
ALTER TABLE [dbo].[T2] WITH CHECK ADD CONSTRAINT [FK_T2] FOREIGN KEY([T1_Id])
REFERENCES [dbo].[T1] ([T1_Id])
GO
--Declaring two table variables
--Following to hold the inserted values in T1
Declare @T1Rows table(T1_Id int,MonthID int)
--Following has the values that will go into T2
Declare @T2Values table (FirstName varchar(100),LastName varchar(100))
Insert into @T2Values values('John','Smith'),('Sam','Jackson')
--Insert into T1 and output the inserted values into table variable @T1Rows
Insert into dbo.T1(CreatedDate)
OUTPUT inserted.T1_Id, inserted.MonthID
into @T1Rows
select GETDATE()
--I was mainly wondering if the following is a good way to import data into T2 tables using the
--table variables that are not related with any keys
--It working fine,however not sure if this is the best way to it
insert into dbo.T2
(
T1_Id,
MonthID,
FirstName,
LastName
)
select a.T1_Id,
a.MonthId,
b.FirstName,
b.LastName
from @T1Rows a, @T2Values b
select * from T1
select * from T2
--Code Ending
June 12, 2009 at 12:12 pm
Hi All,
Any comments or suggetions of any other best practices to handle this would be very helpful.
Thanks
June 12, 2009 at 1:25 pm
Hi,
I tried it using cross join and that seems to be a better way to handle this.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply