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
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
I think there has to be a way to do this but I'm not seeing it.
I would like to set the names of my primary keys in the CREATE TABLE statements. I like this for documentation so it's very clear what the PK name is. When the system generates the key names, it always add the number suffix at the end. I would need to do this both when the PK is a single column and when it is multiple columsn (see examples below).
Thanks very much for your assistance.
CREATE TABLE dbo.SecAppRole1 (
app_id INT IDENTITY(1,1),
app_name_field VARCHAR(128) NOT NULL PRIMARY KEY ,
app_role VARCHAR(128) NOT NULL,
app_role_password VARCHAR(50) NOT NULL)
CREATE TABLE dbo.SecUserAppPermission1 (
app_id INT NOT NULL,
windows_user_name VARCHAR(128) NOT NULL,
user_permission CHAR(01) NOT NULL,
PRIMARY KEY CLUSTERED (app_id ASC, windows_user_name ASC))
Hi all,
my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop...
below is my table variable and I need to create 3 indexes on this...
DECLARE @t_Replenishment_Rpt TABLE
(
Item_Nbr varchar(25) NULL,
Item_Desc varchar(255) NULL,
Trx_Date datetime NULL,
Balance int NULL,
Trx_Type char(10) NULL,
Issue_Type char(10) NULL,
Location char(25) NULL,
Min_Stock int NULL,
Order_Qty int NULL,
Unit char(10) NULL,
Issue_Qty int NULL,
Vendor varchar(10) NULL,
WO_Nbr varchar(10) NULL,
Lead_Time int NULL,
PO_Nbr char(10) NULL,
PO_Status char(10) NULL,
Currency char(10) NULL,
Last_Cost money NULL,
Dept_No varchar(20) NULL,
MSDSNbr varchar(10) NULL,
VendorName varchar(50) NULL,
Reviewed varchar(20) NULL
)
I tryed all below senarios...it is giving error...
--Indexing the @t_Replenishment_Rpt table on the column Names Item Number, Vender , Department Number
--EXEC sp_executesql(CREATE UNIQUE CLUSTERED INDEX Replenishment_index ON @t_Replenishment_Rpt (Item_Nbr))
--CREATE UNIQUE CLUSTERED INDEX Idx1 ON @t_Replenishment_Rpt.Item_Nbr
INDEX_COL ( '@t_Replenishment_Rpt' , ind_Replenishment_id , Item_Nbr )
--EXEC sp_executesql('SELECT INDEXPROPERTY('+ '@t_Replenishment_Rpt' + ', ' + 'Item_Nbr' + ',' + 'IsPadIndex' + ')')
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Vendor','IsPadIndex'))
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Dept_No','IsPadIndex'))
for MS SQL 2000
how can I do this in one time (into the CREATE TABLE)
CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL ,
[Name] [nvarchar] (100) NULL,
[Serial] [nvarchar] (100) NULL,
) ON [PRIMARY]
ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[id_Users]
) ON [PRIMARY]
CREATE UNIQUE INDEX [IX_Users] ON [Users]([Serial]) ON [PRIMARY]
and that one
CREATE TABLE [dbo].[UsersExtra] (
[id_Users] [int] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[UsersExtra] ADD
CONSTRAINT [FK_UsersExtra_Users] FOREIGN KEY
(
[id_Users]
) REFERENCES [Users] (
[id_Users]
) ON DELETE CASCADE
thank you
In what situations we can create primary key on a table? I mean what is the minimum no of rows we can prefer to create PK.
View 8 Replies View RelatedI'm trying to get a SQL 7 and 6.5 DB to interact, but while there is no problem in SQL7, I cannot create a table called "Public" or a field called "Primary"!!
Does anyone know why this might be and if so where I might get a list of any other "invalid" names??
Thanks in advance,
Damon
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
View 4 Replies View RelatedI have successfully execute a union query. How can i create a make-table query to accomodate the resultset of the union query?
View 2 Replies View RelatedHi all,
I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:
-- ImportCSVprojects.sql --
USE ChemDatabase
GO
CREATE TABLE Projects
(
ProjectID int,
ProjectName nvarchar(25),
LabName nvarchar(25)
);
BULK INSERT dbo.Projects
FROM 'c:myfileProjects.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=======================================
-- ImportCSVsamples.sql --
USE ChemDatabase
GO
CREATE TABLE Samples
(
SampleID int,
SampleName nvarchar(25),
Matrix nvarchar(25),
SampleType nvarchar(25),
ChemGroup nvarchar(25),
ProjectID int
);
BULK INSERT dbo.Samples
FROM 'c:myfileSamples.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=========================================
-- ImportCSVtestResult.sql --
USE ChemDatabase
GO
CREATE TABLE TestResults
(
AnalyteID int,
AnalyteName nvarchar(25),
Result decimal(9,3),
UnitForConc nvarchar(25),
SampleID int
);
BULK INSERT dbo.TestResults
FROM 'c:myfileLabTests.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.
2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?
Please help and advise.
Thanks in advance,
Scott Chang
I desire to have a clustered index on a column other than the Primary Key. I have a few junction tables that I may want to alter, create table, or ...
I have practiced with an example table that is not really a junction table. It is just a table I decided to use for practice. When I execute the script, it seems to do everything I expect. For instance, there are not any constraints but there are indexes. The PK is the correct column.
CREATE TABLE [dbo].[tblNotificationMgr](
[NotificationMgrKey] [int] IDENTITY(1,1) NOT NULL,
[ContactKey] [int] NOT NULL,
[EventTypeEnum] [tinyint] NOT NULL,
[code]....
Hey
I am very new to database and have a question about views, that I hope someone can help me with, i am sure its simple:
I have to tables for storing different users, I want(for a log in function),to make a view that combine these to tables.
so all names stored in table1 under column customer_name, and all names stored in table2 under column name contact_name will in the view be stored under column username.
What shall a do?
Thanks for all help
Hi,I was wondering if someone can help me. I have 4 tables in an SQL Server 2005 database, for purposes sake called 'table1', 'table2', 'table3' and 'table4'. They all have the same data structure between them. The columns inside them have the exact same design. For purposes sake called 'column1', 'column2', 'column3' and 'column4'. Alot of these tables have duplicate records scattered between them, so basically I want to create 'table5' with the same column names but only unique records. Can someone specify the syntax I need for this. I'm pretty sure it's a union query I need so am scouring as I type this also. Many Thanks Robert
View 5 Replies View RelatedHI, Guys:
I've some AT_DATE tables (eg: AT_20080401, AT_20080402, ...) in SQLServer DB, and these AT_XX table have same columns. but table count could be variant, so I have to query sysobjects to get all of these tables. like this:
select name from sysobject where name like 'AT_%'
Now I try to create a view AT which is the union of all these AT_XX tables, such as:
Code Snippet
Create View AT as
select * from AT_20080401
union
select * from AT_20080402
union ...
but since I'm not sure how many tables there, it would be impossible to write SQL as above.
though I could get this union result via stored-procedure, view couldn't be created on the resultset of a procedure.
Create View AT as
select * from AT_createView() <-- AT_createView must be a function, not procedure
I've checked msdn, there is Multi-statement table-valued function, but this function type seems to create one temporary table, I don't want to involve much of insert operation because there could be more than 1million records totally in these AT_XX tables.
So is there any way to achived my goal?
any reference would be appreciated, thanks !
I have the following MDX Query:
Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
[Cost Centres].[Cost Centre].[All].Children
[Locations].[Locations].[All].Children
on 1
From MyCube
which produced the following table:
Division
Cost Centre
Location
Dollars
AA
1
X
$30.00
[code]....
What I am hoping to do is create a set out of the Union of specific values in the [Cost Centres].[Cost Centre] and [Locations].[Locations] hierarchies into a single set and use that new set in my MDX query across the columns.
Using the table and query from above, I have the following conditions that would determine the value in the set (lets call the new set 'NewSet')
When Cost Centre = 1 and Location = X Then "CustomType1"
When Cost Centre = 1 and Location = Y Then "CustomType2"
When Cost Centre = 1 and Location = Z Then "CustomType3"
When Cost Centre = 2 and Location = Y Then "CustomType4"
When Cost Centre = 2 and Location = Z Then "CustomType5"Else "Default"
Then, if I was to execute the new query:
with
set NewSet as "Some Unknown Magic Here"
Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
{NewSet}
on 1
From MyCube
I would end up with
Division
NewSet
Dollars
AA
CustomType1
$166.64
AA
CustomType3
$64.24
BB
[code]....
l would like to run a health check on my newly designed database to ensure that l've covered all the necessary points to ensure that l have a good database. Is there there such a procedure?
If l want to create a primary key on a table with duplicates, how would l achive that? l tried to create a new table then create the pk. then ran the following script to populate
instert into tabB
select * distinct tabA
It did not work. Please advive
Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
Doing the exercises in a book, which starts CREATE DATABASE xyz ON PRIMARY...
It fails, so stepping through it. What is PRIMARY?
Searching Help seems always to give primary KEY, but cannot be that at point of creating the DB.
XP ProVS Pro 2005SQL Server 2005 Dev.
With the last table being created below, it has a clustered primary key.One of the fields it is referencing on the previous table, courseId, can NOT be unique.But without it being unique, the cluster primary key won't work.Is there another way to achieve what I am trying to do here?CREATE TABLE dbo.courseScores ( courseId varchar(20) NOT NULL CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId) REFERENCES courseStructure (courseId),
studentId varchar(20) NOT NULL CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId) REFERENCES students (studentId),
CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId)
)
CREATE TABLE dbo.objScores ( objID varchar(20) NOT NULL CONSTRAINT FK_objId_objstructure FOREIGN KEY (objID) REFERENCES objStructure (objID),
studentId varchar(20) NOT NULL CONSTRAINT FK_studentId_students3 FOREIGN KEY (studentId) REFERENCES students (studentId),
courseId varchar(20) NOT NULL CONSTRAINT FK_course FOREIGN KEY (courseId) REFERENCES courseScores (courseId), CONSTRAINT PK_objScores PRIMARY KEY CLUSTERED (objID, studentId, courseId) )
Thanks all,Zath
I have an access table that has a primary key (entitled "ID Number"), no duplicates, the field is an integer.And, importantly, the value is set to "increment".The format is "phd"000 - so it starts out phd001, phd002, and so on...How to do this in an SQL table? Can that format be done? Or is it better not to do it via SQL but in coding instead?
View 1 Replies View RelatedHi, I'm in the midst of an Access 2003 to SQL server 2000 upsizing project and have come across a table on Sql Server that has a field that looks like it's supposed to be the PK but it contains duplicates. What I'd like to do is to have a cursor start at the first value and increment the next value by 1. Could someone explain how I'd go about this?
Many thanks,
Peter
I am trying to create a FK using a composite PK and here are the details that I want to achieve.
Table -A
Column A1 not null,
Column A2 not null
Primary key (A1, A2).
Table -B
Column B1 Primary key.
Column B2 not null
FK (B2) References A(A1).
When I try to do this I am getting some errors. Questions: First of all is this possible? if yes, then how I can create it.
I would like to get the primary key values in a DML CREATE TRIGGER as follows:
CustomerID = 12 AND InvoiceID = 50
I create the DML trigger for UPDATE and DELETE only, how do I retrieve the values as above ?
Jon Galloway provided a sample in http://weblogs.asp.net/jgalloway/archive/2008/01.aspx but the Primary Key Field and Primary Key Value were inserted into separate columns while I need to combine the Primary Key Field and Primary Key Value into 1 single field, using his example, I want to form the primary key as:
ScoreId = 423
ScoreId = 3064
etc.
Note that his example only show a single primary key in a table whereas I need to handle multiple primary keys in a table.
I have two practice tables I have created and want to export the values of one into the source table. I want to know if I can export into a table and have the destination table automatically give a primary key value to a record? I haven't been able to figure this out even after fiddling with the "Enable identity insert" checkbox under the Column Mappings tab. I have created source tables with and without primary keys and neither works because of the fact that I need to have a value for a primary key in order to INSERT into the destination.
Do I have to copy the source records into a staging table and assign the PK values myself by hand? This can't be the answer.
ddave
This is for SQL2k5. The database may be small or big, I don't know (it's going out to multiple customers). I'm wondering if in general it's considered "better" to create a single non-primary default filegroup and put all the objects there, or just leave everything in primary? In one training years back I got the impression that recovering the primary filegroup was important for certain restore operations, so it was always wise to separate them like this.
Thanks for your input,
Mike
MCDBA
Hi All
I hope someone smart can help me, it would be highly appriciated.
I am developing an SQL Serverdatabase and in on of the tables I need the primary key to consist of two pieces of data.
TblOrders: OrderNum, Orderdate, ....
TblDispatchers: DispatcerID, Dispatcher, DispatcherAddress
The OrderNum field in TblOrders need to be a composite of an AutoNum-field (incrementet by 1) and the DispatcerID from the tblDispatchers.
Can this be done, and how.
Many thanks
Kind regards
Tina
SQL Server 2008 r2...
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
[Code] .....
Is there a way to attach a single record to a Select expression without another table?
Here's my case: I am developing a Select query for a report. The report has to show a row for each month, but not all months are necessarily represented in the data, so the result is a report with some of the months missing. Can't have that. To "force" the appearance of all months, I'd like to UNION my Select query to "dummy" records for each month with zeros in the data fields, or something like that. But I'd like to avoid creating a table just to hold the months for the Union. Is there a way to attach records to the result of a Select without those records coming from a table? I'd like to be able to say something like this:
"Do this SELECT. Now, append a row with the following values..."
Thanks!
I have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type
Code:
ID Type Name Field
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
5 Type5Name5(group2) -group2
6 Type6Name6(group6)
But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)
Code:
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
5 Type5Name5(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
6 Type6Name6(group6)
For a new project. I need to create random alphanumeric characters as primary key values when inserting a record.
eg: cmSbXsFE3l8
It can start from 4 digit characters and can grow to 6, 7 as required
The database will involve more than 10000 concurrent users.
I don't want to use guid or auto increment integer or sequence.
I want to create a table with primary key , and put relationship with second table.
View 5 Replies View RelatedIs it possible to create a linked server from an Excel spreadsheet and give it a primary key? If so, how?
Thanks,
--Stan
What am I missing?
I have three tables "UNIONED" and I want the this inserted into a table.
INSERT INTO mytable (A, B, C, D, E)
SELECT A, B, C, D, E
FROM
(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
UNION
(SELECT A, B, C, D, E
FROM a_lot_of_parts)
GROUP BY A,B,C,D,E
This part alone works just like I want it:
(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
UNION
(SELECT A, B, C, D, E
FROM a_lot_of_parts)
I just want it inserted inte stated columns in my table.
I've stared so much at this I'm "homeblind", ie I can't see the forest because of all the trees...