Hello,
Is it possible to feed a SQL server with data from an Excel sheet? If so -
how?
Thanks in advance,
Christian DavidssonHi,
DTS (Data transformation service) will be the easiest option to load data
from Excel file to SQL server table.
Thanks
Hari
MCDBA
"Christian Davidsson" <christian [dot] davidsson [at] skanemejerier
[dot]
se> wrote in message news:OyGFFA4$DHA.3048@.tk2msftngp13.phx.gbl...
> Hello,
> Is it possible to feed a SQL server with data from an Excel sheet? If so -
> how?
>
> Thanks in advance,
> Christian Davidsson
>|||Hari's right, DTS would work great. Another option would be to create a
linked server.
306397 HOWTO: Use Excel with SQL Server Linked Servers and Distributed
Queries
http://support.microsoft.com/?id=306397
321686 HOW TO: Import Data into SQL Server from Excel
http://support.microsoft.com/?id=321686
326839 Support WebCast: Microsoft SQL Server: How to Configure, Manage, Use,
http://support.microsoft.com/?id=326839
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label advance. Show all posts
Showing posts with label advance. Show all posts
Monday, March 26, 2012
Pushing a publication for just one subscriber?
This may be a basic question, if so, apologies in advance.
We're having to set up a new subscriber for several publications. From
what we remember, the last time we added a subscriber, running the
snapshot agent caused every subscriber (not just the new one) to get a
brand new copy of the table. This would take a considerable amount of
time, as some of the subscribers are on slow connections. Is there a
trick to just push the subscription to the one new subscriber? Many
thanks.
Im guessing what happened is someone clicked the "reinitialize all
subscriptions option" which would cause that behavior. Adding 1 subscription
will not do that.
<bourgon@.gmail.com> wrote in message
news:1119539711.905957.139680@.o13g2000cwo.googlegr oups.com...
> This may be a basic question, if so, apologies in advance.
> We're having to set up a new subscriber for several publications. From
> what we remember, the last time we added a subscriber, running the
> snapshot agent caused every subscriber (not just the new one) to get a
> brand new copy of the table. This would take a considerable amount of
> time, as some of the subscribers are on slow connections. Is there a
> trick to just push the subscription to the one new subscriber? Many
> thanks.
>
We're having to set up a new subscriber for several publications. From
what we remember, the last time we added a subscriber, running the
snapshot agent caused every subscriber (not just the new one) to get a
brand new copy of the table. This would take a considerable amount of
time, as some of the subscribers are on slow connections. Is there a
trick to just push the subscription to the one new subscriber? Many
thanks.
Im guessing what happened is someone clicked the "reinitialize all
subscriptions option" which would cause that behavior. Adding 1 subscription
will not do that.
<bourgon@.gmail.com> wrote in message
news:1119539711.905957.139680@.o13g2000cwo.googlegr oups.com...
> This may be a basic question, if so, apologies in advance.
> We're having to set up a new subscriber for several publications. From
> what we remember, the last time we added a subscriber, running the
> snapshot agent caused every subscriber (not just the new one) to get a
> brand new copy of the table. This would take a considerable amount of
> time, as some of the subscribers are on slow connections. Is there a
> trick to just push the subscription to the one new subscriber? Many
> thanks.
>
Tuesday, March 20, 2012
Pulling Random Distinct Percentage
Having trouble coming up with a fast and efficient sproc for this.
Thanks in advance for any help!
tblRecords
record_id (int) [key]
location_id (int)
owned_by_id (int) [default = 0]
is_complete (int) [default = 0]
date_entered (datetime) [default = getDate()]
date_completed (datetime)
I'm trying to populate a Datagrid with ((Total - total completed -
total owned) * 7%) of random non-complete records and all owned records
within a given date range for each site sorted by date_entered.
I figure I'll need to create a temp table holding all the totals
(total, total completed & total owned) for each site (using distinct
and count). And then for each row (or site) loop through the math
shown above and then select random top X where is_complete = 0 AND
owned_by_id = 0 AND date_entered BETWEEN startDate AND stopDate.
Then SELECT WHERE is_complete = 0 AND owned_by_id <> 0 AND date_enteed
BETWEEN startDate AND stopDate. Then somehow merge the result... then
merge again with the looping table. Once complete sort table by
date_entered and return. I'm not sure if that is the best/easiest way
to do this... so I'm wondering if someone else knows a better way, and
can provide an example, as my SQL skills are not up to par for this.
Thanks again!
~GregHi
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data. Also posting the required resultset from your data would be
helpful.
If your is_completed column can only have values of 0 or 1 then you could do
something like:
SELECT location_id, owned_by_id, SUM(is_complete) as Completed,
SUM(1-is_complete)AS incomplete, COUNT(*) AS total
FROM tblRecords
GROUP BY location_id, owned_by_id
to get the number completed. You will need to add the date constriction in a
WHERE CLAUSE. If you can have other values you can do something like:
SELECT location_id, owned_by_id, SUM(CASE WHEN is_complete = 1 THEN 1 ELSE 0
END) as Completed, SUM(CASE WHEN is_complete = 0 THEN 1 ELSE 0 END)AS
incomplete, COUNT(*) AS total
FROM tblRecords
GROUP BY location_id, owned_by_id
It is not clear if your owned_by_id is actually an is_owned binary value!.
You could use this query in a view or derived table instead of using a
temporary table.
HTH
John
"Bac2Day1" <Bac2Day1@.gmail.com> wrote in message
news:1136405669.374006.39790@.g44g2000cwa.googlegroups.com...
> Having trouble coming up with a fast and efficient sproc for this.
> Thanks in advance for any help!
> tblRecords
> record_id (int) [key]
> location_id (int)
> owned_by_id (int) [default = 0]
> is_complete (int) [default = 0]
> date_entered (datetime) [default = getDate()]
> date_completed (datetime)
> I'm trying to populate a Datagrid with ((Total - total completed -
> total owned) * 7%) of random non-complete records and all owned records
> within a given date range for each site sorted by date_entered.
> I figure I'll need to create a temp table holding all the totals
> (total, total completed & total owned) for each site (using distinct
> and count). And then for each row (or site) loop through the math
> shown above and then select random top X where is_complete = 0 AND
> owned_by_id = 0 AND date_entered BETWEEN startDate AND stopDate.
> Then SELECT WHERE is_complete = 0 AND owned_by_id <> 0 AND date_enteed
> BETWEEN startDate AND stopDate. Then somehow merge the result... then
> merge again with the looping table. Once complete sort table by
> date_entered and return. I'm not sure if that is the best/easiest way
> to do this... so I'm wondering if someone else knows a better way, and
> can provide an example, as my SQL skills are not up to par for this.
> Thanks again!
> ~Greg
>
Thanks in advance for any help!
tblRecords
record_id (int) [key]
location_id (int)
owned_by_id (int) [default = 0]
is_complete (int) [default = 0]
date_entered (datetime) [default = getDate()]
date_completed (datetime)
I'm trying to populate a Datagrid with ((Total - total completed -
total owned) * 7%) of random non-complete records and all owned records
within a given date range for each site sorted by date_entered.
I figure I'll need to create a temp table holding all the totals
(total, total completed & total owned) for each site (using distinct
and count). And then for each row (or site) loop through the math
shown above and then select random top X where is_complete = 0 AND
owned_by_id = 0 AND date_entered BETWEEN startDate AND stopDate.
Then SELECT WHERE is_complete = 0 AND owned_by_id <> 0 AND date_enteed
BETWEEN startDate AND stopDate. Then somehow merge the result... then
merge again with the looping table. Once complete sort table by
date_entered and return. I'm not sure if that is the best/easiest way
to do this... so I'm wondering if someone else knows a better way, and
can provide an example, as my SQL skills are not up to par for this.
Thanks again!
~GregHi
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data. Also posting the required resultset from your data would be
helpful.
If your is_completed column can only have values of 0 or 1 then you could do
something like:
SELECT location_id, owned_by_id, SUM(is_complete) as Completed,
SUM(1-is_complete)AS incomplete, COUNT(*) AS total
FROM tblRecords
GROUP BY location_id, owned_by_id
to get the number completed. You will need to add the date constriction in a
WHERE CLAUSE. If you can have other values you can do something like:
SELECT location_id, owned_by_id, SUM(CASE WHEN is_complete = 1 THEN 1 ELSE 0
END) as Completed, SUM(CASE WHEN is_complete = 0 THEN 1 ELSE 0 END)AS
incomplete, COUNT(*) AS total
FROM tblRecords
GROUP BY location_id, owned_by_id
It is not clear if your owned_by_id is actually an is_owned binary value!.
You could use this query in a view or derived table instead of using a
temporary table.
HTH
John
"Bac2Day1" <Bac2Day1@.gmail.com> wrote in message
news:1136405669.374006.39790@.g44g2000cwa.googlegroups.com...
> Having trouble coming up with a fast and efficient sproc for this.
> Thanks in advance for any help!
> tblRecords
> record_id (int) [key]
> location_id (int)
> owned_by_id (int) [default = 0]
> is_complete (int) [default = 0]
> date_entered (datetime) [default = getDate()]
> date_completed (datetime)
> I'm trying to populate a Datagrid with ((Total - total completed -
> total owned) * 7%) of random non-complete records and all owned records
> within a given date range for each site sorted by date_entered.
> I figure I'll need to create a temp table holding all the totals
> (total, total completed & total owned) for each site (using distinct
> and count). And then for each row (or site) loop through the math
> shown above and then select random top X where is_complete = 0 AND
> owned_by_id = 0 AND date_entered BETWEEN startDate AND stopDate.
> Then SELECT WHERE is_complete = 0 AND owned_by_id <> 0 AND date_enteed
> BETWEEN startDate AND stopDate. Then somehow merge the result... then
> merge again with the looping table. Once complete sort table by
> date_entered and return. I'm not sure if that is the best/easiest way
> to do this... so I'm wondering if someone else knows a better way, and
> can provide an example, as my SQL skills are not up to par for this.
> Thanks again!
> ~Greg
>
Subscribe to:
Posts (Atom)