CODEWITHSUNDEEP
sqlsql-serversql-server-2008triggers
user2514925
user2514925

Reputation: 949

How to copy records to another table based on a condition?

I have a table1 contains 4 columns 

AssociateId, chk1, chk2, chk3

and another table(table2) contains same 4 columns.

chk1, chk2, chk3
  1. Columns will have the values 'yes/no/null'
  2. If table1 contains 3 records and 2 records contains the value 'yes' for all chk1,chk2,chk3 columns, then both records have to be inserted into table2
  3. Furthermore, these 2 records should be removed from table 1.

How is it possible using SQL Server 2008?

Upvotes: 1

Views: 6692

Answers (3)

RRM
RRM

Reputation: 2679

I thought the 2nd condition translates as illustrated below:

If Table1 contains records like following,

+-------------+------+------+------+
| AssociateId | chk1 | chk2 | chk2 |
+-------------+------+------+------+
|          01 |  yes | yes  | yes  |
|          02 |  yes | yes  | yes  |
|          03 |   no | no   | yes  |
+-------------+------+------+------+

only then, records with AssociateId = 01, 02 should be copied to Table2

You could achieve the conditional INSERT in table2 using plain SQL, but additional DELETE operation on table1 is not possible in the same SQL query.

Otherwise, create a stored procedure and put both the insert and delete queries in a transaction.

Upvotes: 0

OliC
OliC

Reputation: 175

if i have unterstand your question right, i think this could work for you:

for the insert in table1 you can run this sql statement:

insert into table2 chk1,chk2, chk3 values (select chk1,chk2, chk3 from table 1 where chk1='yes' and chk2='yes' and chk3='yes')

and the you delete the records in table1

delete from table1 where chk1='yes' and chk2='yes' and chk3='yes'

Upvotes: 0

Zo Has
Zo Has

Reputation: 13038

You can use a transaction. First insert your records in table2 like

Insert into table2 (column1,column2,column3)
select column1, column2,column3 from table1 
where column1 ='YES' and column2 ='YES' and column3 ='YES';

you can then delete your records from table1 based on same condition & commit your transaction.

Upvotes: 0

Related Questions