将数据行从表复制到同一个DB中的表 [英] Copy Row Of Data From Table to Table In Same DB
问题描述
嗨。
就像标题所说 - 我该怎么做？
给出了以下示例：
INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 =''A''
上述声明引发了以下错误：
表''TABLE2'中的标识列的显式值只能在使用列列表且IDENTITY_INSERT为ON时指定
。
然后，在填写我上面的所有列名后选择
语句我一直收到一个错误，即
来源和目的地的数量列不匹配。这是因为一栏
" confirm_hash"在目标表中不存在，只是
源表。
有人可以告诉我如何让它工作吗？
谢谢！
PS - MS SQL SERVER EXPRESS 2005
< blockquote>使用INSERT INTO的语法是这样的：
INSERT INTO TABLE2
（< column_list>）
SELECT < column_list>
FROM TABLE1
WHERE COL1 =''A''
简要说明：
- < column_list将列出您的列（如COL1，COL2，COL3等）
- < column_listmust在两个子句中包含相同数量的列
（INSERT INTO和SELECT）
- 如果你没有在INSERT INTO子句中指定< column_list（因为你在样本查询中做了
），然后< column_listin SELECT必须在TABLE2中列出所有
列
- 列必须是相同的数据类型e和大小，能够通过CAST / CONVERT隐式转换或显式转换
- 在您的情况下，如果confirm_hash
目标表中不存在列，那么你必须从列列表中删除它（或者在插入之前更改
TABLE2以添加列）
- 你不必列出IDENTITY列，因为它会自动获得
基于IDENTITY的值（如果你想强制一个值那么
列，在查询之前运行SET IDENTITY_INSERT TABLE2 ON）
如果您发布两个表的CREATE TABLE语句，一些示例数据
和期望结果你可以得到更好的帮助。
HTH，
Plamen Ratchev
http://www.SQLStudio.com
6月4，下午1:54，Plamen Ratchev < Pla ... @ SQLStudio.comwrote：
使用INSERT INTO的语法是这样的：
INSERT INTO TABLE2
（< column_list>）
SELECT< column_list>
FROM TABLE1
WHERE COL1 = ''A''
一些简短的笔记：
- < column_list将列出您的列（如COL1，COL2，COL3等）
- < column_listmust在两个子句中包含相同数量的列
（INSERT INTO和SELECT）
- 如果你没有指定< column_listin INSERT INTO子句（因为你在示例查询中确实是
），那么< column_listin SELECT必须在TABLE2中列出所有
列
- 列必须具有相同的数据类型和大小，能够通过CAST / CONVERT隐式转换或显式转换为
- 在您的情况下如果confirm_hash是
目标表中不存在列，那么你必须从列列表中删除它（或者在插入之前更改
TABLE2以添加列）
- 你不必列出IDENTITY列，因为它会自动获得
基于IDENTITY的值（如果你想强制一个值那么
列，在查询之前运行SET IDENTITY_INSERT TABLE2 ON）
如果您发布两个表的CREATE TABLE语句，一些示例数据
和期望结果你可以得到更好的帮助。
HTH，
Plamen Ratchevhttp：//www.SQLStudio.com
谢谢Plamen，
我已经按照你的指示行事了（在QA中测试过）。
自查询现在变得有点详细了，我已经决定用
创建一个存储过程了。我收到一个错误：
消息102，级别15，状态1，过程sp_MyStoredProcedure，第75行
''，''附近的语法不正确。
你介意告诉我为什么我收到这个错误（并检查我的
SPROC一般）？一个注意事项 - 我已经在Table2中添加了最后一列（第18列）
，而不是在Table1中。
谢谢，我真的很感激您可以提供任何反馈。
彼得
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
- ======================= ======================
- 作者：<作者：最后，第一个>
- 创建日期：<创建日期：2007年6月4日>
- 说明：<说明：表格到表格复制>
- ===== ========================================
CREATE PROCEDURE sp_MyStoredProcedure
@ column1 DATETIME = NULL，
@ column2 VARCHAR（50）= NULL，
@ column3 VARCHAR（50）= NULL，
@ column4 VARCHAR（50）= NULL，
@ column5 VARCHAR（50）= NULL，
@ column6 INT = NULL，
@ column7 VARCHAR（50）= NULL，
@ column8 VARCHAR（50）= NULL，
@ column9 INT = NULL，
@ column10 INT = NULL，
@ column11 INT = NULL，
@ column12 VARCHAR（50 ）= NULL，
@ column13 VARCHAR（50）= NULL，
@ column14 VARCHAR（50）= NULL，
@ column15 VARCHAR（ 50）= NULL，
@ column16 VARCHAR（50）= NULL，
@ column17 VARCHAR（50）= NULL，
@ column18 VARCHAR （50）= NULL
AS
BEGIN
- 添加SET NOCOUNT ON以防止额外的结果集来自
- 干扰SELECT语句。
SET NOCOUNT ON;
INSERT INTO Table1
（column1，
column2，
column3，
column4，
column5，
column6，
column7，
column8，
column9，
column10，
column11，
column12，
column13，
column14，
column15，
column16，
column17）
SELECT column1，
column2 ，
column3，
column4，
column5，
column6，
column7 ，
column8，
column9，
column10，
column11，
column12 ，
column13，
column14，
column15，
column16，
column17
FROM Table2 t2
WHERE t2.column1 = @ column1，
column2 = @ column2，
column3 = @ column3，
column4 = @ column4，
column5 = @ column5，
column6 = @ column6，
column7 = @ column7，
column8 = @ column8，
column9 = @ column9，
column10 = @ column10，
column11 = @ column11，
column12 = @ column12，
column13 = @ column13，
column14 = @ column14，
column15 = @ column15，
column16 = @ column16，
column17 = @ column17，
column18 = @ column18
结束
GO
语法错误是因为WHERE子句中的逗号。 WHERE子句中的
条件是逻辑表达式，您必须根据需要过滤的内容在表达式之间使用
AND或OR。修剪后的
示例如下：
INSERT INTO Table1
（column1，
column2）
SELECT column1，
column2
FROM Table2
WHERE column1 = @ column1
AND column2 = @ column2
所有这一切，我有点疑惑为什么你决定写这个存储的
程序和传递这些专栏的目的参数。如果你只需要
需要将Table2复制到Table1，那么直接运行如
这样的语句：
INSERT INTO Table1
（第1栏，
第2栏，
- ......剩下的栏目都在这里
column17 ）
SELECT column1，
column2，
- ......剩下的列都到这里
第17栏
来自表2
如果你有任何过滤器需要申请来自
Table2的列，你可以添加WHERE子句。此外，您可以将该语句包装在存储过程的
中，但我没有看到将所有这些
列参数传递给SP的目的。你能解释一下你添加它们的原因以及你如何计划执行SP，以及你传递的参数的一个例子吗？
如果你正在尝试执行类似动态搜索的东西（即在多个变量条件下过滤
），那么你可能想阅读Erland
Sommarskog关于动态搜索条件的文章：
http://www.sommarskog.se/dyn-search.html
HTH，
Plamen Ratchev
http://www.SQLStudio.com
Hi.
Like the title says - how do i do this?
I was given the following example:
INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = ''A''
The above statement threw the following error:
An explicit value for the identity column in table ''TABLE2'' can only
be specified when a column list is used and IDENTITY_INSERT is ON.
Then, after filling in all the column names in my above select
statement I kept getting an error to the effect that the number of
source and destination columns don''t match. This is because one column
"confirm_hash" does not exist in the destination table, just the
source table.
could somebody show me how to get this to work?
thanks!
PS - MS SQL SERVER EXPRESS 2005
The syntax to use INSERT INTO is like this:
INSERT INTO TABLE2
(<column_list>)
SELECT <column_list>
FROM TABLE1
WHERE COL1 = ''A''
A few brief notes:
- the <column_listwill list your columns (like COL1, COL2, COL3, etc.)
- the <column_listmust contain the same number of columns in both clauses
(INSERT INTO and SELECT)
- if you do not specify the <column_listin the INSERT INTO clause (as you
did in your sample query), then the <column_listin SELECT must much all
columns in TABLE2
- the columns have to be of the same data type and size, being able to
implicitly convert, or explicitly converted via CAST/CONVERT
- in your case if the "confirm_hash" column does not exists in the
destination table, then you have to drop it from the column list (or alter
TABLE2 before the insert to add the column)
- you do not have to list the IDENTITY column as it will get automatically
the value based on the IDENTITY (of if you want to force a value in that
column, run before the query SET IDENTITY_INSERT TABLE2 ON)
If you post your CREATE TABLE statements for both tables, some sample data
and desired results you can get much better help.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
On Jun 4, 1:54 pm, "Plamen Ratchev" <Pla...@SQLStudio.comwrote:The syntax to use INSERT INTO is like this:
INSERT INTO TABLE2
(<column_list>)
SELECT <column_list>
FROM TABLE1
WHERE COL1 = ''A''
A few brief notes:
- the <column_listwill list your columns (like COL1, COL2, COL3, etc.)
- the <column_listmust contain the same number of columns in both clauses
(INSERT INTO and SELECT)
- if you do not specify the <column_listin the INSERT INTO clause (as you
did in your sample query), then the <column_listin SELECT must much all
columns in TABLE2
- the columns have to be of the same data type and size, being able to
implicitly convert, or explicitly converted via CAST/CONVERT
- in your case if the "confirm_hash" column does not exists in the
destination table, then you have to drop it from the column list (or alter
TABLE2 before the insert to add the column)
- you do not have to list the IDENTITY column as it will get automatically
the value based on the IDENTITY (of if you want to force a value in that
column, run before the query SET IDENTITY_INSERT TABLE2 ON)
If you post your CREATE TABLE statements for both tables, some sample data
and desired results you can get much better help.
HTH,
Plamen Ratchevhttp://www.SQLStudio.com
Thanks Plamen,
I have followed your directions and that works (tested in QA).
Since the query is now getting kind of detailed, I have decided to
create a stored procedure out of this. I am getting an error:
Msg 102, Level 15, State 1, Procedure sp_MyStoredProcedure, Line 75
Incorrect syntax near '',''.
Would you mind telling me why I am getting this error (and checking my
SPROC in general)? One note - I have added a final column (column18)
in my sproc that exists in Table2, but not in Table1.
Thanks, I really appreciate any feedback you can provide.
Peter
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author: Last, First>
-- Create date: <Create Date: 4 June 2007>
-- Description: <Description: Table To Table Copy>
-- =============================================
CREATE PROCEDURE sp_MyStoredProcedure
@column1 DATETIME = NULL,
@column2 VARCHAR(50) = NULL,
@column3 VARCHAR(50) = NULL,
@column4 VARCHAR(50) = NULL,
@column5 VARCHAR(50) = NULL,
@column6 INT = NULL,
@column7 VARCHAR(50) = NULL,
@column8 VARCHAR(50) = NULL,
@column9 INT = NULL,
@column10 INT = NULL,
@column11 INT = NULL,
@column12 VARCHAR(50) = NULL,
@column13 VARCHAR(50) = NULL,
@column14 VARCHAR(50) = NULL,
@column15 VARCHAR(50) = NULL,
@column16 VARCHAR(50) = NULL,
@column17 VARCHAR(50) = NULL,
@column18 VARCHAR(50) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO Table1
(column1,
column2,
column3,
column4,
column5,
column6,
column7,
column8,
column9,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17)
SELECT column1,
column2,
column3,
column4,
column5,
column6,
column7,
column8,
column9,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17
FROM Table2 t2
WHERE t2.column1 = @column1,
column2 = @column2,
column3 = @column3,
column4 = @column4,
column5 = @column5,
column6 = @column6,
column7 = @column7,
column8 = @column8,
column9 = @column9,
column10 = @column10,
column11 = @column11,
column12 = @column12,
column13 = @column13,
column14 = @column14,
column15 = @column15,
column16 = @column16,
column17 = @column17,
column18 = @column18
END
GO
The syntax error is because of the commas in the WHERE clause. The
conditions in the WHERE clause are logical expressions and you have to use
AND or OR between expressions based on what you need to filter. A trimmed
down example is:
INSERT INTO Table1
(column1,
column2)
SELECT column1,
column2
FROM Table2
WHERE column1 = @column1
AND column2 = @column2
All that said, I am a bit puzzled why you decided to write this stored
procedure and the purpose of passing those column parameters. If you just
need to copy the Table2 to Table1, then directly run the statement like
this:
INSERT INTO Table1
(column1,
column2,
-- ... the rest of the columns go here
column17)
SELECT column1,
column2,
-- ... the rest of the columns go here
column17
FROM Table2
And then if you have any filters that you need to apply to the columns from
Table2, you can add the WHERE clause. Also, you could wrap that statement in
a stored procedure, but I just do not see the purpose of passing all those
column parameters to the SP. Can you explain why you added them and how you
plan to execute the SP, and maybe an example of what parameters you pass?
If you are trying to perform something like dynamic searching (that is
filter on multiple variable conditions), then you may want to read Erland
Sommarskog''s article on dynamic search conditions:
http://www.sommarskog.se/dyn-search.html
HTH,
Plamen Ratchev
http://www.SQLStudio.com
这篇关于将数据行从表复制到同一个DB中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
