Is it possible for me to define a Primary Key on a table using a Select Into
statement? If so could someone give me an example of how to do this?> Is it possible for me to define a Primary Key on a table using a Select
> Into
> statement? If so could someone give me an example of how to do this?
SELECT ...
INTO dbo.NewTableName
FROM dbo.OldTableName
GO
ALTER TABLE dbo.NewTableName ADD PRIMARY KEY(ColumnName)
Tom
http://kbupdate.com/sql
Showing posts with label define. Show all posts
Showing posts with label define. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
Primary key question
Hi,
I am new to SQL 2000 and scratching my head...I am importing text files to SQL 2000 tables. How should I define the primary key in a table if the text file is :
Example 'customer invoice file'
column 1-Customer Bus Unit (Char 3)
column 2-Batch number (Char 6)
column 3-Document type (Char 1)
column 4-Invoice number (char 15)
column 5-Invoice dollar (Money)
column 6 - due date (date)
column 7 - customer name (char 35)
column 8 - customer address (char 50)
The primary key of this file is column1 + column 2 + coumn 3 + column 4. That concatnation of 4 fields make each record unique.
Should I define all 4 feilds as the primary key field? Thank you, Yanoroouh...yes...
Do you have a staging environemnt?
You can create work tables with no constraints...that way you can audit the data before it hit the final destination...|||Originally posted by Brett Kaiser
uh...yes...
Do you have a staging environemnt?
You can create work tables with no constraints...that way you can audit the data before it hit the final destination...
Please bear with me...
What do you mean a staging environment?
Could you suggest a work table design? (example)|||Sure...it's a table that looks like your final table...just that it has no keys, indexes or other constraints...even make all of the columns varchar (dates, money, ect)
You can the audit the data
LIKE
SELECT * FROM myTable99 WHERE ISDATE(adatecolumn) = 0
Will show you all rows with bad dates
SELECT * FROM myTable99 WHERE ISNUMERIC(anumericcolumn) = 0
Will show you all rows with non numbers in the expected number columns
PK violations
SELECT col1, col2, col3, col4, count(*)
FROM mytable99
GROUP BY col1, col2, col3, col4
HAVING COUNT(*) > 1
Shows where you'll have a dup key
ect
You can even make sure RI is ok...
If the data passes your audit, you can either bcp/dts or bulk insert the file, or Just INSERT it from your stage table...
Clear as mud?
Let me know...|||Okay, I will try that. Thank you very much.
I may encounter another questions... I will let you know.
Yanoroo|||Brett, are you still checking?
Not only the staging the work, I tried your PK violation check and it worked beautifully.
I appriciate your help. Yanoroo|||Hey....Merry Holiday
I'm glad it worked for you...It's always better to know that you're working with clean data...
TRUST NO ONE bearing Excel spreadsheets...(or other data sources outside your environment)..
Better still require data files with header and/or trailers...and if you can only get one...demand a trailer...with record counts and possible the sum of int/decimal columns...
I am new to SQL 2000 and scratching my head...I am importing text files to SQL 2000 tables. How should I define the primary key in a table if the text file is :
Example 'customer invoice file'
column 1-Customer Bus Unit (Char 3)
column 2-Batch number (Char 6)
column 3-Document type (Char 1)
column 4-Invoice number (char 15)
column 5-Invoice dollar (Money)
column 6 - due date (date)
column 7 - customer name (char 35)
column 8 - customer address (char 50)
The primary key of this file is column1 + column 2 + coumn 3 + column 4. That concatnation of 4 fields make each record unique.
Should I define all 4 feilds as the primary key field? Thank you, Yanoroouh...yes...
Do you have a staging environemnt?
You can create work tables with no constraints...that way you can audit the data before it hit the final destination...|||Originally posted by Brett Kaiser
uh...yes...
Do you have a staging environemnt?
You can create work tables with no constraints...that way you can audit the data before it hit the final destination...
Please bear with me...
What do you mean a staging environment?
Could you suggest a work table design? (example)|||Sure...it's a table that looks like your final table...just that it has no keys, indexes or other constraints...even make all of the columns varchar (dates, money, ect)
You can the audit the data
LIKE
SELECT * FROM myTable99 WHERE ISDATE(adatecolumn) = 0
Will show you all rows with bad dates
SELECT * FROM myTable99 WHERE ISNUMERIC(anumericcolumn) = 0
Will show you all rows with non numbers in the expected number columns
PK violations
SELECT col1, col2, col3, col4, count(*)
FROM mytable99
GROUP BY col1, col2, col3, col4
HAVING COUNT(*) > 1
Shows where you'll have a dup key
ect
You can even make sure RI is ok...
If the data passes your audit, you can either bcp/dts or bulk insert the file, or Just INSERT it from your stage table...
Clear as mud?
Let me know...|||Okay, I will try that. Thank you very much.
I may encounter another questions... I will let you know.
Yanoroo|||Brett, are you still checking?
Not only the staging the work, I tried your PK violation check and it worked beautifully.
I appriciate your help. Yanoroo|||Hey....Merry Holiday
I'm glad it worked for you...It's always better to know that you're working with clean data...
TRUST NO ONE bearing Excel spreadsheets...(or other data sources outside your environment)..
Better still require data files with header and/or trailers...and if you can only get one...demand a trailer...with record counts and possible the sum of int/decimal columns...
Primary Key fields require index?
Whenever you define a Primary Key on a table in SQL Server 2000/2005 my
understanding was that this implicitly created a index for all the fields in
the Primary Key. Therefore, as far as I'm aware, it would be redundant to
explicitly define an index comprised of these same fields.
For example, given the following Primary key declaration...
ALTER TABLE dbo.Table1 WITH NOCHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED
(
Field1,
Field2
) ON [PRIMARY]
GO
...the following would be unnecessary...
CREATE INDEX Index_Table1 ON Table1(Field1, Field2)
Is this correct?
Thanks in advance.yes, the index is created by the constraint clause, so there's no need to
create separate index.
peter|||Cipher a crit :
> Whenever you define a Primary Key on a table in SQL Server 2000/2005 my
> understanding was that this implicitly created a index for all the fields
in
> the Primary Key. Therefore, as far as I'm aware, it would be redundant to
> explicitly define an index comprised of these same fields.
> For example, given the following Primary key declaration...
> ALTER TABLE dbo.Table1 WITH NOCHECK ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED
> (
> Field1,
> Field2
> ) ON [PRIMARY]
> GO
> ...the following would be unnecessary...
> CREATE INDEX Index_Table1 ON Table1(Field1, Field2)
Every time you create a constraint that must be unique (PRIMARY KEY or
UNIQUE constraint) SQL Server put an INDEX.
You do not need to add your own index.
For Primary Key the index type is CLUSTERED wich is not perfect for some
primary key types. In fact it is perfect when :
1) the key is a unique column
2) the data to be stored in the time is always in the index order
wich means perfect for autoinc or timestamped DATETIME columns.
IF you want to avoid this CLUSTERED index in the PK, just add the word
NONCLUSTERED after the PRIMARY KEY spec.
Only one CLUTERED index can be set in a table, because the clustred
index in fact is the table.
A +
>
> Is this correct?
>
> Thanks in advance.
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Two indexes that are exactly the same are not needed, but since the
statistics for a composite index are only kept for the first column, maybe a
n
extra index might be of help - i.e. in a situation with a composite primary
key on columns Name, LastName an additional index on column LastName may
improve queries where the first index cannot be used.
But this, of course is not what you were asking.
ML
http://milambda.blogspot.com/
understanding was that this implicitly created a index for all the fields in
the Primary Key. Therefore, as far as I'm aware, it would be redundant to
explicitly define an index comprised of these same fields.
For example, given the following Primary key declaration...
ALTER TABLE dbo.Table1 WITH NOCHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED
(
Field1,
Field2
) ON [PRIMARY]
GO
...the following would be unnecessary...
CREATE INDEX Index_Table1 ON Table1(Field1, Field2)
Is this correct?
Thanks in advance.yes, the index is created by the constraint clause, so there's no need to
create separate index.
peter|||Cipher a crit :
> Whenever you define a Primary Key on a table in SQL Server 2000/2005 my
> understanding was that this implicitly created a index for all the fields
in
> the Primary Key. Therefore, as far as I'm aware, it would be redundant to
> explicitly define an index comprised of these same fields.
> For example, given the following Primary key declaration...
> ALTER TABLE dbo.Table1 WITH NOCHECK ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED
> (
> Field1,
> Field2
> ) ON [PRIMARY]
> GO
> ...the following would be unnecessary...
> CREATE INDEX Index_Table1 ON Table1(Field1, Field2)
Every time you create a constraint that must be unique (PRIMARY KEY or
UNIQUE constraint) SQL Server put an INDEX.
You do not need to add your own index.
For Primary Key the index type is CLUSTERED wich is not perfect for some
primary key types. In fact it is perfect when :
1) the key is a unique column
2) the data to be stored in the time is always in the index order
wich means perfect for autoinc or timestamped DATETIME columns.
IF you want to avoid this CLUSTERED index in the PK, just add the word
NONCLUSTERED after the PRIMARY KEY spec.
Only one CLUTERED index can be set in a table, because the clustred
index in fact is the table.
A +
>
> Is this correct?
>
> Thanks in advance.
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Two indexes that are exactly the same are not needed, but since the
statistics for a composite index are only kept for the first column, maybe a
n
extra index might be of help - i.e. in a situation with a composite primary
key on columns Name, LastName an additional index on column LastName may
improve queries where the first index cannot be used.
But this, of course is not what you were asking.
ML
http://milambda.blogspot.com/
Tuesday, March 20, 2012
Primary key and clustered index
Do I need to define clustered index on the primary key of a table explicity?> Do I need to define clustered index on the primary key of a table
> explicity?
A primary key constraint always creates a unique index so there is no need
to define one explicitly. You have the choice of creating the primary key
index as either clustered or non-clustered when you create the constraint.
If neither CLUSTERED nor NONCLUSTERED is specified and no clustered index
exists on the table, the default is clustered. A non-clustered primary key
index will be created if a clustered index already exists. Examples below:
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY CLUSTERED (MyColumn)
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY NONCLUSTERED (MyColumn)
--clustered if no existing clustered index, otherwise non-clustered
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY (MyColumn)
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Man T" <alan_nospam_pltse@.yahoo.com.au> wrote in message
news:uOSlnYUoIHA.2064@.TK2MSFTNGP05.phx.gbl...
> Do I need to define clustered index on the primary key of a table
> explicity?
>
> explicity?
A primary key constraint always creates a unique index so there is no need
to define one explicitly. You have the choice of creating the primary key
index as either clustered or non-clustered when you create the constraint.
If neither CLUSTERED nor NONCLUSTERED is specified and no clustered index
exists on the table, the default is clustered. A non-clustered primary key
index will be created if a clustered index already exists. Examples below:
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY CLUSTERED (MyColumn)
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY NONCLUSTERED (MyColumn)
--clustered if no existing clustered index, otherwise non-clustered
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY (MyColumn)
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Man T" <alan_nospam_pltse@.yahoo.com.au> wrote in message
news:uOSlnYUoIHA.2064@.TK2MSFTNGP05.phx.gbl...
> Do I need to define clustered index on the primary key of a table
> explicity?
>
Monday, March 12, 2012
Primary key
Is it possible to define a primary key in a view? My View is a 5 tables.
Table 1 holds a primary key that is used as foreign key in the other 4.
It is a view i have made on an SQL 2000 server
best regards
Trond>> Is it possible to define a primary key in a view? <<
No. A VIEW is defined as a virtual table with a SELECT statement. You
can make the rows in the VIEW unique by proper coding. You can modify
it with an INSTEAD OF trigger that changes the underlying base tables.
Table 1 holds a primary key that is used as foreign key in the other 4.
It is a view i have made on an SQL 2000 server
best regards
Trond>> Is it possible to define a primary key in a view? <<
No. A VIEW is defined as a virtual table with a SELECT statement. You
can make the rows in the VIEW unique by proper coding. You can modify
it with an INSTEAD OF trigger that changes the underlying base tables.
Saturday, February 25, 2012
Preview pane not showing data
I have a problem within the Report Designer in Visual Studio .NET. I a new
report, I define a dataset that returns data via a SQL query fine. I design
the report without issues, but when I try to preview the report, no data is
displayed.
This error seems to have occured after applying reporting services SP1 to my
machine. No errors are reported, and nothing appears in the event log.
Any ideas as to how I can correct this problem out there?
Thanks in advance.
MattIs there an .rdl.data file in the same directory as your .rdl file? If so, delete it and try again.
Also, does hitting the Refresh button in the preview pane have any effect?
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt Pothier" <Matt.Pothier@.sonepar-us.com> wrote in message
news:eEtr8WvfEHA.596@.TK2MSFTNGP11.phx.gbl...
> I have a problem within the Report Designer in Visual Studio .NET. I a new
> report, I define a dataset that returns data via a SQL query fine. I design
> the report without issues, but when I try to preview the report, no data is
> displayed.
> This error seems to have occured after applying reporting services SP1 to my
> machine. No errors are reported, and nothing appears in the event log.
> Any ideas as to how I can correct this problem out there?
> Thanks in advance.
> Matt
>|||Thanks for the response. There were .rdl.data files in the same directory.
I tried deleting them, but got the same results. The refresh button did not
help either.
In a moment of frustration I uninstalled reporting services; performed a
repair install on VS .Net, reinstalled Reporting services; applied the
service pack and started over again. Everything seems to be working now.
"Donovan R. Smith [MSFT]" <donovans@.online.microsoft.com> wrote in message
news:%23iGKIuvfEHA.3556@.TK2MSFTNGP12.phx.gbl...
> Is there an .rdl.data file in the same directory as your .rdl file? If
so, delete it and try again.
> Also, does hitting the Refresh button in the preview pane have any effect?
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Matt Pothier" <Matt.Pothier@.sonepar-us.com> wrote in message
> news:eEtr8WvfEHA.596@.TK2MSFTNGP11.phx.gbl...
> > I have a problem within the Report Designer in Visual Studio .NET. I a
new
> > report, I define a dataset that returns data via a SQL query fine. I
design
> > the report without issues, but when I try to preview the report, no data
is
> > displayed.
> >
> > This error seems to have occured after applying reporting services SP1
to my
> > machine. No errors are reported, and nothing appears in the event log.
> >
> > Any ideas as to how I can correct this problem out there?
> >
> > Thanks in advance.
> >
> > Matt
> >
> >
>
report, I define a dataset that returns data via a SQL query fine. I design
the report without issues, but when I try to preview the report, no data is
displayed.
This error seems to have occured after applying reporting services SP1 to my
machine. No errors are reported, and nothing appears in the event log.
Any ideas as to how I can correct this problem out there?
Thanks in advance.
MattIs there an .rdl.data file in the same directory as your .rdl file? If so, delete it and try again.
Also, does hitting the Refresh button in the preview pane have any effect?
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt Pothier" <Matt.Pothier@.sonepar-us.com> wrote in message
news:eEtr8WvfEHA.596@.TK2MSFTNGP11.phx.gbl...
> I have a problem within the Report Designer in Visual Studio .NET. I a new
> report, I define a dataset that returns data via a SQL query fine. I design
> the report without issues, but when I try to preview the report, no data is
> displayed.
> This error seems to have occured after applying reporting services SP1 to my
> machine. No errors are reported, and nothing appears in the event log.
> Any ideas as to how I can correct this problem out there?
> Thanks in advance.
> Matt
>|||Thanks for the response. There were .rdl.data files in the same directory.
I tried deleting them, but got the same results. The refresh button did not
help either.
In a moment of frustration I uninstalled reporting services; performed a
repair install on VS .Net, reinstalled Reporting services; applied the
service pack and started over again. Everything seems to be working now.
"Donovan R. Smith [MSFT]" <donovans@.online.microsoft.com> wrote in message
news:%23iGKIuvfEHA.3556@.TK2MSFTNGP12.phx.gbl...
> Is there an .rdl.data file in the same directory as your .rdl file? If
so, delete it and try again.
> Also, does hitting the Refresh button in the preview pane have any effect?
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Matt Pothier" <Matt.Pothier@.sonepar-us.com> wrote in message
> news:eEtr8WvfEHA.596@.TK2MSFTNGP11.phx.gbl...
> > I have a problem within the Report Designer in Visual Studio .NET. I a
new
> > report, I define a dataset that returns data via a SQL query fine. I
design
> > the report without issues, but when I try to preview the report, no data
is
> > displayed.
> >
> > This error seems to have occured after applying reporting services SP1
to my
> > machine. No errors are reported, and nothing appears in the event log.
> >
> > Any ideas as to how I can correct this problem out there?
> >
> > Thanks in advance.
> >
> > Matt
> >
> >
>
Monday, February 20, 2012
preventing second instance of SP
Is it possible to prevent second instance of my SP running
at the same time on SQL Server?
I'll appreciate a SQL example how-to define if the SP
already running.
Thank youAbout the only way you can do that is by exclusively locking something so
the second instance waits...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"vitaliyk" <vitaliykrasner@.hotmail.com> wrote in message
news:075a01c3c315$d1d5b8c0$a101280a@.phx.gbl...
> Is it possible to prevent second instance of my SP running
> at the same time on SQL Server?
> I'll appreciate a SQL example how-to define if the SP
> already running.
> Thank you
at the same time on SQL Server?
I'll appreciate a SQL example how-to define if the SP
already running.
Thank youAbout the only way you can do that is by exclusively locking something so
the second instance waits...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"vitaliyk" <vitaliykrasner@.hotmail.com> wrote in message
news:075a01c3c315$d1d5b8c0$a101280a@.phx.gbl...
> Is it possible to prevent second instance of my SP running
> at the same time on SQL Server?
> I'll appreciate a SQL example how-to define if the SP
> already running.
> Thank you
Subscribe to:
Posts (Atom)