Inserting Records from one table to another

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)

Inserting Records from one table to another

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-06-05 : 15:53:25

Is there a way to easily insert all unique records from one table to another without specifiying the column names and values? The second table structure is exactly the same as the first table.

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-05 : 15:59:33

USE Northwind
GO

SELECT DISTINCT * INTO NewOrders FROM Orders
GO

SELECT * FROM NewOrders
GO

DROP TABLE NewOrders
GO

EDIT: And a new VETERAN YAK

Brett

8-)

Edited by - x002548 on 06/05/2003 16:00:49

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-05 : 17:08:55

Use the code above to create the NewOrders table. If it already exists use the following syntax:

TRUNCATE TABLE NewOrders
INSERT INTO NewOrders SELECT DISTINCT * FROM Orders

Owais

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-06 : 09:10:02

Owais,

Assuming the structures are the same...

I would only do SELECT * INTO when creating a new object (that I most likely will dispose of).

I know doing stuff "on the fly" SELECT * comes in handy, but if it's going in to code, NEVER use SELECT *

Always use the full blown syntax. This protects you from column changes to the object.

Use this for example:


INSERT INTO NewOrders (
	  OrderID
	, CustomerID
	, EmployeeID
	, OrderDate
	, RequiredDate
	, ShippedDate
	, ShipVia
	, Freight
	, ShipName
	, ShipAddress
	, ShipCity
	, ShipRegion
	, ShipPostalCode
	, ShipCountry
)
SELECT	  OrderID
	, CustomerID
	, EmployeeID
	, OrderDate
	, RequiredDate
	, ShippedDate
	, ShipVia
	, Freight
	, ShipName
	, ShipAddress
	, ShipCity
	, ShipRegion
	, ShipPostalCode
	, ShipCountry
FROM 	  Order

I guess I gotta MOO here

Sorry Mr. Mist...this is a heart felt (My Own Opinion).

Brett

8-)

EDIT: That loud noise you heard was me falling off my soapbox...

Edited by - x002548 on 06/06/2003 09:11:24

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-06 : 09:35:04

Oh yes, I certainly agree with you 100% Brett. You should never use SELECT * in production, last thing you wanna see is somebody added a varchar(8000) column to the table and it takes all evening to get the data

Just wanted to point out the alternative syntax available (when I should have been sleeping, it was somewhere close to midnight

Owais

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-06-06 : 09:52:18

Brett:

I wish You Had told me about the dangers of useing select * before.
About a month ago I Had to rebuild 10 application's because I had to add 2 fields to a table and it threw off all of the computed fields in the apps.

Oh Yeah you and Terra did tell me, And I didint listen.... Stupid Me. Cost me 3 days of work.

So Keep up your crusade against SELECT *

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources