Can I generate a SQL CREATE script for a table that exists in the db automatical

I'm trying to figure out what the easiest/best way to transfer just one table from one sql server to another. Any help would be appreciated. Thanks!you can use the query analyzer, select database --> user tables --> select correct table --> right click --> script object to new window as -->> create. The query analyzer will generate a script that will generate your table.|||Thanks for the quick reply!
However, I can't seem to find the "user tables-->select correct table" option in the query analyzer?
Thanks again!|||At the left side is the object browser. If you cannot see, push the F8 button. You see at the top your server and below the databases in that server. From that point on it's like the Windows Explorer. When you see the user tables you can select one and right click on it for the scripting.|||Is this a SQL 2000 thing? I'm on SQL 7.0|||don't know, never used sql 7. Something else you can try: in the enterprise manager, select the table you want to script. Right click on it, select all tasks and then generate script. A popup will appear and if you want you can set some preferences, but just pushing OK is sufficient. Hopefully this is in sql 7|||You can access this through enterprise manager by right-clicking on the table and selecting All Tasks, Generate SQL Script.

Note that this does not copy the actual data. To do that you will need to use DTS.

blindman

Can I do this in one SQL statement

Let's say that I have a database that has customer name, invoice date, and
amount in it. If an entry exists in this database, then the company owes us
money. I am trying to put together a report that, for each company, details
how much is owed that is 30 days out from a given date, how much is owed
that is 60 days out, 90 days out, and greater than that.
The first statement would look a lot like this:
SELECT name, sold, SUM(owed) AS AmountOwed
FROM InvOwed
WHERE (CONVERT(datetime, invdate) >= CONVERT(datetime, '4-mar-2004')) AND
(DATEADD(d, - 30, '4-mar-2004') <= CONVERT(datetime, invdate))
GROUP BY name, sold
ORDER BY name
The second statement looks like this:
SELECT name, sold, SUM(owed) AS AmountOwed
FROM InvOwed
WHERE (CONVERT(datetime, invdate) <= DATEADD(d, - 31, '4-mar-2004')) AND
(DATEADD(d, - 60, '4-mar-2004') <= CONVERT(datetime, invdate))
GROUP BY name, sold
ORDER BY name
And so on. But, then I have to cut-and-paste this into Excel, and massage
it a bit to get
Customer Amount30DaysDue Amount60DaysDue Amount90DaysDue
Is there any way to get all these in one SQL statement so I don't have to
massage the data?
Thank you.
JoshuaTry,
SELECT
[name],
sold,
SUM(case when invdate >= dateadd(day, -30, convert(char(8), getdate(),
112)) then owed end) AS Amount30DaysDue,
SUM(case when invdate >= dateadd(day, -60, convert(char(8), getdate(),
112)) and invdate < dateadd(day, -30, convert(char(8), getdate(), 112)) then
owed end) AS Amount60DaysDue,
SUM(case when invdate >= dateadd(day, -90, convert(char(8), getdate(),
112)) and invdate < dateadd(day, -60, convert(char(8), getdate(), 112)) then
owed end) AS Amount90DaysDue,
FROM
InvOwed
GROUP BY name, sold
ORDER BY name
go
AMB
"Joshua Campbell" wrote:

> Let's say that I have a database that has customer name, invoice date, and
> amount in it. If an entry exists in this database, then the company owes
us
> money. I am trying to put together a report that, for each company, detai
ls
> how much is owed that is 30 days out from a given date, how much is owed
> that is 60 days out, 90 days out, and greater than that.
> The first statement would look a lot like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) >= CONVERT(datetime, '4-mar-2004')) AND
> (DATEADD(d, - 30, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> The second statement looks like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) <= DATEADD(d, - 31, '4-mar-2004')) AND
> (DATEADD(d, - 60, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> And so on. But, then I have to cut-and-paste this into Excel, and massage
> it a bit to get
> Customer Amount30DaysDue Amount60DaysDue Amount90DaysDue
>
> Is there any way to get all these in one SQL statement so I don't have to
> massage the data?
> Thank you.
> Joshua
>
>
>|||Try:
select name,sold,sum(case when datediff(d,invdate,'20040304') between 1 and
30 then owed else 0 end) [30day],
sum(case when datediff(d,invdate,'20040304') between 31 and 60 then owed
else 0 end) [60day],
sum(case when datediff(d,invdate,'20040304') between 61 and 90 then owed
else 0 end) [90day]
from InvOwed
group by name,sold
-oj
"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:%231oNyuUOFHA.3144@.tk2msftngp13.phx.gbl...
> Let's say that I have a database that has customer name, invoice date, and
> amount in it. If an entry exists in this database, then the company owes
> us
> money. I am trying to put together a report that, for each company,
> details
> how much is owed that is 30 days out from a given date, how much is owed
> that is 60 days out, 90 days out, and greater than that.
> The first statement would look a lot like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) >= CONVERT(datetime, '4-mar-2004')) AND
> (DATEADD(d, - 30, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> The second statement looks like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) <= DATEADD(d, - 31, '4-mar-2004')) AND
> (DATEADD(d, - 60, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> And so on. But, then I have to cut-and-paste this into Excel, and massage
> it a bit to get
> Customer Amount30DaysDue Amount60DaysDue Amount90DaysDue
>
> Is there any way to get all these in one SQL statement so I don't have to
> massage the data?
> Thank you.
> Joshua
>
>|||I didn't know you could use case like that. Excellent. Thank you very
much!
"oj" <nospam_ojngo@.home.com> wrote in message
news:eoXDR5UOFHA.2468@.tk2msftngp13.phx.gbl...
> Try:
> select name,sold,sum(case when datediff(d,invdate,'20040304') between 1
and
> 30 then owed else 0 end) [30day],
> sum(case when datediff(d,invdate,'20040304') between 31 and 60 then owed
> else 0 end) [60day],
> sum(case when datediff(d,invdate,'20040304') between 61 and 90 then owed
> else 0 end) [90day]
> from InvOwed
> group by name,sold
> --
> -oj
>
> "Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
> news:%231oNyuUOFHA.3144@.tk2msftngp13.phx.gbl...
and
owes
AND
massage
to
>

Can a new field be added to an existing table.

Can a new field be added to a table that already exists.

Thanks in advance."kjc" <ksitron@.elp.rr.com> wrote in message
news:Rqi2d.2380$3b.1942@.fe2.texas.rr.com...
> Can a new field be added to a table that already exists.
> Thanks in advance.

Yes - see ALTER TABLE and "Adding and Deleting Columns" in Books Online:

alter table dbo.MyTable add MyColumn int null

Simon|||Thanks a bunch

Simon Hayes wrote:
> "kjc" <ksitron@.elp.rr.com> wrote in message
> news:Rqi2d.2380$3b.1942@.fe2.texas.rr.com...
>>Can a new field be added to a table that already exists.
>>
>>Thanks in advance.
>>
>
> Yes - see ALTER TABLE and "Adding and Deleting Columns" in Books Online:
> alter table dbo.MyTable add MyColumn int null
> Simon

Calling update and insert in one stored procedure

Hello,

Basically i want to have a stored procedute which will do an insert statement if the record is not in the table and update if it exists.

The Id is not autonumber, so if the record doesn't exists the sp should return the last id+1 to use it for the new record.

Is there any example i can see, or somebody can help me with that?

Thanks a lot.

IF EXISTS (SELECT * FROM table WHERE <condition>)

BEGIN

--the record exists. So just do the UPDATE

END

ELSE

BEGIN

--The record does not exist. So do an INSERT

END

|||

This is very helpful, thanks!

If it exists, then how can i get the max value of the id column?

Thanks again.

|||SELECT MAX(column) FROM table WHERE <Condition>|||

Thank you very much for your help!