Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 22nd, 2005, 02:29 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 198
Thanks: 2
Thanked 0 Times in 0 Posts
Send a message via MSN to itHighway
Default Stored Procedures

Hello,

I need to create a Stored Procedure, which has the SELECT and INSERT command.

Using Stored Procedure, I need to SELECT a record from table "tblClient" and INSERT it into "tblShipping".

Any Help..
Any Idea...
Will really be appriciated....

Thank you

 
Old November 22nd, 2005, 02:46 PM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

The following syntax will work:

INSERT INTO tablename (columnaname1, columnname2, columnname3) SELECT columnaname1, columnname2, columnname3 FROM tablename2
WHERE ...

Scott

 
Old November 22nd, 2005, 10:30 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 198
Thanks: 2
Thanked 0 Times in 0 Posts
Send a message via MSN to itHighway
Default

Is this how sql statement work in stored procedure??????

 
Old November 23rd, 2005, 10:08 AM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Yes!!! For example:

CREATE TABLE [dbo].[table1](
[col1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[col2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[table2](
[col1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[col2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO

INSERT INTO table1 (col1, col2) VALUES ('value1', 'value2')
INSERT INTO table1 (col1, col2) VALUES ('value3', 'value4')
INSERT INTO table1 (col1, col2) VALUES ('value5', 'value6')
INSERT INTO table1 (col1, col2) VALUES ('value7', 'value8')
GO

CREATE PROCEDURE [dbo].[tempstoredproc]
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO table2 (col1, col2) SELECT col1, col2
    FROM table1

END
GO

exec tempstoredproc
GO

SELECT * FROM table2
GO












Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored Procedures KeviJay VB Databases Basics 1 June 5th, 2008 07:17 AM
stored procedures MunishBhatia SQL Server 2005 4 April 12th, 2008 01:39 AM
STORED PROCEDURES shazia1 SQL Server ASP 7 September 26th, 2007 06:11 AM
Stored Procedures jazzcatone Classic ASP Databases 0 August 28th, 2005 02:57 PM
Stored Procedures stu9820 Access 3 February 8th, 2004 01:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.