怎么按需求从一个表的内容复制到另一个表
怎么按需求从一个表的内容复制到另一个表
tb1：
ID NAME Code
12 xx w
13 dd x
14 ee r
15 cc g
16 vv e
.
.
要求从某一个ID开始按需求的数量输出到另一个表tb2中
比如从ID 13开始输出2行到TB2
tb2:
13 dd x
14 ee r
比如从ID 14开始输出3行到TB2
tb2:
14 ee r
15 cc g
16 vv e
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-08-13 11:47:11
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--Jun 10 2013 20:09:10
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[tb1]
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([ID] int,[NAME] varchar(2),[Code] varchar(1))
insert [tb1]
select 12,'xx','w' union all
select 13,'dd','x' union all
select 14,'ee','r' union all
select 15,'cc','g' union all
select 16,'vv','e'
--------------开始查询--------------------------
DECLARE @beginid INT
DECLARE @num INT
SET @beginid=14
SET @num=3
select * ,'全部'
from [tb1]
WHERE id BETWEEN @beginid AND @beginid+@num
UNION ALL
select * ,'第一行'
from [tb1]
WHERE id = @beginid
UNION ALL
select * ,'最后一行'
from [tb1]
WHERE id = @beginid+@num
----------------结果----------------------------
/*
ID NAME Code
----------- ---- ---- --------
14 ee r 全部
15 cc g 全部
16 vv e 全部
14 ee r 第一行
*/
DECLARE @count INT, @beginId INT
SELECT @count = 3, @beginId = 14
INSERT INTO tb2( id, name, code)
SELECT TOP(@count) id, name, code
FROM tb1 WHERE id >= @beginId
UNION
SELECT * FROM
(
SELECT TOP(1) id, name, code
FROM tb1
ORDER BY id
) a
UNION
SELECT * FROM
(
SELECT TOP(1) id, name, code
FROM tb1
ORDER BY id DESC
) b
ORDER BY id
--打开高级选项,允许即席查询
EXEC SP_CONFIGURE 'show advanced options',1
RECONFIGURE
EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries',1
RECONFIGURE
GO
DECLARE @ip varchar(100), @loginname varchar(100), @password varchar(100), @sql NVARCHAR(MAX)
SELECT @ip = '127.0.0.1,1433', @loginname = 'sa', @password = '1q2w3e4R'
SET @sql = N'
INSERT INTO dbo.T2
SELECT *
FROM OPENDATASOURCE
(
''SQLOLEDB'',
''Persist Security Info=False;User ID='+ @loginname +';Password='+ @password +';Initial Catalog=your_database_name;Data Source='+@ip+'''
).your_database_name.dbo.t1 T
'
EXEC (@sql)
--关闭选项
EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries',0
RECONFIGURE
EXEC SP_CONFIGURE 'show advanced options',0
RECONFIGURE
GO
