请帮我创建多插入查询

发布于 2024-08-20 00:27:21 字数 993 浏览 2 评论 0原文

我有两个表的

create table t1(cid int, isnews int)

create table t2(nid int,cid int, isnews int)

情况是这样的：如果 t2 包含 t2.cid = t1.cid 则 t2.isnews = t1.news 且如果 t2 不包含 t1 的 cid，则应在 t2 中插入新记录，并且应在 t2 中插入 t1.cid、t1.isnews..

完整的表应在单个查询中完成...我已经完成了更新部分，但是无法执行插入部分..

更新查询：

 UPDATE    t22
SET       t22.isnews = t11.isnews 
FROM      t2 AS t22
    JOIN  t1 AS t11
    ON  t11.cid= t22.cid

我已准备好下面的光标用于插入...这样好吗？：

DECLARE @clntid INT
DECLARE @clntnewsltr INT
DECLARE clientnews CURSOR FOR 
SELECT clientid,newsLetter 
FROM clients 
WHERE clientid NOT IN (SELECT clientid FROM clientprivacy) 

OPEN clientnews  
FETCH NEXT FROM clientnews INTO @clntid,@clntnewsltr  

WHILE @@FETCH_STATUS = 0  
BEGIN  

   INSERT INTO clientprivacy (clientId,tdNewsLetters) VALUES(@clntid, @clntnewsltr)
 FETCH NEXT FROM clientnews INTO @clntid,@clntnewsltr  
END  

CLOSE clientnews  
DEALLOCATE clientnews

原文

I have got two table

create table t1(cid int, isnews int)

create table t2(nid int,cid int, isnews int)

situations is like this:
if t2 contain t2.cid = t1.cid then the t2.isnews = t1.news and
if t2 not contain cid of t1 then new record should be inserted in t2 and that t1.cid, t1.isnews should be inserted in t2..

and complete table should be done in single query... i have done the updation part but not able to do insertion part..

update query:

 UPDATE    t22
SET       t22.isnews = t11.isnews 
FROM      t2 AS t22
    JOIN  t1 AS t11
    ON  t11.cid= t22.cid

i have prepared below cursor for insert... is it good? :

DECLARE @clntid INT
DECLARE @clntnewsltr INT
DECLARE clientnews CURSOR FOR 
SELECT clientid,newsLetter 
FROM clients 
WHERE clientid NOT IN (SELECT clientid FROM clientprivacy) 

OPEN clientnews  
FETCH NEXT FROM clientnews INTO @clntid,@clntnewsltr  

WHILE @@FETCH_STATUS = 0  
BEGIN  

   INSERT INTO clientprivacy (clientId,tdNewsLetters) VALUES(@clntid, @clntnewsltr)
 FETCH NEXT FROM clientnews INTO @clntid,@clntnewsltr  
END  

CLOSE clientnews  
DEALLOCATE clientnews

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

硪扪都還晓 2024-08-27 00:27:21

我认为这就是您所追求的事情：

--INSERT t2 (cid, isnews)
SELECT t1.cid, t1.isnews
FROM t1
    LEFT JOIN t2 ON t1.cid = t2.cid
WHERE t2.cid IS NULL

我已经注释掉了 INSERT 行 - 我建议您首先运行 SELECT 本身来检查它是否给出了正确的结果（来自 t1 的所有记录都没有t2 中有一个匹配的 cid）。

我假设 t2.nid 是 IDENTITY 列。

I think this is the kind of thing you're after:

--INSERT t2 (cid, isnews)
SELECT t1.cid, t1.isnews
FROM t1
    LEFT JOIN t2 ON t1.cid = t2.cid
WHERE t2.cid IS NULL

I've commented out the INSERT line - I recommend you run the SELECT on it's own first to check it does give you the correct result (all records from t1 that don't have a matching cid in t2).

I've assumed t2.nid is an IDENTITY column.

回复收藏 0 原文

甩你一脸翔 2024-08-27 00:27:21

如果没有游标，你的情况会好得多:)游标在大型数据集中运行需要更长的时间。

确实可以使用 LEFT JOIN，但也可以在 WHERE 子句中使用 SELECT。大多数时候，这是一种风格选择。

CREATE TABLE table1(col_1 int, col_2 int)
CREATE TABLE table2(nid int, col_1 int, col_2 int)


INSERT INTO table2 (col_1,col_2) 
SELECT col_1,col_2 
FROM table1 
WHERE col_1 NOT IN (SELECT col_1 FROM table2)

You will be so much better off without cursors :) Cursors take MUCH longer to run in large data sets.

It is true you can use a LEFT JOIN, but you can also use a SELECT in your WHERE clause. Most of the time it's a style choice.

CREATE TABLE table1(col_1 int, col_2 int)
CREATE TABLE table2(nid int, col_1 int, col_2 int)


INSERT INTO table2 (col_1,col_2) 
SELECT col_1,col_2 
FROM table1 
WHERE col_1 NOT IN (SELECT col_1 FROM table2)

回复收藏 0 原文

~没有更多了~