November 30, 2015 at 2:02 pm
How to insert data into table based on another table?
If the columnA in table one has some value then insert into table B
November 30, 2015 at 2:20 pm
PJ_SQL (11/30/2015)
How to insert data into table based on another table?If the columnA in table one has some value then insert into table B
The answer is the same as to the question "how to answer the question that is missing";-)
😎
On a more serious note, please provide all the information needed and rephrase the question accordingly, there is no way anyone can even start to guess the answer as it stands.
November 30, 2015 at 2:24 pm
Here's the scenario:
I have tableA with columns A,B,C
if in tableA column B has some value other than null
then insert into tableB, so how do I create trigger for this?
November 30, 2015 at 2:31 pm
PJ_SQL (11/30/2015)
Here's the scenario:I have tableA with columns A,B,C
if in tableA column B has some value other than null
then insert into tableB, so how do I create trigger for this?
By creating a trigger. https://msdn.microsoft.com/en-us/library/ms189799.aspx
I suspect however you would like more information that that. We can help and many of us are really good at t-sql. On the other hand, most of us are pretty lousy mind readers. If you actually post the tables and what you want to happen we can help. Otherwise we are just guessing.
Please take a few minutes and read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 30, 2015 at 2:31 pm
Still far too vague
If a row is inserted into table A and column B has some value other than null, then insert into another table?
If Column B is updated to a value other than null?
Something else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2015 at 2:40 pm
Here's what my data looks like:
Col A, B and C in table A:
A BC
4460636543NULL
4431076543NULL
3034 1152NULL
4434516543NULL
4405016543NULL
9973 5152005-10-04 14:34:00.673
106505152005-10-04 14:34:19.953
106515152005-10-04 14:35:09.343
152895112005-10-25 11:30:31.227
153345112005-10-25 11:30:50.600
What I need is to insert values to Table B only if the col C in table A has some value and is not null.
Thank you in advance.
November 30, 2015 at 2:45 pm
PJ_SQL (11/30/2015)
Here's what my data looks like:Col A, B and C in table A:
A BC
4460636543NULL
4431076543NULL
3034 1152NULL
4434516543NULL
4405016543NULL
9973 5152005-10-04 14:34:00.673
106505152005-10-04 14:34:19.953
106515152005-10-04 14:35:09.343
152895112005-10-25 11:30:31.227
153345112005-10-25 11:30:50.600
What I need is to insert values to Table B only if the col C in table A has some value and is not null.
Thank you in advance.
Still vague, especially since you're not providing any information about when the trigger should fire.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 30, 2015 at 2:50 pm
When Col C(which is of datatype datetime) in table A is updated from null to some date then insert the data to table B.
November 30, 2015 at 2:56 pm
PJ_SQL (11/30/2015)
When Col C(which is of datatype datetime) in table A is updated from null to some date then insert the data to table B.
And what if the non null value in Col C is updated to a null value or a different non null value?
November 30, 2015 at 2:59 pm
Only if Col C is updated to some non-null values.
November 30, 2015 at 3:11 pm
PJ_SQL (11/30/2015)
Only if Col C is updated to some non-null values.
Which values do you want? All columns or just column c? If you are not willing to put in some effort for your problem how do you expect other people to? I coulr write your trigger in my sleep but it is not at all clear what you really want to do here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 30, 2015 at 3:15 pm
Sorry for not being clear.
I need all the values in A,B and C in table B.
November 30, 2015 at 3:18 pm
Pulling hen's teeth, but based on what little detail you have been willing to provide, does the following help?
create table dbo.FirstTable(
ColA int,
ColB int,
ColC datetime null);
create table dbo.SecondTable(
ColA int,
ColB int,
ColC datetime null);
go
create trigger dbo.DataMove on dbo.FirstTable for insert, update
as
insert into dbo.SecondTable(ColA,ColB,Colc)
select ins.ColA, ins.ColB, ins.ColC
from inserted ins
where ins.ColC > '' or ins.ColC <> (select del.ColC from deleted del where del.ColA = ins.ColA);
go
insert into dbo.FirstTable(ColA,ColB,ColC)
values
(446063,6543,NULL),
(443107,6543,NULL),
(3034 ,1152,NULL),
(443451,6543,NULL),
(440501,6543,NULL),
(9973 ,515 ,'2005-10-04 14:34:00.673'),
(10650 ,515 ,'2005-10-04 14:34:19.953'),
(10651 ,515 ,'2005-10-04 14:35:09.343'),
(15289 ,511 ,'2005-10-25 11:30:31.227'),
(15334 ,511 ,'2005-10-25 11:30:50.600');
select * from dbo.FirstTable;
select * from dbo.SecondTable;
update dbo.FirstTable set
ColC = getdate()
where ColA = 3034;
select * from dbo.FirstTable;
select * from dbo.SecondTable;
I am sure there is more involved, but you really haven't been forth coming with any details.
November 30, 2015 at 3:27 pm
Lynn Pettis (11/30/2015)
Pulling hen's teeth, but based on what little detail you have been willing to provide, does the following help?
create table dbo.FirstTable(
ColA int,
ColB int,
ColC datetime null);
create table dbo.SecondTable(
ColA int,
ColB int,
ColC datetime null);
go
create trigger dbo.DataMove on dbo.FirstTable for insert, update
as
insert into dbo.SecondTable(ColA,ColB,Colc)
select ins.ColA, ins.ColB, ins.ColC
from inserted ins
where ins.ColC > '' or ins.ColC <> (select del.ColC from deleted del where del.ColA = ins.ColA);
go
insert into dbo.FirstTable(ColA,ColB,ColC)
values
(446063,6543,NULL),
(443107,6543,NULL),
(3034 ,1152,NULL),
(443451,6543,NULL),
(440501,6543,NULL),
(9973 ,515 ,'2005-10-04 14:34:00.673'),
(10650 ,515 ,'2005-10-04 14:34:19.953'),
(10651 ,515 ,'2005-10-04 14:35:09.343'),
(15289 ,511 ,'2005-10-25 11:30:31.227'),
(15334 ,511 ,'2005-10-25 11:30:50.600');
select * from dbo.FirstTable;
select * from dbo.SecondTable;
update dbo.FirstTable set
ColC = getdate()
where ColA = 3034;
select * from dbo.FirstTable;
select * from dbo.SecondTable;
I am sure there is more involved, but you really haven't been forth coming with any details.
I think we need an additional condition in the where clause:
and ins.ColC IS NOT NULL
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 30, 2015 at 3:28 pm
Alvin Ramard (11/30/2015)
Lynn Pettis (11/30/2015)
Pulling hen's teeth, but based on what little detail you have been willing to provide, does the following help?
create table dbo.FirstTable(
ColA int,
ColB int,
ColC datetime null);
create table dbo.SecondTable(
ColA int,
ColB int,
ColC datetime null);
go
create trigger dbo.DataMove on dbo.FirstTable for insert, update
as
insert into dbo.SecondTable(ColA,ColB,Colc)
select ins.ColA, ins.ColB, ins.ColC
from inserted ins
where ins.ColC > '' or ins.ColC <> (select del.ColC from deleted del where del.ColA = ins.ColA);
go
insert into dbo.FirstTable(ColA,ColB,ColC)
values
(446063,6543,NULL),
(443107,6543,NULL),
(3034 ,1152,NULL),
(443451,6543,NULL),
(440501,6543,NULL),
(9973 ,515 ,'2005-10-04 14:34:00.673'),
(10650 ,515 ,'2005-10-04 14:34:19.953'),
(10651 ,515 ,'2005-10-04 14:35:09.343'),
(15289 ,511 ,'2005-10-25 11:30:31.227'),
(15334 ,511 ,'2005-10-25 11:30:50.600');
select * from dbo.FirstTable;
select * from dbo.SecondTable;
update dbo.FirstTable set
ColC = getdate()
where ColA = 3034;
select * from dbo.FirstTable;
select * from dbo.SecondTable;
I am sure there is more involved, but you really haven't been forth coming with any details.
I think we need an additional condition in the where clause:
and ins.ColC IS NOT NULL
Why? If ins.ColC > '' it isn't NULL.
I could seeing changing it to ins.ColC is not null.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply