Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    8

    Exclamation Unanswered: Create a table with a Union and specify Primary Key

    I want to create a table with a union. Which I have already accomplished. I want to specify the Primary Key in the statement.

    Or would I have to use another statement. How would I do that? With an update and what would the syntax be?



    Thanks before hand,

    itarin

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Like this?

    Code:
    USE Northwind
    GO
    
    DECLARE @OrderID int
    
    SELECT @OrderId = OrderId FROM Orders
    
    SELECT @OrderId
    GO
    
    DECLARE @OrderID int
    
    SET @OrderId = (SELECT OrderId FROM Orders)
    
    SELECT @OrderId
    GO
    
    
    USE Northwind 
    GO
    
    CREATE TABLE myTable99(Col1 int)
    GO
    
    INSERT INTO myTable99(Col1) SELECT 1
    GO
    
    SELECT * FROM myTable99
    GO
    
    DECLARE @Col1 int
    SELECT @Col1 = 0
    
    UPDATE myTable99 SET @Col1 = Col1 = @Col1+1
    
    SELECT * FROM myTable99
    SELECT @Col1
    GO
    
    DROP TABLE myTable99
    GO
    
    
    SELECT Char(160)
    
    
    USE Northwind
    
    CREATE TABLE myTable99(Col1 int)
    GO
    
    INSERT INTO myTable99(Col1)
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    GO
    
    DECLARE myCursor99 CURSOR FOR SELECT Col1 FROM myTable99
    DECLARE @Col1 int
    
    OPEN myCursor99
    
    FETCH NEXT FROM myCursor99 INTO @Col1
    SELECT '@@ERROR = ' + CONVERT(varchar(5),@@ERROR) + ' @@FETCH_STATUS = ' + CONVERT(varchar(5), @@FETCH_STATUS)
    SELECT @Col1
    
    WHILE @@FETCH_STATUS = 0
      BEGIN
    	FETCH NEXT FROM myCursor99 INTO @Col1
    	SELECT '@@ERROR = ' + CONVERT(varchar(5),@@ERROR) + ' @@FETCH_STATUS = ' + CONVERT(varchar(5), @@FETCH_STATUS)
    	SELECT @Col1
    	FETCH NEXT FROM myCursor99 INTO @Col1
    	SELECT '@@ERROR = ' + CONVERT(varchar(5),@@ERROR) + ' @@FETCH_STATUS = ' + CONVERT(varchar(5), @@FETCH_STATUS)
    	SELECT @Col1
      END
    CLOSE myCursor99
    DEALLOCATE myCursor99
    GO
    
    DROP Table myTable99
    GO
    
     
    CREATE TRIGGER myTrigger99 ON EmployeeGamingLicense
    FOR UPDATE
    AS
    
    INSERT INTO TERMINATION(Status, [TM #], LastName, FirstName, SocialSecurityNumber, DateHired, Title)
         SELECT STATUS, [TM#], LASTNAME, FIRSTNAME, [SSN#], HIREDATE, JOBTITLE
           FROM inserted
          WHERE STATUS = 'TERMINATED'
    
    --Assuming you want them removed as well, and assumin SSN# is the key
    DELETE FROM EmployeeGamingLicense o
     WHERE EXISTS (SELECT * FROM inserted i WHERE STATUS = 'TERMINATED' AND o.[SSN#] = i.[SSN#])
    
    USE Northwind
    GO
    
    CREATE TABLE myTable00(Col1 int NOT NULL)
    CREATE TABLE myTable01(Col1 int NOT NULL)
    GO
    
    INSERT INTO myTable00(Col1) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    INSERT INTO myTable01(Col1) SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
    GO
    
    SELECT * INTO myTable99 FROM myTable00 UNION ALL SELECT * FROM myTable01
    GO
    
    sp_Help myTable99
    GO
    
    ALTER TABLE myTable99 ADD CONSTRAINT [PK_Col1] PRIMARY KEY  CLUSTERED 
    	(
    		[Col1]
    	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
    GO	
    
    sp_Help myTable99
    GO
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable00
    DROP TABLE myTable01
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •