[Store Proc] Create table + Insert records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

[Store Proc] Create table + Insert records

Author

Topic

captusite
Starting Member

3 Posts

Posted - 2006-01-11 : 08:40:05

Hi,

In a store procedure, after the creating the table 'TEST445' I want to copy all records (about 300) from the table 'Articles' which as the same structure.

The folowing code is incomplete, I think i need a loop, but i'm lost ... Pls help !


CREATE PROCEDURE [dbo].[NEWTABLE] 

AS 

CREATE TABLE TEST445 
( 
Id int PRIMARY KEY IDENTITY(1,1), 
ReferenceEmape nvarchar(255) NULL, 
Designation nvarchar(255) NULL, 
Type nvarchar(255) NULL, 
MarqueComp nvarchar(255) NULL, 
ModeleComp nvarchar(255) NULL, 
PrixHT money, 
Favoris Bit 
) 

SELECT ReferenceEmape, Designation, Type, MarqueComp, ModeleComp, PrixHT, Favoris FROM Articles 

INSERT INTO TEST445 (ReferenceEmape, Designation, Type, MarqueComp, ModeleComp, PrixHT, Favoris) 
VALUES ('@RefEMAPE', '@Designation', '@Type','@Marque','@Modele','@PrixHT', 0) 

GO

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-11 : 10:47:17

First check the table creation part only
If it works:

the query to insert data is:
Insert into destinationTbl (Fields List seperated by commas)
Select (Corresponding Fields List seperated by commas) from SourceTable

- No need to have @RefEMAPE ...
- the variables (if used) like @RefEMAPE are never sorrounded by quotes - then it becomes text
- Learn Insert Syntax from BOL
- No need of a loop in most of the cases, as its inefficient
- In case a loop is needed the syntax is very much different - c the BOL
- U can do it directly (if u wanted to) without any stored proc using the following kind of query (in that case u don't need to create the table even, but i doubt that using inside a Stored proc
Select (fields ...) into DestinationTbl from SourceTable
- Just go thru BOL (Books On Line) - which has immense info

captusite
Starting Member

3 Posts

Posted - 2006-01-11 : 10:54:26

In fact, here is the solution

select * into TEST445 from articles

as simple as that !
So much trouble for that ....

Thanks anyway.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-11 : 11:22:47

quote:
Originally posted by captusite

In fact, here is the solution

-- I gave that answer my dear ( u'd c that if u take time to read what I wrote )

Select (fields ...) into DestinationTbl from SourceTable

quote:
Originally posted by captusite

So much trouble for that ....

trouble for whom?
trouble because u gave the question, starting with all sort of things as stored procedure , table creation etc...
So I assume to be a beginner who needs more analysis on the things that u want. I wrote all these, for u to learn.
U want me to learn a new way (I don't mind if it is something I never knew).
ur second post is addressed to whom ? Its good only if its addressing to u.

I also have to tell u :

Thanks anyway.

captusite
Starting Member

3 Posts

Posted - 2006-01-11 : 11:32:43

The only problem on this is my bad english... (I'm french but that's not the reason)
I was just impress to find the answer so simple.

As U said i'm a beginner, and i always will be as long as i do not read the full posted answers.

Sorry for the trouble.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-12 : 03:16:59

Why do you create table inside stored procedure?
Every time you run the sp the table will be created and you will get error the objects already exists. Script the Articles table;Rename table name to TEST445; run create table script and do write

Insert into TEST445 (columns)
Select columns from Articles

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources