create and insert to table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

create and insert to table

Author

Topic

JimAmigo
Posting Yak Master

119 Posts

Posted - 2005-09-26 : 15:51:43

This is probably a simple question, but have searched and haven't found an answer. Basically I have a query that creates a table, but due to row length, need to narrow down the field sizes.

Here is my current stored procedure that creates the big table from a query, (I Have condensed the query for example purposes). This creates a new table.

SELECT tbl_Officer.GOID, tbl_Assignment.AssignmentID, tbl_Officer.YG, tbl_Officer.Branch, tbl_Officer.Nickname
INTO NEWTABLE
FROM tbl_Officer INNER JOIN tbl_Assignment ON tbl_Assignments.GOID= tbl_Officer.GOID
WHERE tbl_Officer.Status ='Active'
ORDER BY tbl_Officer.LastName;

I have been able to create the new table first, Like this:

CREATE TABLE NEWTABLE
(
GOID int,
IDPosition int,
YG nvarchar(5),
Branch nvarchar(5),
Nickname nvarchar(20
)

Now how can I use the same query above to popluate this new table? Also in the above create table how do I set the GOID as a primary key?

Thanks for your help

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-09-26 : 20:02:45

Hi there.
Lookup INSERT INTO and CREATE TABLE in Books Online for more detailed help, but give the following a shot. Repost here is you have any difficulty.

Ex.


-- create table
create table dbo.testName (num int primary key, numName varchar(10))

-- populate table w/ select
insert into dbo.testName
select 1, 'One' union
select 2, 'Two'

-- return results
select num, numName 
from testName

-- cleanup
drop table dbo.testName

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources