Tuesday, March 27, 2012

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,
itarinLike this?

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

No comments:

Post a Comment