August 23rd, 2004, 09:39 PM
|
|
Registered User
|
|
Join Date: Aug 2004
Posts: 2
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
Update help
This is from SQL Server Books Online Help
G. Load data using the SELECT and EXECUTE options
This example demonstrates three different methods for getting data from one table and loading it into another. Each is based on a multitable SELECT statement that includes an expression and a literal value in the column list.
The first INSERT statement uses a SELECT statement directly to retrieve data from the source table, authors, and store the result set in the author_sales table. The second INSERT executes a procedure that contains the SELECT statement, and the third INSERT executes the SELECT statement as a literal string.
Quote:
<code>
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'author_sales')
DROP TABLE author_sales
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'get_author_sales' AND type = 'P')
DROP PROCEDURE get_author_sales
GO
USE pubs
CREATE TABLE author_sales
( data_source varchar(20),
au_id varchar(11),
au_lname varchar(40),
sales_dollars smallmoney
)
GO
CREATE PROCEDURE get_author_sales
AS
SELECT 'PROCEDURE', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id like '8%'
GROUP BY authors.au_id, authors.au_lname
GO
--INSERT...SELECT example
USE pubs
INSERT author_sales
SELECT 'SELECT', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id LIKE '8%'
GROUP BY authors.au_id, authors.au_lname
--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales
--INSERT...EXECUTE('string') example
INSERT author_sales
EXECUTE
('
SELECT ''EXEC STRING'', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id like ''8%''
GROUP BY authors.au_id, authors.au_lname
')
--Show results.
SELECT * FROM author_sales
</code> | hope it is of some use
Quote: | Originally Posted by rocknc I have a table called Items and one called ItemC.
Items is already populated with data and I need to INSERT records into ItemC for every record in Items using the itemid field in Items to the same feild in ItemC.
I could write a SP and an ASP page that loops through Items ans inserts into ItemC, but it seems that there is a better way since I need only do this once.
Any ideas?
Thanks. |
|
ASP Free Forums Sponsor:
Copy field from one table to another
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
