SQL 批量插入问题，求高手解决
问题描述
我想把 从A表和B表中查询出来的数据插入C表
A表的结构如下
ID UserName
1 600351
B表的结构如下
ID Code
1 7708893
2 7708894
3 7708895
4 7708896
5 7708897
6 7708898
C表的结构
如下
id aid code
我想把A把UserName和B表的Code批量插入到C表最终想要结果
id aid code
1 600351 7708893
2 600351 7708894
3 600351 7708895
4 600351 7708896
5 600351 7708897
6 600351 7708898 SQL?
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-23 15:57:06
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] int,[UserName] int)
insert [a]
select 1,600351
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([ID] int,[Code] int)
insert [b]
select 1,7708893 union all
select 2,7708894 union all
select 3,7708895 union all
select 4,7708896 union all
select 5,7708897 union all
select 6,7708898
--------------开始查询--------------------------
INSERT INTO C(id, aid , code)
select id,(SELECT username FROM a)aid,code
from [b]
----------------结果----------------------------
/*
*/
CREATE TABLE #UserInfo
(
IDINT
,UserNameVARCHAR(20)
)
CREATE TABLE #UserCode
(
IDINT
,CodeVARCHAR(20)
)
CREATE TABLE #UserResult
(
ID INT
,AId VARCHAR(20)
,CodeVARCHAR(20)
)
INSERT INTO #UserInfo
VALUES(1,'600351')
INSERT INTO #UserCode
SELECT 1, '7708893' UNION ALL
SELECT 2, '7708894' UNION ALL
SELECT 3, '7708895' UNION ALL
SELECT 4, '7708896' UNION ALL
SELECT 5, '7708897' UNION ALL
SELECT 6, '7708898'
INSERT INTO #UserResult
SELECT ID
,(SELECT UserName FROM #UserInfo WHERE ID=1) AS AId
,Code
FROM #UserCode
SELECT * FROM #UserResult
/*
IDAIdCode
16003517708893
26003517708894
36003517708895
46003517708896
56003517708897
66003517708898
*/
create table
#A(ID INT,UserName varchar(10));
insert #A
SELECT 1,'600351'
--union all select 2,'600352'
create table #B
([ID] int,[Code] int)insert #B
select 1,'7708893' union all
select 2,'7708894' union all
select 3,'7708895' union all
select 4,'7708896' union all
select 5,'7708897' union all
select 6,'7708898'
select ab.ID , a.UserName ,ab.Code from
#A a
cross apply (select * from #B b ) AB
drop table #A
drop table #B
