Appending Records to the Existing MS SQL EXPRESS SERVER Table.

Dear All,

I am Using MS SQL EXPRESS SERVER .I have installed all tools available to Express Edition site.

Now I have created my database on this .I have imported a table from my MS ACCESS database (Using ODBC Datasource).This table contains 10,000 records ,

Now I want to append 1 more access Table(5500 records) to the existing table having same fields.

How to do this.Can any body tell me?

Thanks and Regards

mukesh

Hi mukesh,

You can append the imported records into the existing table using SQL Server import & Export Wizard.

1.Select Your database from SQL Server Management Studio

2.Right Click on Database and go to the Task->Import Data menu Item

3. SQL Server Import & Export Wizard will be open., choose ur data source. as MicrosoftAccess, and select the MDB file

4.Press Next to Move onChoose a Destination Page, and select your Database Name from dropdown

5.Press Next to Move onSpecity Table Copy or QueryPage, and selectcopy data from one or more tables or viewradio button.

6. Press Next to Move onSelect Source Table and View , Select your Source Table , and Destination Table and then pressEdit button,Column Mappings dialog box will be open, chooseAppend rows to the destination tabelradion button option ( it will append the new records with existing records, in your case ur new 5500 records will be apended with existing 10,000 records )

7Press Next, and then Press Finish.Import Process will be started.

Thanks

Best Regards,

Muhammad AKhtar Shiekh

SQL Server Import & Export Wizard

|||

Hi mukesh,

The way you imported the first table just load the second table but with a different name i.e Table2 to the same database and then you can use the query

Lets Table_1 is having 10000

Table_2 is having 5500

----------------

Insert into Table_1

Select * from Table_2

----------------

the simplest way to do the stuff...

Satya

|||

Thanks Mr.Akhhttar

But sir in management studio I can't find "Import and Export option" there r these option,"detach","shrink","backup","restore"& generate scripts.

thanks and regards

mukesh

|||

Thanks a lot,Mr.Satya

append data to an excel file,

I have a DTS that exports a view to an excel file, is it possible to append
data to an excel file, it seems if it sees that the sheet is available it
creates a new sheet,
I have a logo in my excel file and I want to append the rows below it.JIM
create procedure file_write_text(@.file_name varchar(1000),
@.text varchar(8000),
@.append bit = 0)
as
/*
exec file_write_text 'c:\TEST.XLS','hello'
exec file_write_text 'c:\TEST.XLS',' world',1
*/
declare @.fso int
declare @.ts int
declare @.rv int
exec @.rv = sp_oacreate "scripting.filesystemobject", @.fso OUTPUT, 1
if @.rv <> 0 goto eh
if @.append = 1
begin
--open the text stream for append, will fail if the file doesn't exist
exec @.rv = sp_oamethod @.fso,"opentextfile", @.ts OUTPUT, @.file_name, 8
if @.rv <> 0 goto eh
end
else
begin
--create a new text file, overwriing if necessary
exec @.rv = sp_oamethod @.fso,"createtextfile", @.ts OUTPUT, @.file_name, -1
if @.rv <> 0 goto eh
end
exec @.rv = sp_oamethod @.ts,"write",null ,@.text
if @.rv <> 0 goto eh
exec @.rv = sp_oamethod @.ts,"close"
if @.rv <> 0 goto eh
exec sp_oadestroy @.ts
exec sp_oadestroy @.fso
return 0
eh:
declare @.es varchar(512)
declare @.ed varchar(512)
exec sp_oageterrorinfo null, @.es OUTPUT, @.ed OUTPUT
raiserror(@.ed,16,1)
exec sp_oadestroy @.ts
exec sp_oadestroy @.fso
return 1
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:AFE084C6-4A5F-433D-B693-A3CCEAC70596@.microsoft.com...
>I have a DTS that exports a view to an excel file, is it possible to append
> data to an excel file, it seems if it sees that the sheet is available it
> creates a new sheet,
> I have a logo in my excel file and I want to append the rows below it.
>|||Thanks for the reply Uri, is this for text file or Excel file (xls, not csv)
?
"Uri Dimant" wrote:

> JIM
> create procedure file_write_text(@.file_name varchar(1000),
> @.text varchar(8000),
> @.append bit = 0)
> as
> /*
> exec file_write_text 'c:\TEST.XLS','hello'
> exec file_write_text 'c:\TEST.XLS',' world',1
> */
> declare @.fso int
> declare @.ts int
> declare @.rv int
> exec @.rv = sp_oacreate "scripting.filesystemobject", @.fso OUTPUT, 1
> if @.rv <> 0 goto eh
> if @.append = 1
> begin
> --open the text stream for append, will fail if the file doesn't exist
> exec @.rv = sp_oamethod @.fso,"opentextfile", @.ts OUTPUT, @.file_name, 8
> if @.rv <> 0 goto eh
> end
> else
> begin
> --create a new text file, overwriing if necessary
> exec @.rv = sp_oamethod @.fso,"createtextfile", @.ts OUTPUT, @.file_name, -1
> if @.rv <> 0 goto eh
> end
> exec @.rv = sp_oamethod @.ts,"write",null ,@.text
> if @.rv <> 0 goto eh
> exec @.rv = sp_oamethod @.ts,"close"
> if @.rv <> 0 goto eh
> exec sp_oadestroy @.ts
> exec sp_oadestroy @.fso
> return 0
> eh:
> declare @.es varchar(512)
> declare @.ed varchar(512)
> exec sp_oageterrorinfo null, @.es OUTPUT, @.ed OUTPUT
> raiserror(@.ed,16,1)
> exec sp_oadestroy @.ts
> exec sp_oadestroy @.fso
> return 1
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:AFE084C6-4A5F-433D-B693-A3CCEAC70596@.microsoft.com...
>
>|||It should be doable for both types
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D751008B-0C20-4F1E-A07C-FA6108F2CD08@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply Uri, is this for text file or Excel file (xls, not
> csv)?
>
> "Uri Dimant" wrote:
>

append data to an excel file,

I have a DTS that exports a view to an excel file, is it possible to append
data to an excel file, it seems if it sees that the sheet is available it
creates a new sheet,
I have a logo in my excel file and I want to append the rows below it.JIM
create procedure file_write_text(@.file_name varchar(1000),
@.text varchar(8000),
@.append bit = 0)
as
/*
exec file_write_text 'c:\TEST.XLS','hello'
exec file_write_text 'c:\TEST.XLS',' world',1
*/
declare @.fso int
declare @.ts int
declare @.rv int
exec @.rv = sp_oacreate "scripting.filesystemobject", @.fso OUTPUT, 1
if @.rv <> 0 goto eh
if @.append = 1
begin
--open the text stream for append, will fail if the file doesn't exist
exec @.rv = sp_oamethod @.fso,"opentextfile", @.ts OUTPUT, @.file_name, 8
if @.rv <> 0 goto eh
end
else
begin
--create a new text file, overwriing if necessary
exec @.rv = sp_oamethod @.fso,"createtextfile", @.ts OUTPUT, @.file_name, -1
if @.rv <> 0 goto eh
end
exec @.rv = sp_oamethod @.ts,"write",null ,@.text
if @.rv <> 0 goto eh
exec @.rv = sp_oamethod @.ts,"close"
if @.rv <> 0 goto eh
exec sp_oadestroy @.ts
exec sp_oadestroy @.fso
return 0
eh:
declare @.es varchar(512)
declare @.ed varchar(512)
exec sp_oageterrorinfo null, @.es OUTPUT, @.ed OUTPUT
raiserror(@.ed,16,1)
exec sp_oadestroy @.ts
exec sp_oadestroy @.fso
return 1
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:AFE084C6-4A5F-433D-B693-A3CCEAC70596@.microsoft.com...
>I have a DTS that exports a view to an excel file, is it possible to append
> data to an excel file, it seems if it sees that the sheet is available it
> creates a new sheet,
> I have a logo in my excel file and I want to append the rows below it.
>|||Thanks for the reply Uri, is this for text file or Excel file (xls, not csv)?
"Uri Dimant" wrote:
> JIM
> create procedure file_write_text(@.file_name varchar(1000),
> @.text varchar(8000),
> @.append bit = 0)
> as
> /*
> exec file_write_text 'c:\TEST.XLS','hello'
> exec file_write_text 'c:\TEST.XLS',' world',1
> */
> declare @.fso int
> declare @.ts int
> declare @.rv int
> exec @.rv = sp_oacreate "scripting.filesystemobject", @.fso OUTPUT, 1
> if @.rv <> 0 goto eh
> if @.append = 1
> begin
> --open the text stream for append, will fail if the file doesn't exist
> exec @.rv = sp_oamethod @.fso,"opentextfile", @.ts OUTPUT, @.file_name, 8
> if @.rv <> 0 goto eh
> end
> else
> begin
> --create a new text file, overwriing if necessary
> exec @.rv = sp_oamethod @.fso,"createtextfile", @.ts OUTPUT, @.file_name, -1
> if @.rv <> 0 goto eh
> end
> exec @.rv = sp_oamethod @.ts,"write",null ,@.text
> if @.rv <> 0 goto eh
> exec @.rv = sp_oamethod @.ts,"close"
> if @.rv <> 0 goto eh
> exec sp_oadestroy @.ts
> exec sp_oadestroy @.fso
> return 0
> eh:
> declare @.es varchar(512)
> declare @.ed varchar(512)
> exec sp_oageterrorinfo null, @.es OUTPUT, @.ed OUTPUT
> raiserror(@.ed,16,1)
> exec sp_oadestroy @.ts
> exec sp_oadestroy @.fso
> return 1
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:AFE084C6-4A5F-433D-B693-A3CCEAC70596@.microsoft.com...
> >I have a DTS that exports a view to an excel file, is it possible to append
> > data to an excel file, it seems if it sees that the sheet is available it
> > creates a new sheet,
> > I have a logo in my excel file and I want to append the rows below it.
> >
>
>|||It should be doable for both types
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D751008B-0C20-4F1E-A07C-FA6108F2CD08@.microsoft.com...
> Thanks for the reply Uri, is this for text file or Excel file (xls, not
> csv)?
>
> "Uri Dimant" wrote:
>> JIM
>> create procedure file_write_text(@.file_name varchar(1000),
>> @.text varchar(8000),
>> @.append bit = 0)
>> as
>> /*
>> exec file_write_text 'c:\TEST.XLS','hello'
>> exec file_write_text 'c:\TEST.XLS',' world',1
>> */
>> declare @.fso int
>> declare @.ts int
>> declare @.rv int
>> exec @.rv = sp_oacreate "scripting.filesystemobject", @.fso OUTPUT, 1
>> if @.rv <> 0 goto eh
>> if @.append = 1
>> begin
>> --open the text stream for append, will fail if the file doesn't exist
>> exec @.rv = sp_oamethod @.fso,"opentextfile", @.ts OUTPUT, @.file_name, 8
>> if @.rv <> 0 goto eh
>> end
>> else
>> begin
>> --create a new text file, overwriing if necessary
>> exec @.rv = sp_oamethod @.fso,"createtextfile", @.ts OUTPUT,
>> @.file_name, -1
>> if @.rv <> 0 goto eh
>> end
>> exec @.rv = sp_oamethod @.ts,"write",null ,@.text
>> if @.rv <> 0 goto eh
>> exec @.rv = sp_oamethod @.ts,"close"
>> if @.rv <> 0 goto eh
>> exec sp_oadestroy @.ts
>> exec sp_oadestroy @.fso
>> return 0
>> eh:
>> declare @.es varchar(512)
>> declare @.ed varchar(512)
>> exec sp_oageterrorinfo null, @.es OUTPUT, @.ed OUTPUT
>> raiserror(@.ed,16,1)
>> exec sp_oadestroy @.ts
>> exec sp_oadestroy @.fso
>> return 1
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:AFE084C6-4A5F-433D-B693-A3CCEAC70596@.microsoft.com...
>> >I have a DTS that exports a view to an excel file, is it possible to
>> >append
>> > data to an excel file, it seems if it sees that the sheet is available
>> > it
>> > creates a new sheet,
>> > I have a logo in my excel file and I want to append the rows below it.
>> >
>>

append data to an excel file

I have a DTS that exports a view to an excel file, is it possible to append data to an excel file, it seems if it sees that the sheet is available it creates a new sheet,

I have a logo in my excel file and I want to append the rows below it.

If you're looking to populate a Worksheet with data at a specific location I think you will probably need to use Excel automation instead of a batch or data access method. See the following for more info:

How to transfer data to an Excel workbook by using Visual Basic .NET

Anyway to track SQL Server Agent error messages

We are in on SQL2000 and our SQL Server Agent started to receive errors for
Mail Profile not available. It wasn't until we looked at the agent error log
we saw the problem.
Is there any way to report the SQL Server Agent Warnings/Errors via email or
alert as a pro-active stance?
JTS
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1There is product called ProIT 4 (I work for the company and wrote the SQL
monitoring function in it) that will do it. ProIT is a server & network
monitoring software package. In additionally to writting it, we use it
internally and have customers using it to monitor both the sql error log and
the system eventlogs for SQL Agent job failures and SQL errors.
"jsheldon via SQLMonster.com" wrote:
> We are in on SQL2000 and our SQL Server Agent started to receive errors for
> Mail Profile not available. It wasn't until we looked at the agent error log
> we saw the problem.
> Is there any way to report the SQL Server Agent Warnings/Errors via email or
> alert as a pro-active stance?
> JTS
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||Netmon what is the web address?
Thanks
Netmon wrote:
>There is product called ProIT 4 (I work for the company and wrote the SQL
>monitoring function in it) that will do it. ProIT is a server & network
>monitoring software package. In additionally to writting it, we use it
>internally and have customers using it to monitor both the sql error log and
>the system eventlogs for SQL Agent job failures and SQL errors.
>> We are in on SQL2000 and our SQL Server Agent started to receive errors for
>> Mail Profile not available. It wasn't until we looked at the agent error log
>[quoted text clipped - 4 lines]
>> JTS
--
Message posted via http://www.sqlmonster.com

Anyway to track SQL Server Agent error messages

We are in on SQL2000 and our SQL Server Agent started to receive errors for
Mail Profile not available. It wasn't until we looked at the agent error log
we saw the problem.
Is there any way to report the SQL Server Agent Warnings/Errors via email or
alert as a pro-active stance?
JTS
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
There is product called ProIT 4 (I work for the company and wrote the SQL
monitoring function in it) that will do it. ProIT is a server & network
monitoring software package. In additionally to writting it, we use it
internally and have customers using it to monitor both the sql error log and
the system eventlogs for SQL Agent job failures and SQL errors.
"jsheldon via droptable.com" wrote:

> We are in on SQL2000 and our SQL Server Agent started to receive errors for
> Mail Profile not available. It wasn't until we looked at the agent error log
> we saw the problem.
> Is there any way to report the SQL Server Agent Warnings/Errors via email or
> alert as a pro-active stance?
> JTS
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
|||Netmon what is the web address?
Thanks
Netmon wrote:[vbcol=seagreen]
>There is product called ProIT 4 (I work for the company and wrote the SQL
>monitoring function in it) that will do it. ProIT is a server & network
>monitoring software package. In additionally to writting it, we use it
>internally and have customers using it to monitor both the sql error log and
>the system eventlogs for SQL Agent job failures and SQL errors.
>[quoted text clipped - 4 lines]
Message posted via http://www.droptable.com

Anyway to track SQL Server Agent error messages

We are in on SQL2000 and our SQL Server Agent started to receive errors for
Mail Profile not available. It wasn't until we looked at the agent error lo
g
we saw the problem.
Is there any way to report the SQL Server Agent Warnings/Errors via email or
alert as a pro-active stance?
JTS
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1There is product called ProIT 4 (I work for the company and wrote the SQL
monitoring function in it) that will do it. ProIT is a server & network
monitoring software package. In additionally to writting it, we use it
internally and have customers using it to monitor both the sql error log and
the system eventlogs for SQL Agent job failures and SQL errors.
"jsheldon via droptable.com" wrote:

> We are in on SQL2000 and our SQL Server Agent started to receive errors fo
r
> Mail Profile not available. It wasn't until we looked at the agent error
log
> we saw the problem.
> Is there any way to report the SQL Server Agent Warnings/Errors via email
or
> alert as a pro-active stance?
> JTS
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||Netmon what is the web address?
Thanks
Netmon wrote:[vbcol=seagreen]
>There is product called ProIT 4 (I work for the company and wrote the SQL
>monitoring function in it) that will do it. ProIT is a server & network
>monitoring software package. In additionally to writting it, we use it
>internally and have customers using it to monitor both the sql error log an
d
>the system eventlogs for SQL Agent job failures and SQL errors.
>
>[quoted text clipped - 4 lines]
Message posted via http://www.droptable.com

Anyway to emulate LIMIT Start,Count?

Does anyone know of a way to emulate the LIMIT clause that is available in Oracle and MySQL?

I could greatly improve my performance on my web application if I could figure out how to do it. I am using TOP right now to just pull back the records needed, but as with any page with many results, once you get 10 pages in you're pulling way too many records accross the wire.

The LIMIT clause works great in the other DBMS's, but MS SQL 2K does not have it. :(set rowcount 100|||That doesn't deal with the offset right??|||It just limits the number of rows returned to the client to 100 (or whatever number you specify)

Don't forget to set it to 0 to restore the default functionality - RETURN ALL ROWS.|||I already get that with TOP so there is no need for this.|||if you want LIMIT 30,20 (start at offset 30, i.e. 31st row, and return 20 rows) like this:

select *
from (
select top 20
foo
, bar
from (
select top 50
foo
, bar
from yourtable
order
by bar desc
)
order
by bar asc
)
order
by bar desc|||http://www.dbforums.com/t994143.html|||I like the multiple subselect idea but don't seem to be able to get it to work. The sql errors out when trying to do an order by on a subselect.

For example - this errors out:

select top 20 AccountID,[Name] from (
select top 50 AccountID,[Name]
from CRDACCOUNT
order by AccountID desc
) order by AccountID

Give an "Incorrect syntax near the keyword 'order'." on the last line.

Ideas?

PS: Thanks for the great replies so far everyone!!|||I'm 99 5/8% certain that you are just missing an alias, something like:SELECT TOP 20 AccountID, [Name]
FROM (SELECT TOP 50 AccountID, [Name]
FROM CRDACCOUNT
ORDER BY AccountID DESC) AS zz
ORDER BY AccountID-PatP|||wow, I just posted a question asking nearly the identical thing. So far it doesn't look good. My options are:

- Using an ORDER BY on column and maintain a bookmark position. This works but I can't get decent performance out of it.
- Paging techniques. Having trouble getting these to work right as well.

Let me know if you find an acceptable solution.|||Yep - it was the alias. Here is the first query formatted with the aliases:

select *
from (
select top 20
foo, bar
from (
select top 50
foo, bar
from yourtable
order
by bar desc
) as tbl1
order
by bar asc
) as tbl2
order
by bar desc

I guess the trick now is to figure out how to have multi fields in the ORDER BY clause.

Thanks for the help that has been given!

Anything new about RS SP2?

Anybody knows when RS SP2 will be available?
Thanks.
VictorQuestion of the month.
http://www.msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=50f4abcf-371b-4675-ba27-4ec9223ed375
Adrian M.
MCP
"Victor" <Victor@.discussions.microsoft.com> wrote in message
news:8DFE38B6-F1F0-4C45-B13F-4142B7038C58@.microsoft.com...
> Anybody knows when RS SP2 will be available?
> Thanks.
> Victor
>|||Thank you.
Looks like we are waiting it as a bride waits a wedding.
Victor
"Adrian M." wrote:
> Question of the month.
> http://www.msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=50f4abcf-371b-4675-ba27-4ec9223ed375
>
> --
> Adrian M.
> MCP
>
> "Victor" <Victor@.discussions.microsoft.com> wrote in message
> news:8DFE38B6-F1F0-4C45-B13F-4142B7038C58@.microsoft.com...
> > Anybody knows when RS SP2 will be available?
> >
> > Thanks.
> > Victor
> >
>
>