insert 有关问题

insert 问题

SQL code

--有TAB1BH    NAME     ID1      ID21     NAME1    1001     200012     NAME2    1002     200023     NAME3    1003     200034     NAME4    1004     200045     NAME5    1005     20005.....--TAB2XH    NAME     ID1       ID21     NAME1             200012     NAME1             200012     NAME2             200023     NAME3             200034     NAME4             200045     NAME5             20005.....--通过ID2关联，得到TAB2XH    NAME     ID1       ID21     NAME1    1001     200012     NAME1    1001     200012     NAME2    1002     200023     NAME3    1003     200034     NAME4    1004     200045     NAME5    1005     20005.....

SQL code

--> 测试数据：[TAB1]if object_id('[TAB1]') is not null drop table [TAB1]create table [TAB1]([BH] int,[NAME] varchar(5),[ID1] int,[ID2] int)insert [TAB1]select 1,'NAME1',1001,20001 union allselect 2,'NAME2',1002,20002 union allselect 3,'NAME3',1003,20003 union allselect 4,'NAME4',1004,20004 union allselect 5,'NAME5',1005,20005--> 测试数据：[TAB2]if object_id('[TAB2]') is not null drop table [TAB2]create table [TAB2]([XH] int,[NAME] varchar(5),[ID1] int,[ID2] int)insert [TAB2]select 1,'NAME1',null,20001 union allselect 2,'NAME1',null,20001 union allselect 2,'NAME2',null,20002 union allselect 3,'NAME3',null,20003 union allselect 4,'NAME4',null,20004 union allselect 5,'NAME5',null,20005update [TAB2]set [ID1]=a.[ID1] from [TAB1] awhere a.NAME=[TAB2].NAME and a.ID2=[TAB2].ID2select * from [TAB2]/*XH    NAME    ID1    ID21    NAME1    1001    200012    NAME1    1001    200012    NAME2    1002    200023    NAME3    1003    200034    NAME4    1004    200045    NAME5    1005    20005*/