how to insert to one table values from another table

Question

4.00/5 (2 votes)

See more:

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

SQL

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

Posted 26-Nov-13 1:29am

Member 10423955

Add a Solution

Comments

CHill60 26-Nov-13 7:37am

What do you mean "doesn't seem as expected" - I presume you get an error when you run this - what is it?

Member 10423955 26-Nov-13 7:54am

Well it just insert nulls into my wishlist table instead of values

CHill60 26-Nov-13 8:01am

See RyanDevs solution

Herman<T>.Instance 26-Nov-13 7:42am

no values, no set between Insert Into and Select

CHill60 26-Nov-13 7:47am

You need to use the reply button next to a post otherwise the poster will not be notified of your comment. I've never seen an error message worded like that - please be specific in future. Not sure what the point of all those parameters is, they're not being used. The only insert in your SP is just trying to insert that ID three times. See RyanDev's solution for how to do it properly *smacking myself in head*

Herman<T>.Instance 26-Nov-13 7:52am

FYI: it is not my SP!

CHill60 26-Nov-13 7:55am

D'oh - sorry!

Member 10423955 26-Nov-13 7:53am

well what I'm trying to achieve by this ,is to create an automatic output where I don't have to fill in any values into my wish list table yet whenever I insert something in general it should just automatically insert them into my wish list

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Answer 1 · 2013-11-26T01:41:00

Solution 1

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

SQL

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

Posted 26-Nov-13 1:41am

ZurdoDev

Comments

kabifarm 26-Nov-13 7:54am

you can also do it thiis way

On the same databse use:
SELECT * into stock_record1
FROM stock_record

in another database
SELECT * into [pos].[dbo].[bank]

from [school].[dbo].[bank]

CHill60 26-Nov-13 7:55am

But not if the table already exists

ZurdoDev 26-Nov-13 7:57am

As CHill60 points out, this will actually create the table. Which is very useful sometimes but if the table already exists it won't work this way.

obhijitghosh · Answer 2 · 2013-11-26T02:00:00

Solution 2

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.

Posted 26-Nov-13 2:00am

obhijitghosh

Comments

phil.o 26-Nov-13 8:02am

Please do not post a comment as a solution.
You should copy your answer, post it as a comment to the OP's question, and delete this solution.

Member 10423955 · Answer 3 · 2013-11-26T02:17:00

I solved thanks for the help

SQL

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>

how to insert to one table values from another table

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us