insert - 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

SQL Server 2005 Forums

Transact-SQL (2005)

insert

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-05 : 05:18:53

/*
In the last part of the below sql, you see that I am trying to add the new data but I am not sure how to do this.
Note that the schema of the two tables are identical.
Can you let me know how to do the insert of the new data in i.e. @tblNewData into @tblFinancials in the last part please.
Thanks
*/

declare @tblFinancial table
(ID int, Code varchar(20), Amount decimal(15, 2), Setting tinyint)

insert into @tblFinancial
select 1,'xyz', 23.95, 3
union
select 1,'abc', 99.45, 2
union
select 1,'abc', 87.33, 2

select 'Financials'
select * from @tblFinancial

declare @tblNewData table
(ID int, Code varchar(20), Amount decimal(15, 2), Setting tinyint)

insert into @tblNewData
select 1,'abc', 99.45, 3
union
select 1,'abc', 87.33, 3
union
select 2,'TRE', 100.34, 3
union
select 1,'KWE', 54.22, 3

select 'NewData'
select * from @tblNewdata

--Add new trades...
--insert into @tblFinancial
--select
-- nd.ID, nd.Code, nd.Amount, nd.Setting
--from
-- @tblNewData as nd
-- right join @tblFinancial as f
-- on
-- f.ID = nd.ID
-- and f.Code = nd.Code
-- and f.Amount = nd.Amount
-- and f.Setting = nd.Setting
--where

select 'inserted Data'
select * from @tblFinancial

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-05 : 05:24:39

isnt it just matter of


insert into @tblFinancial
select n.ID, n.Code , n.Amount , n.Setting from @tblNewData n
left join @tblFinancial f
on f.ID=n.ID
and f.Code=n.Code 
and f.Amount=n.Amount 
and f.Setting = n.Setting
where f.ID is null