如何从另一个表插入一个表值 [英] how to insert to one table values from another table

问题描述

您好我想创建一个存储过程，从一个表中获取特定值并将它们插入到另一个表中，如果特定的表是'DateAquired'为null我在下面有这个存储过程，但它看起来不像预期请帮助。

提前致谢



这是我的代码

Hi I want to create a stored procedure that takes specific values from one table and inserts them into another if a specific table been 'DateAquired ' is null I have this stored procedure below but it doesn't seem as expected please help.
Thanks in advance

this is my code

USE [MediaPlayer]
GO
/****** Object:  StoredProcedure [dbo].[sp_Wishlists]    Script Date: 11/26/2013 11:43:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_Wishlists]
	-- Add the parameters for the stored procedure here
	@Name nvarchar (250),
	@FileName nvarchar (250),
	@FilePath  nvarchar (50),
	@FileSize float,
	@DateAdded date,
	@MediaLength nvarchar (50),
	@MediaSubType nvarchar(50),
	@MediaType nvarchar(50),
	@Thumbnail image,
	@DateAquired nvarchar (50),
	@WishList int output ,
	@CName nvarchar  (50)output ,
	@Media nvarchar (50)output,
	@WishListID int output
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
Select GeneralID, Name, FileName, FilePath,FileSize,DateAdded,MediaLength,MediaSubType,MediaType, Thumbnail,DateAquired As Wishlist 
From General where NULLIF(DateAquired,'')IS Null

insert into Wishlists (generalID ,MediaType, Name)
values ((IDENT_CURRENT('dbo.General')),(IDENT_CURRENT('dbo.General')),(IDENT_CURRENT('dbo.General')))
SET @WishListID = @@IDENTITY
select GeneralID, MediaSubType, Name

From General where NOT EXISTS (Select Name from WishLists Where Name =@Name);
END

推荐答案

从中插入值的简便方法一个表到另一个表是使用INSERT INTO SELECT

An easy way to insert values from one table into another is to use INSERT INTO SELECT

INSERT INTO Table1(Field1, Field2, Field3)
SELECT Field1, Field2, Field3
FROM Table2
WHERE field4 = 'Whatever'

从代码中可以看出哪些表从中取值并插入哪一个。我只能看到一行标识插入。

It is not clear from the code which table are you taking values from and inserting to which one. All I can see is one row of identity inserts.

我解决了感谢您的帮助

I solved thanks for the help

USE [MediaPlayer]
GO
/****** Object:  StoredProcedure [dbo].[sp_Wishlists]    Script Date: 11/26/2013 11:43:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<author,,name>
-- Create date: <create>
-- Description:	<description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_Wishlists]
	-- Add the parameters for the stored procedure here
	@Name nvarchar (250),
	@FileName nvarchar (250),
	@FilePath  nvarchar (50),
	@FileSize float,
	@DateAdded date,
	@MediaLength nvarchar (50),
	@MediaSubType nvarchar(50),
	@MediaType nvarchar(50),
	@Thumbnail image,
	@DateAquired nvarchar (50),
	@WishList int output ,
	@CName nvarchar  (50)output ,
	@Media nvarchar (50)output,
	@WishListID int output
	
AS
BEGIN
SET NOCOUNT ON;
--inserting into this table
insert into Wishlists (generalID ,MediaType, Name)
values ((IDENT_CURRENT('dbo.General')),@MediaType, @Name)
SET @WishListID = @@IDENTITY
--Select from genereal Table
Select GeneralID, Name, FileName, FilePath,FileSize,DateAdded,MediaLength,MediaSubType,MediaType, Thumbnail,DateAquired As Wishlist 
From General where NULLIF(DateAquired,'')IS Null
-- Select from 
Select * from WishLists
select GeneralID, MediaSubType, Name
From General where NOT EXISTS (Select Name from WishLists Where Name =@Name);
END</create>

这篇关于如何从另一个表插入一个表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！