Creating an Identity column to a SELECT statement

Hi all,
I am pretty much going insane. I have tried all sorts of things and gotten
nowhere, and I'm fairly sure there exists a simple solution to my problem.
If you help me, I will be eternally in your debt.
Here is the scenario. I have a stored procedure which conains a fairly heavy
UNION query, which I'm not going to repeat here. The data comes from all
sorts of places, and the data that comes back has no unique record
identifier. I want to add one, that is, effectively add a IDENTITY column to
the query result.
I have put the data from the UNION query into a table variable, called
@.myResults (I could put it into a temp table #myResult instead, if you
care). This kind of makes my problem simpler to see, but be aware that the
source tables have no unique identifier I can use. The column "myID" I have
just made up with zero value, in case it can be used.
SELECT myID, Color FROM @.myResults -- Simplified example, this is
what I get
0 Blue
0 Red
0 Green
I want to add a column or update the myID column so that it looks like this,
counting each row
-- This is what I want. How?!?!
1 Blue
2 Red
3 Green
Simple, eh? That's what I thought.
I have tried this --
SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
work
and
SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has number
3 on each row
what I want is something like this --
SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt as
far as I can tell
As a general thing, I'm not sure how to add an identity column to a table
that already has data in it. That's kind of what I am trying to do, but to a
select statement result.
I have even considered looping through each record in a cursor and manually
updating the int. Seems like a lot of work, and this stored procedure is
going to get hit a lot and needs to be fairly fast.
Mostly I'm just burning up because I *know* there is a simple answer to
this - I just can't see it!
Thanks in advance,
SaulLook at the IDENTITY function in Books Online; you basically want to do
something like:
SELECT IdentColumn = IDENTITY(int, 1,1),
OTHERColumns
INTO TargetTable --must be a table or temp table
FROM @.myResults
HTH
Stu|||I suggest you to insert data into a #TempTable like
Select Identity(int, 1, 1) as RowNumber, * Into #TempTableName From TableNam
e
-- That should generate record numbers for you
HTH
Ed
"Saul" wrote:

> Hi all,
> I am pretty much going insane. I have tried all sorts of things and gotten
> nowhere, and I'm fairly sure there exists a simple solution to my problem.
> If you help me, I will be eternally in your debt.
> Here is the scenario. I have a stored procedure which conains a fairly hea
vy
> UNION query, which I'm not going to repeat here. The data comes from all
> sorts of places, and the data that comes back has no unique record
> identifier. I want to add one, that is, effectively add a IDENTITY column
to
> the query result.
> I have put the data from the UNION query into a table variable, called
> @.myResults (I could put it into a temp table #myResult instead, if you
> care). This kind of makes my problem simpler to see, but be aware that the
> source tables have no unique identifier I can use. The column "myID" I hav
e
> just made up with zero value, in case it can be used.
> SELECT myID, Color FROM @.myResults -- Simplified example, this is
> what I get
> 0 Blue
> 0 Red
> 0 Green
> I want to add a column or update the myID column so that it looks like thi
s,
> counting each row
> -- This is what I want. How?!?!
> 1 Blue
> 2 Red
> 3 Green
> Simple, eh? That's what I thought.
> I have tried this --
> SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
> work
> and
> SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has numbe
r
> 3 on each row
> what I want is something like this --
> SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt a
s
> far as I can tell
> As a general thing, I'm not sure how to add an identity column to a table
> that already has data in it. That's kind of what I am trying to do, but to
a
> select statement result.
> I have even considered looping through each record in a cursor and manuall
y
> updating the int. Seems like a lot of work, and this stored procedure is
> going to get hit a lot and needs to be fairly fast.
> Mostly I'm just burning up because I *know* there is a simple answer to
> this - I just can't see it!
> Thanks in advance,
> Saul
>
>
>|||Guys,
Thank you for your responses!!
Yes, what you suggested works, and works quite well. What I guess I don't
like about it is that this means creating a temp table to solve the problem.
But it does work, so I'm not complaining! So, thanks again!!
On the way down to lunch, I thought of another solution though, which I like
better and also works. Here's the idea - when I intially declare the table
variable (@.myResults) I define an indentity column there. Then, when I do
the insert into.., I don't insert into that ID column, and it takes care of
creating the identity. What I preffer about this solution is that the
identity is built the first time when the data is being inserted.
eg
declare @.myResults table (my_ID int identity(1,1) , colour varchar(20))
insert into @.myResults
SELECT colour
FROM table1
UNION
SELECT colour
FROM table2
Of course, my real world query is vastly more complicated, but it's for
illustration purposes.
- Saul
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:7088713E-1340-4D8D-9328-C8F0DEAF251B@.microsoft.com...
>I suggest you to insert data into a #TempTable like
> Select Identity(int, 1, 1) as RowNumber, * Into #TempTableName From
> TableName
> -- That should generate record numbers for you
> HTH
> Ed
>
> "Saul" wrote:
>|||There is no "simple" way of doing this, and there may not every be. The
issue here is that you need to something to order the data on. If you have
some unique value, and you want to add a sequence number, you can do
something like:
select 'Blue' as color
into #testtable
union all
select 'Red'
union all
select 'Green'
select color, (select count(*) from #testTable as t2 where t2.color <=
#testTable.color) as rowNumber
from #testTable
order by 2
To do a non-sortable order, you will need to build the data first (don't
expect the order you see from a select to always be the order of the
results. There are no guarantees with row order in a relational database.)
(note, in 2005 there will be an easier way to do this, but the same
limitations do exist.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Saul" <sbryan@.nsw.counterpoint.com.au> wrote in message
news:4355a839$0$1360$c30e37c6@.ken-reader.news.telstra.net...
> Hi all,
> I am pretty much going insane. I have tried all sorts of things and gotten
> nowhere, and I'm fairly sure there exists a simple solution to my problem.
> If you help me, I will be eternally in your debt.
> Here is the scenario. I have a stored procedure which conains a fairly
> heavy UNION query, which I'm not going to repeat here. The data comes from
> all sorts of places, and the data that comes back has no unique record
> identifier. I want to add one, that is, effectively add a IDENTITY column
> to the query result.
> I have put the data from the UNION query into a table variable, called
> @.myResults (I could put it into a temp table #myResult instead, if you
> care). This kind of makes my problem simpler to see, but be aware that the
> source tables have no unique identifier I can use. The column "myID" I
> have just made up with zero value, in case it can be used.
> SELECT myID, Color FROM @.myResults -- Simplified example, this is
> what I get
> 0 Blue
> 0 Red
> 0 Green
> I want to add a column or update the myID column so that it looks like
> this, counting each row
> -- This is what I want. How?!?!
> 1 Blue
> 2 Red
> 3 Green
> Simple, eh? That's what I thought.
> I have tried this --
> SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
> work
> and
> SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has
> number 3 on each row
> what I want is something like this --
> SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt
> as far as I can tell
> As a general thing, I'm not sure how to add an identity column to a table
> that already has data in it. That's kind of what I am trying to do, but to
> a select statement result.
> I have even considered looping through each record in a cursor and
> manually updating the int. Seems like a lot of work, and this stored
> procedure is going to get hit a lot and needs to be fairly fast.
> Mostly I'm just burning up because I *know* there is a simple answer to
> this - I just can't see it!
> Thanks in advance,
> Saul
>
>

Creating an "ALL" Parameter Value

I am trying to create an "All" parameter. I created a stored procedure that says:

Code Snippet

CREATE PROCEDURE dbo.Testing123

AS

SELECT distinct ID AS ID, ID AS Label

FROM TPFDD

UNION

SELECT NULL AS ID, 'ALL' AS Label

FROM TPFDD
Order by ID
GO

Then I createded a report parameter and set the default to All

I also created a filter that sets the textbox vaule to the report parameter.

In theory I think that when I select ALL it should bring back everything but it is not. It brings back nothing. What am i doing wrong?

In the query that returns the data for the report, I suspect you're doing something like:

WHERE id = @.ID

What you would need to do is:

WHERE id = @.ID OR @.ID IS NULL

I think that you should be setting your default value to NULL instead of "ALL", that would remove the need for your filter.

|||

Is this what you mean?

Code Snippet

CREATE PROCEDURE dbo.Testing123

@.id char

AS

SELECT distinct ID AS ID, ID AS Label

FROM TPFDD

UNION

SELECT NULL AS ID, 'ALL' AS Label

FROM TPFDD

Where ID = @.id or @.id is NULL

Order by ID

Should this make it work?

I am also getting this error: "The report parameter 'pid' has a DefaultValue or ValidValue that depends on the report parameter "pid" Forward dependencies are not valid."

|||

No, I meant for you to put it in the query that is returning the data for your report, not for the parameter.

I am assuming that what you have currently is a parameter as a drop down box where they select the id from the list generated by the query that you have posted above. Then, the user presses "view Report" and a report for that id is executed with data filled in by some other query, that currently has

Where ID = @.id

to return just the data for the id you have selected.

If you add

or @.id is NULL

to the where clause, it will detect if the user has selected ALL and not filter the results.

Maybe I have misunderstood what you're trying to do?

|||You were right! Thank you so much for your help with this

Creating a VIEW

I'm trying to create a view in EM.
SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
FROM dbo.wf_styles LEFT OUTER JOIN
dbo.wf_bom ON dbo.wf_styles.code =
dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
WHERE (dbo.wf_styles.SA_Active = 'Y')
ORDER BY dbo.wf_styles.code
The error message I get was "Incorrect syntax near '100'". I didn't put "TOP
100 PERCENT" there (not sure what it does...) but EM puts it back after I
delete it. How can I get this to work?
Thanks!!
But theWill,
The ORDER BY clause cannot be used in a view definition without the TOP
clause listed. Do you really want to include the ORDER BY in the view
definition? Could cause unexpected results when querying the view and using
a different ORDER BY clause.
HTH
Jerry
"will" <will@.discussions.microsoft.com> wrote in message
news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
> I'm trying to create a view in EM.
> SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
> dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
> FROM dbo.wf_styles LEFT OUTER JOIN
> dbo.wf_bom ON dbo.wf_styles.code =
> dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
> WHERE (dbo.wf_styles.SA_Active = 'Y')
> ORDER BY dbo.wf_styles.code
> The error message I get was "Incorrect syntax near '100'". I didn't put
> "TOP
> 100 PERCENT" there (not sure what it does...) but EM puts it back after I
> delete it. How can I get this to work?
> Thanks!!
> But the|||Will,
Also move
AND dbo.wf_bom.raw_type = 'F'
to the WHERE clause.
HTH
Jerry
"will" <will@.discussions.microsoft.com> wrote in message
news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
> I'm trying to create a view in EM.
> SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
> dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
> FROM dbo.wf_styles LEFT OUTER JOIN
> dbo.wf_bom ON dbo.wf_styles.code =
> dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
> WHERE (dbo.wf_styles.SA_Active = 'Y')
> ORDER BY dbo.wf_styles.code
> The error message I get was "Incorrect syntax near '100'". I didn't put
> "TOP
> 100 PERCENT" there (not sure what it does...) but EM puts it back after I
> delete it. How can I get this to work?
> Thanks!!
> But the|||(a) write your VIEW in Query Analyzer, not Enterprise Mangler
(b) update your database to not be in 6.5 compatibility mode, where TOP
wasn't supported.
"will" <will@.discussions.microsoft.com> wrote in message
news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
> I'm trying to create a view in EM.
> SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
> dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
> FROM dbo.wf_styles LEFT OUTER JOIN
> dbo.wf_bom ON dbo.wf_styles.code =
> dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
> WHERE (dbo.wf_styles.SA_Active = 'Y')
> ORDER BY dbo.wf_styles.code
> The error message I get was "Incorrect syntax near '100'". I didn't put
> "TOP
> 100 PERCENT" there (not sure what it does...) but EM puts it back after I
> delete it. How can I get this to work?
> Thanks!!
> But the|||> Also move
> AND dbo.wf_bom.raw_type = 'F'
> to the WHERE clause.
No - this is the unpreserved table in an outer join. Doing this changes the
semantics of the query.|||I need that to be part of the JOIN condition because it's a LEFT JOIN.
"Jerry Spivey" wrote:

> Will,
> Also move
> AND dbo.wf_bom.raw_type = 'F'
> to the WHERE clause.
> HTH
> Jerry
> "will" <will@.discussions.microsoft.com> wrote in message
> news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
>
>|||Ok, I took out the ORDER BY, now I'm getting:
Error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.wf_styles'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.wf_bom'.
Query:
SELECT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
FROM dbo.wf_styles LEFT OUTER JOIN
dbo.wf_bom ON dbo.wf_styles.code =
dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
WHERE (dbo.wf_styles.SA_Active = 'Y')
Thanks.
"Jerry Spivey" wrote:

> Will,
> The ORDER BY clause cannot be used in a view definition without the TOP
> clause listed. Do you really want to include the ORDER BY in the view
> definition? Could cause unexpected results when querying the view and usi
ng
> a different ORDER BY clause.
> HTH
> Jerry
> "will" <will@.discussions.microsoft.com> wrote in message
> news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
>
>|||Ok...that I've never seen before. Would you mind elaborating on that for my
understanding?
Thanks Scott.
Jerry
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23PbXC1Z2FHA.1576@.TK2MSFTNGP15.phx.gbl...
> No - this is the unpreserved table in an outer join. Doing this changes
> the semantics of the query.
>|||wf_styles and wf_bom has a 1-to-many relationship. One line in wf_styles and
many wf_bom lines for each item (bom = Bill of Materials, what each item
contains). I only want to join where the wf_bom.raw_type = 'F'. If it's not
F, return NULL. If you put it in the WHERE condition, the results returned
would only be items that have F, which is not really what I wanted.
Basically, I want a list of all the styles in my table. If I have the amount
of fabric yards that style uses, display that too. That's why I need the LEF
T
JOIN. If you were to put that clause in the WHERE, it would be "Display all
styles in the table and the fabric yards it uses". But that won't work for m
e
since I have some styles where I don't have the fabric yards. Does that
explain it?
"Jerry Spivey" wrote:

> Ok...that I've never seen before. Would you mind elaborating on that for
my
> understanding?
> Thanks Scott.
> Jerry
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23PbXC1Z2FHA.1576@.TK2MSFTNGP15.phx.gbl...
>
>

Creating a TXT file with SQL

Hi , I am new in this group
I have a simple question.
How can i create a TXT file from a SQL statement?
Something like this
Select * from table to Txtfile
is there any way?
Tks
Carlos Lages
Rio de JaneiroThis is something I've used before for a business case that needed me to create a text file with the latest primary key (sequential ID)

DECLARE @.sCmd varchar(256)
declare @.maxid varchar(12)
set @.maxid = (select max(pk) from test_tbl)
SET @.sCmd= 'echo>c:\temp\test.txt The new Primary Key is -' + @.maxid
EXEC master..xp_cmdshell @.sCmd, no_output

You can play with this and get what you need to throw to the text file or post back if you need help.

HTH|||rhigdon

tks
i will change to my needs , but i think will work fine

because what i realy need is select several fields from
several tables and create a txt file.
Carlos Lages|||THis article may be of help as well

http://www.sqlteam.com/item.asp?ItemID=4722

HTH

creating a table with select into

Hi,
how can i create a new table from a select statement? i use select into, but
my select query has case columns and query analyzer says that i did not
specified a column for them. how do i do it?
thanks in advance for your helpGive the column a name. Make this:
SELECT somecolum, CASE when x then y else z END
INTO #table
FROM table
look like this:
SELECT somecolum, CASE when x then y else z END AS SomeNewColumn
INTO #table
FROM table
or this:
SELECT somecolum, 'SomeNewColumn' = CASE when x then y else z END
INTO #table
FROM table
Keith
"Jose" <norespondais@.almailpor.favor> wrote in message
news:uNmJgiTDFHA.1396@.tk2msftngp13.phx.gbl...
> Hi,
> how can i create a new table from a select statement? i use select into,
but
> my select query has case columns and query analyzer says that i did not
> specified a column for them. how do i do it?
> thanks in advance for your help
>sql

Creating a table from data in a SQL server db

I have a form with a drop down box so the user can select a quote.. When a quote is selected i need to populate a table of all the records associated with the quote id. I need the table to be created in such a way that the user can add new rows, delete rows and edit the data. Then all of the changes need to be written back to the database. Whats the most efficient/best way of doing this and if you have any ideas can you explain them as thoroughly as possible! I'm currently upgrading an access database to a sql server back end with an asp.net client and it's taking me a while to get to grips with all the changes!
Thanks in advance,
Chrisyou could create a dataset/datatable at the front end with the samestructure as the actual table in the db. You can then use thedataset/datatable and do all the modifications and push the entiretable back to the sql db. check out articles about datasets and youwould find enough info.
sql

Creating a table by executing a storedproc

It is possible to create a table by executing a storedproc, similar to using
the SELECT...INTO...FROM TABLE but replacing the FROM TABLE clause by the
execution a a storedproc.
thanks in advanceIt is not possible.
You may have to create the table and then yo can use an EXEC Sproc to
populate it with the Result Set.
You may use Base Table or a # Table to populate but not the @.Table.
--
HTH
Satish Balusa
Corillian Corp.
"Benoit Drapeau" <review@.videotron.ca> wrote in message
news:ej1mS1P5DHA.2168@.TK2MSFTNGP12.phx.gbl...
> It is possible to create a table by executing a storedproc, similar to
using
> the SELECT...INTO...FROM TABLE but replacing the FROM TABLE clause by the
> execution a a storedproc.
> thanks in advance
>|||It's possible by creating table first covering all the columns displayed by
the sotred procedure:
create table test (col1, col2, col3...)
insert into table exec ('stored proc')
richard
"Benoit Drapeau" <review@.videotron.ca> wrote in message
news:ej1mS1P5DHA.2168@.TK2MSFTNGP12.phx.gbl...
> It is possible to create a table by executing a storedproc, similar to
using
> the SELECT...INTO...FROM TABLE but replacing the FROM TABLE clause by the
> execution a a storedproc.
> thanks in advance
>|||A workaround is to use OPENROWSET or OPENQUERY:
SELECT * INTO #tmp
FROM OPENROWSET('SQLOLEDB', 'machine';'login';'pwd', 'EXEC sp_help')
SELECT * FROM #tmp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Benoit Drapeau" <review@.videotron.ca> wrote in message
news:ej1mS1P5DHA.2168@.TK2MSFTNGP12.phx.gbl...
> It is possible to create a table by executing a storedproc, similar to using
> the SELECT...INTO...FROM TABLE but replacing the FROM TABLE clause by the
> execution a a storedproc.
> thanks in advance
>|||Thanks, this is exactly wat I was looking for.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O9U1m#Y5DHA.2392@.TK2MSFTNGP11.phx.gbl...
> A workaround is to use OPENROWSET or OPENQUERY:
> SELECT * INTO #tmp
> FROM OPENROWSET('SQLOLEDB', 'machine';'login';'pwd', 'EXEC sp_help')
> SELECT * FROM #tmp
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Benoit Drapeau" <review@.videotron.ca> wrote in message
> news:ej1mS1P5DHA.2168@.TK2MSFTNGP12.phx.gbl...
> > It is possible to create a table by executing a storedproc, similar to
using
> > the SELECT...INTO...FROM TABLE but replacing the FROM TABLE clause by
the
> > execution a a storedproc.
> >
> > thanks in advance
> >
> >
>

Creating a table by executing a storedproc

It is possible to create a table by executing a storedproc, similar to using
the SELECT...INTO...FROM TABLE but replacing the FROM TABLE clause by the
execution a a storedproc.
thanks in advanceIt is not possible.
You may have to create the table and then yo can use an EXEC Sproc to
populate it with the Result Set.
You may use Base Table or a # Table to populate but not the @.Table.
HTH
Satish Balusa
Corillian Corp.
"Benoit Drapeau" <review@.videotron.ca> wrote in message
news:ej1mS1P5DHA.2168@.TK2MSFTNGP12.phx.gbl...

quote:

---

> It is possible to create a table by executing a storedproc, similar to

---

using

quote:

---

> the SELECT...INTO...FROM TABLE but replacing the FROM TABLE clause by the
> execution a a storedproc.
> thanks in advance
>

---

|||It's possible by creating table first covering all the columns displayed by
the sotred procedure:
create table test (col1, col2, col3...)
insert into table exec ('stored proc')
richard
"Benoit Drapeau" <review@.videotron.ca> wrote in message
news:ej1mS1P5DHA.2168@.TK2MSFTNGP12.phx.gbl...

quote:

---

> It is possible to create a table by executing a storedproc, similar to

---

using

quote:

---

> the SELECT...INTO...FROM TABLE but replacing the FROM TABLE clause by the
> execution a a storedproc.
> thanks in advance
>

---

|||A workaround is to use OPENROWSET or OPENQUERY:
SELECT * INTO #tmp
FROM OPENROWSET('SQLOLEDB', 'machine';'login';'pwd', 'EXEC sp_help')
SELECT * FROM #tmp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Benoit Drapeau" <review@.videotron.ca> wrote in message
news:ej1mS1P5DHA.2168@.TK2MSFTNGP12.phx.gbl...

quote:

---

> It is possible to create a table by executing a storedproc, similar to usi
ng
> the SELECT...INTO...FROM TABLE but replacing the FROM TABLE clause by the
> execution a a storedproc.
> thanks in advance
>

---

|||Thanks, this is exactly wat I was looking for.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O9U1m#Y5DHA.2392@.TK2MSFTNGP11.phx.gbl...

quote:

---

> A workaround is to use OPENROWSET or OPENQUERY:
> SELECT * INTO #tmp
> FROM OPENROWSET('SQLOLEDB', 'machine';'login';'pwd', 'EXEC sp_help')
> SELECT * FROM #tmp
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

---

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

---

>
> "Benoit Drapeau" <review@.videotron.ca> wrote in message
> news:ej1mS1P5DHA.2168@.TK2MSFTNGP12.phx.gbl...
using[QUOTE]
the[QUOTE]
>

---

Creating a Table

How do you create a new table from a SELECT statement of another table
using MS SQL Server. This is part of a distributed database topic for
university. Unfortunately I can only seem to get the new table created
in Oracle and not MS."Ritchie" <vartegrich@.aol.com> wrote in message
news:634b2f3c.0402271338.74dac87e@.posting.google.c om...
> How do you create a new table from a SELECT statement of another table
> using MS SQL Server. This is part of a distributed database topic for
> university. Unfortunately I can only seem to get the new table created
> in Oracle and not MS.

I'm not completely sure, but are you looking for this?

select *
into dbo.NewTable
from dbo.ExistingTable
where ...

If you want to copy only the structure, you can use "where 1=2". But in both
cases, you get only the columns - no keys, no constraints, no triggers etc.
If you need to copy those as well, then you'll have to recreate them after
creating the new table.

Simon|||Thanks Simon, if you are not sure, that leaves me high and dry. I'll try to
explain the objective and see if that helps at all. We have to locate certain
records in a particular table (e.g. locate records by city='London') then
relocate all these records into a new table.

so I guess using
SELECT * FROM oldtable WHERE city='London'
works to gather the necessary records.
We tried CREATE TABLE newtable AS, but this of course is for oracle. So what I
need is the SQL to permit this in MS-SQL|||"VARTEGRICH" <vartegrich@.aol.com> wrote in message
news:20040227182545.20278.00000483@.mb-m27.aol.com...
> Thanks Simon, if you are not sure, that leaves me high and dry. I'll try
to
> explain the objective and see if that helps at all. We have to locate
certain
> records in a particular table (e.g. locate records by city='London') then
> relocate all these records into a new table.
> so I guess using
> SELECT * FROM oldtable WHERE city='London'
> works to gather the necessary records.
> We tried CREATE TABLE newtable AS, but this of course is for oracle. So
what I
> need is the SQL to permit this in MS-SQL

Perhaps my previous post wasn't very clear - you can do this:

select *
into dbo.NewTable
from dbo.OldTable
where city = 'London'

That will create NewTable and insert the records you require, but as I said,
it will have no keys, constraints, indexes etc. If you need those, then you
can use Enterprise Manager to generate the CREATE TABLE script for OldTable,
modify the script as needed (change the table name, constraint names etc),
run it to create NewTable and then do this:

insert into dbo.NewTable
select *
from dbo.OldTable
where city = 'London'

Simon|||Cheers Simon,
works a treat.
Pity the tutors don't know how to do this though.
Would make life a lot easier.
Thanks Again

Creating a Subtotal of select Groups

Hi,

I am working on a new reporting system using reporting services, but I cannot figure out how to create a footer row which will only subtotal select group totals. If anyone has a method to do this please help!

Nathan

If you have a matrix report right click on the group and select the option subtotal.
If you have a tabular report right click on the left side of the table and select table footer. Then in each field you want to summarize put = SUM(Fields!FieldName.Value)
That's all|||

I've been able to do that for individual groups, but what I want to do is make footer subtotal of a select set of groups. So say I have data grouped by Credit card type. I have a group for MC, and another for Visa, and another for American express.

I want a footer total of just the MC and Visa groups, excluding the total for American express.

|||You could use an expression similar to this in the footer (it will add 0 instead of the actual amount if the card was Amex):
=Sum(iif(Fields!CardType.Value = "Amex", 0, Fields!TransactionAmount.Value))

--Robert|||Alright!

Thank you Robert. you made my day |||

When running this selective sum, i get a scope error. I have tried giving it a group name and a dataset. What am i doing wrong? here is my code:

=Sum(iff(Fields!CardType.Value = "Visa/Mc" OR Fields!cardType.Value = "Diner" OR Fields!cardType.Value = "JCB", Fields!amount.Value, 0))
This is my error:

"The value expression for the textbox ‘textbox9’ refers to the field ‘CardType’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."

Whats wrong?

|||I have the same problem. I don't want to sum a select number of groups, but all groups within the report. The principle is the same as the above, and I get the same scope error.
In my case I have a bunch of items grouped by customer. Each customer has a subtotal, and I want to have a grand total of all the customers.
Any ideas/workarounds?|||Note: Field names are case-sensitive. In your expression it seems like you have upper-case and lower-case "CardType" fields.
Also, are the cardType field and the amount field in the same dataset?

-- Robert|||If you want to get the grand total, you just need to specify either the data region name (i.e. table, list, or matrix report item name) or the data set name.
E.g.
=Sum(Fields!Amount.Value, "DataSet1")

-- Robert|||Thanks, Robert! I knew it had to be something simple. |||Hi, somehow related with the topic:
Is posible to have something like: the sum of the ValueField from all the rows of DataSet2 that have CompareFiled equal with the current value of ComparedWithField from DataSet1?
In other words: in the expression of the SUM function can be used more then one scope?
=Sum(iff( DS2!Fields!CardType.Value = Fields!CT.Value, DS2!Fields!amount.Value, 0))

creating a stored procedure-- help

im novice to sqlserver and stored procedures.
Can the php code below be converted to a stored procedure
$y=1;
$query = "Select * From tblNews Order By aOrder";
$result = mysql_query($query,$db_connection);
$NoRows = mysql_num_rows($result);
if ($NoRows != 0 )
{
while ($row = mysql_fetch_array($result))
{
$UpdateQuery = "Update tblNews
Set aOrder= $y
Where ID=".$row["ID"];
mysql_query($UpdateQuery,$db_connection)
;
$y++;
}
}
basically, i want select all from tblNews, order by aOrder
then update aOrder in each record starting at 1 and incrementing by 1
untill all the records have been processed
Can anyone help me please, is it possible
thanks in advance
SteveIf all values in column [aOrder] are diff, then you can try,
update tblNews
set aOrder = (select count(*) from tblNews as a where a.aOrder <=
tblNews.aOrder)
AMB
"ahoy hoy" wrote:

> im novice to sqlserver and stored procedures.
> Can the php code below be converted to a stored procedure
> $y=1;
> $query = "Select * From tblNews Order By aOrder";
> $result = mysql_query($query,$db_connection);
> $NoRows = mysql_num_rows($result);
> if ($NoRows != 0 )
> {
> while ($row = mysql_fetch_array($result))
> {
> $UpdateQuery = "Update tblNews
> Set aOrder= $y
> Where ID=".$row["ID"];
> mysql_query($UpdateQuery,$db_connectio
n);
> $y++;
> }
> }
>
> basically, i want select all from tblNews, order by aOrder
> then update aOrder in each record starting at 1 and incrementing by 1
> untill all the records have been processed
> Can anyone help me please, is it possible
> thanks in advance
> Steve
>|||
Steve,
You could use something along the lines of this... (Un-Tested)
Create Proc TestProcedure
As Begin
Declare @.ID Integer
Declare @.NewOrder Integer
Set @.NewOrder = 1
Declare OrderCursor Cursor For
Select ID From tblNews
Order By aOrder
Open OrderCursor
Fetch Next From OrderCursor Into @.ID
While @.@.Fetch_Status = 0
Begin
Update tblNews
Set aOrder = @.NewOrder
Where ID = @.ID
Set @.NewOrder = @.NewOrder + 1
Fetch Next From OrderCursor Into @.ID
End
Close OrderCursor
Deallocate OrderCursor
End
Go
Although if it is a huge amount of Data and performance is an issue
then I would probably not use a Cursor.
Hope this helps
Barry|||Barry
thank you so much!
i wouldve been trying to figure that out for days, it is exactly what i
needed.
Just needed to use the correct field names and rename Interger to Int,
proc to procedure!
Now i can finish my job
Its only for a small amount of data, 10-20 records
Awesome
Steve :)
Barry wrote:

> Steve,
>
> You could use something along the lines of this... (Un-Tested)
>
> Create Proc TestProcedure
> As Begin
>
> Declare @.ID Integer
> Declare @.NewOrder Integer
> Set @.NewOrder = 1
>
> Declare OrderCursor Cursor For
> Select ID From tblNews
> Order By aOrder
>
> Open OrderCursor
> Fetch Next From OrderCursor Into @.ID
>
> While @.@.Fetch_Status = 0
> Begin
>
> Update tblNews
> Set aOrder = @.NewOrder
> Where ID = @.ID
> Set @.NewOrder = @.NewOrder + 1
> Fetch Next From OrderCursor Into @.ID
> End
> Close OrderCursor
> Deallocate OrderCursor
>
> End
> Go
>
> Although if it is a huge amount of Data and performance is an issue
> then I would probably not use a Cursor.
> Hope this helps
> Barry
>

Creating a Select statement with subqueries to 3 other tables...

I have four total tables.

Table One (Documents)- List of Documents. Each record has two fields
related to this issue. First field (Document_ID) is the ID of the
document, second field is the ID of the record (Task_ID) it is
associated to in Table Two.

Table Two (Activities)- List of activities. Each record has two fields
related to this issue. First field (Activity_ID) is the ID of the
activity, the second field (Group_ID) is the ID of the record it is
associated to in Table Three.

Table Three (Groups) - List of groups. Each record has two fields
related to this issue. First field (Group_ID) is the ID of the group,
the second field (Stage_ID) is the ID of the record it is associated to
in Table four.

Table Four (Stages)- List of Event Stages. Each record has two fields
that is related to this issue. The first field (Stage_ID) is the ID of
the stage of an event, the second record is the ID number associated to
the event. This last ID is a known value.

20000024 = the Event ID

I'm trying to come up with a list of Documents from the first table
that is associated to an Event in the Fourth table.

Query Analyzer shows no errors within the script. It just doesn't
return any data. I know that it should, if it does what I'm wanting it
to do.

SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROM
Stages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C WHERE
(C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE (B.Activity_ID
= A.Activity_ID))))))= '20000024')Wenin wrote:
> I have four total tables.
>
> Table One (Documents)- List of Documents. Each record has two fields
> related to this issue. First field (Document_ID) is the ID of the
> document, second field is the ID of the record (Task_ID) it is
> associated to in Table Two.
> Table Two (Activities)- List of activities. Each record has two
fields
> related to this issue. First field (Activity_ID) is the ID of the
> activity, the second field (Group_ID) is the ID of the record it is
> associated to in Table Three.
> Table Three (Groups) - List of groups. Each record has two fields
> related to this issue. First field (Group_ID) is the ID of the
group,
> the second field (Stage_ID) is the ID of the record it is associated
to
> in Table four.
> Table Four (Stages)- List of Event Stages. Each record has two
fields
> that is related to this issue. The first field (Stage_ID) is the ID
of
> the stage of an event, the second record is the ID number associated
to
> the event. This last ID is a known value.
> 20000024 = the Event ID
> I'm trying to come up with a list of Documents from the first table
> that is associated to an Event in the Fourth table.
> Query Analyzer shows no errors within the script. It just doesn't
> return any data. I know that it should, if it does what I'm wanting
it
> to do.
>
> SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROM
> Stages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C
WHERE
> (C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE
(B.Activity_ID
> = A.Activity_ID))))))= '20000024')

I'm thinking this is going to require the use of Joins, but I still
can't seem to wrap my head around how joins work exactly.|||First, to save Joe Celko from having to post...

Rows are not records, columns are not fields. You are thinking in the
old sequential file processing mentality. It will take you a year to
unlearn this and get your mind accustomed to thinking in a set-oriented
fashion.

Ok, now that that's out of the way...

You are correct, it will require the use of joins. Please don't take
this as an insult, as I'm sure that you are still learning SQL and
there was a time for all of us when we didn't know it, but this is a
pretty simple set of joins. You really do need to "get your head around
how joins work exactly" if you are going to be doing any SQL coding.
There are plenty of good books for SQL beginners out there. Even the
SQL for Dummies book should get you past this hurdle.

A join takes each table being joined, creates a cartesian product of
the two (i.e., every possible combination of records) then filters that
result based on the ON part of the join. As an example, if I have two
tables:

Customers
cust_id
1
2

Orders
ord_id cust_id
1 1
2 2
3 1

The cartesian product would be:
cust_id ord_id cust_id
1 1 1
1 2 2
1 3 1
2 1 1
2 2 2
2 3 1

If I joined these tables on cust_id = cust_id then it would give me:
cust_id ord_id cust_id
1 1 1
1 3 1
2 2 2

Without the join criteria the cartesian product is useless, but it is
the starting point.

Another way to think of it is this... I know my two tables are related
and I know the column that relates them. Therefore I can join on that
column. That is a VERY simplistic approach to joins, but it will
hopefully point you in the right direction.

Good luck,
-Tom.

Creating a report from a Stored Procedure

Hi

I'm not sure if this is possible, i want to create a report from a stored procedure, all the stored procedure does is select data from my database, however, the code is 1800+ lines of code, it executes in about 1 minute, which is fine. My problem is that i can't paste 1800+ lines of code into the dataset in Reporting Services, so is it possible to get the values from my stored procedure into reporting services so that i can use them to design and execute my report?

Or musst i find an alternative way to do this?

Any help or suggestions will be greatly appreaciated and welcome.

Thanks in advance

Kind Regards

Carel Greaves

If you have a data source pinting to your database, go to Visual Studio and create a dataset for your rdl and in the Query String type exec SProc_Name @.Param1, @.Param2 .. etc. Make sure the command type is text.

That should do it.
|||

Thanks, strangely enough it was on text, and i set it to stored procedure, and then changed it back, now it works.

Thanks Gatharia

Creating a Publication

Hi,
I am trying to create a publication and when i select all tables then there
are certain tables which cannot be published. I get a key with a cross sign
on it.
Whats the reason and how to overcome this? coz I want to publish all tables.
Any help is highly appreciated.
Thanks
pmud
It seems you're using transactional replication and the key with a cross
sign means that this table can't be replicated because it doesn't have a
primary key. This script will list all these tables:
select * from information_schema.tables
where table_type = 'base table'
and objectproperty(object_id(table_name),'IsMsShipped' ) = 0
and objectproperty(object_id(table_name),'TableHasPrim aryKey') = 0
Using this script you can find them, add the necessary PKs and then add them
to your publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Thanks for the answer. I have a doubt. I have to write this script in query
analyzer after creating the publication? Also , some tables will not have a
primary key. is there no way these tables can be replicated?
Thanks for any help.
pmud
"Paul Ibison" wrote:

> It seems you're using transactional replication and the key with a cross
> sign means that this table can't be replicated because it doesn't have a
> primary key. This script will list all these tables:
> select * from information_schema.tables
> where table_type = 'base table'
> and objectproperty(object_id(table_name),'IsMsShipped' ) = 0
> and objectproperty(object_id(table_name),'TableHasPrim aryKey') = 0
> Using this script you can find them, add the necessary PKs and then add them
> to your publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||This script is to identify those tables requiring a PK and which don't have
one. The PK is mandatory for transactional replication. Otherwise you could
use merge or snapshot for those extra tables. Have a look in books on line
(BOL) for more details of the differences between these types of
replication - this is a good starting point.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul...That was helpful
pmud
"Paul Ibison" wrote:

> This script is to identify those tables requiring a PK and which don't have
> one. The PK is mandatory for transactional replication. Otherwise you could
> use merge or snapshot for those extra tables. Have a look in books on line
> (BOL) for more details of the differences between these types of
> replication - this is a good starting point.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

Creating a primary key as a non clustered index

Hi,

I have created a very simple table. Here is the script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]

GO

CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO

The records that i added are:

Id Code

1 a
2 b
3 aa
4 bb

Now when i query like

Select * from IndexTable

I expect the results as:

Id Code

1 a
3 aa
2 b
4 bb

as i have the clustered index on column Code.

But i m getting the results as:

Id Code

1 a
2 b
3 aa
4 bb

as per the primary key order that is a non clustered index.

Can anyone explain why it is happening?

Thanks

Nitin

It appears to me from the code above that you actually created the clustered index on the Id field.|||

As rottengeek noticed, you are creating the clustered index on column [Id], but even if you create it on column [code], does not expect any specific order if you are not using the "order by" clause in your "select" statement. That is the only way to assure a specific order.

Quaere Verum - Clustered Index Scans - Part I

http://www.sqlmag.com/articles/index.cfm?articleid=92886&

Quaere Verum - Clustered Index Scans - Part II

http://www.sqlmag.com/articles/index.cfm?articleid=92887&

Quaere Verum - Clustered Index Scans - Part III

http://www.sqlmag.com/articles/index.cfm?articleid=92888&

AMB

|||

you are correct, so i have modified it to have the clustered index on the Code field.

Hi,

I have created a very simple table. Here is the script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]

GO

CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Code]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO

The records that i added are:

Id Code

1 a
2 b
3 aa
4 bb

Now when i query like

Select * from IndexTable

I expect the results as:

Id Code

1 a
3 aa
2 b
4 bb

as i have the clustered index on column Code.

But i m getting the results as:

Id Code

1 a
2 b
3 aa
4 bb

as per the primary key order that is a non clustered index.

Can anyone explain why it is happening?

Thanks

Nitin

Creating a partitioned view based on results from select?

I have a situation which seems like it should have a fairly simple
answer... but I'm not sure how to do it.
I want to create a view that joins several remotely distributed tables.
Something like:
CREATE VIEW AllData
AS
SELECT * FROM Server1.SomeDB.dbo.DataTable
UNION ALL
SELECT * FROM Server2.SomeDB.dbo.DataTable
...
SELECT * FROM ServerN.SomeDB.dbo.DataTable
The problem is that we anticipate some downtime or discontinuity with
our server links. Will the view fail if just ONE server can't be
reached? I think it will... please correct me if I'm wrong.
So, to solve this we have another table which stores the server name
and a status field. The question is how do I take this list of
available servers and turn it into a valid view? Is this going to
require some form of dynamic SQL? I'm picturing creating a trigger so
that any time the status DB changes the view gets recreated.status table could be out of sync and you will still get error. Long story
short, there isn't anything builtin right now to allow you precheck for
linked server status other than sending a query to it.
-oj
<bryanp10@.hotmail.com> wrote in message
news:1113511868.193053.138020@.l41g2000cwc.googlegroups.com...
>I have a situation which seems like it should have a fairly simple
> answer... but I'm not sure how to do it.
> I want to create a view that joins several remotely distributed tables.
> Something like:
> CREATE VIEW AllData
> AS
> SELECT * FROM Server1.SomeDB.dbo.DataTable
> UNION ALL
> SELECT * FROM Server2.SomeDB.dbo.DataTable
> ...
> SELECT * FROM ServerN.SomeDB.dbo.DataTable
>
> The problem is that we anticipate some downtime or discontinuity with
> our server links. Will the view fail if just ONE server can't be
> reached? I think it will... please correct me if I'm wrong.
> So, to solve this we have another table which stores the server name
> and a status field. The question is how do I take this list of
> available servers and turn it into a valid view? Is this going to
> require some form of dynamic SQL? I'm picturing creating a trigger so
> that any time the status DB changes the view gets recreated.
>|||OK... not quite what I was asking, but thanks. :)
Basically, I'm useless in T-SQL and what I really need to know is how
to transform a table of server names into:
select * from server1
UNION ALL
select * from server2
...
etc.
Really this is just a basic T-SQL question. In ANY other programming
language I've ever seen, this would be simplicity itself. But for some
reason T-SQL just baffles me.
The other question was whether this absolutely requires dynamic SQL or
not. At this point I'm thinking I might just build the create view
procedure in my app where I have a real language to do it.

creating a function to be used in select query

I have this select query which returns a date. I would like to be able to
call this from a stored procedure and have the result appear in the result
set from the stored procedure.
i.e. SELECT *, CalcDate
FROM Table
WHERE somedate = @.dte
The CalcDate field would be the c.dt in the following select statement.
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day, 25, @.dte)
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
How can I do this - I think the above will be a UDF with a return statement
but I am not sure of the syntax.
THanksHi
I assume this subquery does not return more than 1 value.Yes you can write
an UDF to return the date as well , so please refer to the BOL for more info
See if this hepls , I could not tested it since you have not provided DDL+
sample data
SELECT *, (SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day, 25, @.dte)
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
)
) as CalcDate
FROM Table
WHERE somedate = @.dte
in message news:uoSoNyhAGHA.3268@.TK2MSFTNGP10.phx.gbl...
>I have this select query which returns a date. I would like to be able to
>call this from a stored procedure and have the result appear in the result
>set from the stored procedure.
> i.e. SELECT *, CalcDate
> FROM Table
> WHERE somedate = @.dte
> The CalcDate field would be the c.dt in the following select statement.
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND c.dt <= DATEADD(day, 25, @.dte)
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
>
> How can I do this - I think the above will be a UDF with a return
> statement but I am not sure of the syntax.
> THanks
>|||I have tried the following but get the error msg:
The column prefix c does not match with a table name . ..
CREATE FUNCTION dbo.AddWorkDays
(
@.dte smalldatetime,
@.NoDays TINYINT
)
RETURNS SMALLDATETIME
AS
BEGIN
RETURN (SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isWday = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day,25, @.dte)
AND @.NoDays = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isWday=1
AND c2.isHoliday=0
))
END
GO
"Newbie" <nospam@.noidea.com> wrote in message
news:uoSoNyhAGHA.3268@.TK2MSFTNGP10.phx.gbl...
>I have this select query which returns a date. I would like to be able to
>call this from a stored procedure and have the result appear in the result
>set from the stored procedure.
> i.e. SELECT *, CalcDate
> FROM Table
> WHERE somedate = @.dte
> The CalcDate field would be the c.dt in the following select statement.
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isWday = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND c.dt <= DATEADD(day, 25, @.dte)
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isWday=1
> AND c2.isHoliday=0
> )
>
> How can I do this - I think the above will be a UDF with a return
> statement but I am not sure of the syntax.
> THanks
>

Creating a diagram with existing tables

I'd like to create a diagram for my existing tables without wiping them out
in the process. I rightclick & select New Diagram, place the various
tables, create relationships, then save. I get a dialog box asking if I
want to create these tables. No! They have data in them. Unfortunately I
don't see a way to save the relationships and the diagram without blowing
everything away.
Jeremy
The diagram utility operates directly on the underlying tables so if you
only want to add the relationships for documentation or viewing purposes and
not have them permanently saved to the database, then you will need to use
another tool.
--Brian
(Please reply to the newsgroups only.)
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>
|||Brian, thanks. The diagram tool is asking about saving tables, not the
relationships. I can understanding the need to save the relationship, but
don't see why it wants to save my already-existing tables and potentially
blow away the data.
Jeremy
"Brian Lawton" <brian.k.lawton@.redtailcreek.com> wrote in message
news:OW%23thLEnFHA.420@.TK2MSFTNGP09.phx.gbl...
> The diagram utility operates directly on the underlying tables so if you
> only want to add the relationships for documentation or viewing purposes
> and not have them permanently saved to the database, then you will need to
> use another tool.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "JeremyGrand" <jeremy@.ninprodata.com> wrote in message
> news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
>
|||I agree that the messaging in the dialog could certainly be improved however
no matter what changes you make, it will always say that it is saving the
tables. Unless you changed the table structure itself, it should only apply
the relationship constraints via ALTER TABLE syntax and not recreate your
tables. If you want to see exactly how your changes are going to be
applied, there is an option to "Save Change Script" which will generate a
SQL script with the code it plans to apply.
--Brian
(Please reply to the newsgroups only.)
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:uUygVYEnFHA.4064@.TK2MSFTNGP10.phx.gbl...
> Brian, thanks. The diagram tool is asking about saving tables, not the
> relationships. I can understanding the need to save the relationship, but
> don't see why it wants to save my already-existing tables and potentially
> blow away the data.
> Jeremy
> "Brian Lawton" <brian.k.lawton@.redtailcreek.com> wrote in message
> news:OW%23thLEnFHA.420@.TK2MSFTNGP09.phx.gbl...
>
|||By the way, DataAnalyst saves relationship discovered in its own access
file, so it won't touch your original table at all.
Download it at http://www.agileinfollc.com
Eric
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>
|||Hi Jeremy
You might want to look at this product
http://www.ag-software.com/?tabid=17
It is a diagram tool and database compare tool. All details are saved
outside of SQL Server
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:%23Pkl2mDnFHA.3900@.TK2MSFTNGP09.phx.gbl...
> I'd like to create a diagram for my existing tables without wiping them
> out in the process. I rightclick & select New Diagram, place the various
> tables, create relationships, then save. I get a dialog box asking if I
> want to create these tables. No! They have data in them. Unfortunately I
> don't see a way to save the relationships and the diagram without blowing
> everything away.
> Jeremy
>

Creating a column as NOT NULL with "select into"

I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.Try:
create table t
(
ID int identity primary key
, x char (2) null
)
insert t (x) values ('XX')
insert t (x) values ('YY')
go
select
ID
, isnull (x, '--') as y
into
x
from
t
exec sp_help x
go
drop table t, x
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.|||TJ,
A general recommendation is to create the table using DDL.
CREATE TABLE NewTable
(col1 int,
col2 varchar(100)0
go
INSERT INTO NewTable SELECT col1, COALESCE(col2, '') FROM OldTable
go
SELECT INTO is best used for the temporary quick and dirty operation, not
for when you want something to stick around.
Russell Fields
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:##97VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||Your welcomes. ;-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23pCzI8iWEHA.2852@.TK2MSFTNGP12.phx.gbl...
That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>

Creating a column as NOT NULL with "select into"

I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.Try:
create table t
(
ID int identity primary key
, x char (2) null
)
insert t (x) values ('XX')
insert t (x) values ('YY')
go
select
ID
, isnull (x, '--') as y
into
x
from
t
exec sp_help x
go
drop table t, x
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
I am using a select into to create a new table and populate with data.
Select a.col1, a.col2 into NewTable From OldTable
Col2 is defined as NULLible but has no NULL values. The NewTable that is
created has Col2 as NULLible but I would like it to be NOT NULL.
Is there any way to get this Select into statement to create the column as
NOT NULL. I know that I could always alter the table later to change the
NULLibility but if this table has many million rows this Alter would take
some time.
Thanks in advance.|||TJ,
A general recommendation is to create the table using DDL.
CREATE TABLE NewTable
(col1 int,
col2 varchar(100)0
go
INSERT INTO NewTable SELECT col1, COALESCE(col2, '') FROM OldTable
go
SELECT INTO is best used for the temporary quick and dirty operation, not
for when you want something to stick around.
Russell Fields
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:##97VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>|||Your welcomes. ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23pCzI8iWEHA.2852@.TK2MSFTNGP12.phx.gbl...
That is an awesome idea. Thanks You !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:et7Q7HiWEHA.3800@.TK2MSFTNGP11.phx.gbl...
> Try:
> create table t
> (
> ID int identity primary key
> , x char (2) null
> )
> insert t (x) values ('XX')
> insert t (x) values ('YY')
> go
> select
> ID
> , isnull (x, '--') as y
> into
> x
> from
> t
> exec sp_help x
> go
> drop table t, x
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:%23%2397VDiWEHA.1368@.TK2MSFTNGP10.phx.gbl...
> I am using a select into to create a new table and populate with data.
> Select a.col1, a.col2 into NewTable From OldTable
> Col2 is defined as NULLible but has no NULL values. The NewTable that is
> created has Col2 as NULLible but I would like it to be NOT NULL.
> Is there any way to get this Select into statement to create the column as
> NOT NULL. I know that I could always alter the table later to change the
> NULLibility but if this table has many million rows this Alter would take
> some time.
> Thanks in advance.
>