create tables and insert data in sql server mobile on dekstop

Hello (sorry my bad english, im brazilian)

I was using Visual Studio 2003 and SQL Server CE 2.0 for C# mobile applications. The .sdf database were created in the emulator or in the mobile device itself using Query Analizer.

The application developed need some initial data to run, and this data is obtained executing one service that reads a postgree database, and insert the data in the SQL CE database of the mobile device. But, given the size of the database (maybe 10.000 rows), it tooks too much time (sometimes 6 hours).

Now we are migrating to Visual Studio 2005 and SQL Server 2005 Mobile Edition.

I want to know if its possible to create the .sdf database and load the data into this database on the desktop. Maybe through the execution of a .sql script, or through a service executed on the desktop.

After this, its just upload de .sdf file to the mobile device.

Thanks

Robson

Yes, you can create and populate your SQL Mobile database on the desktop as long as that desktop or server meets one of these criteria:

1. it contains a licensed copy of Visual Studio 2005

2. it contains a licesed copy of SQL Server 2005

3. it runs Windows XP Tablet PC edition

The code to do so is covered in the SQL Mobile Books Online.

There are other approaches as well, including third party tools like those at www.primeworks.pt, using SQL Server 2005 Integration Services, or creating and populating the database within SQL Server 2005 management studio.

-Darren

|||

Daren,

thanks for the help... I have found the way to create a sql server mobile 2005 database and insert data on the desktop using c# (running on desktop off course) at these forum topics:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=213973&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16369&SiteID=1

I′ve used my licensed copy of Visual Studio 2005 to do it. Now I′ll test the solution to make a benchmark... I hope that now i will be able to create the database for my application faster....

thanks again...

|||

Please let us know how it works out for you.

Darren

|||

I have created the following table ('cidade' means city in portuguese):

CREATE TABLE cidade ( idcidade numeric(18,0) NOT NULL, codigo integer NOT NULL, descricao nvarchar(80) NOT NULL, ddd nvarchar(3), naturalidade nvarchar(80), idunidadefederativa numeric(18,0) NOT NULL )

The program (written in c# with visual studio 2005 and sql server 2005 mobile edition) insert 5565 rows in this table. It reads a sql insert line from a text file and execute the sql, eg:

INSERT INTO cidade VALUES (1, 1, 'ALTA FLORESTA D OESTE', NULL, NULL, 21)

It tooks 6 seconds to do it (running on a HP notebook with celeron processor).So, 927.5 rows per second.

Before, when we insert data on a database located at a pocket pc, this operation took 20 - 30 minutes (using c# compact framework from visual studio 2003 and sql ce 2.0).

|||

thanks for sharing your benchmark results - that's very good news.

-Darren

sql

create table with TRIGGER

I need an Insert trigger to generate new tables when i insert a new record..
new tables will be "MasterSub_[ID]" where ID is the id of new inserted
parent record..
I also want a delete triger to remove child table if it has no data in it..
Any help plz'This is a very bad design and will likely perform poorly. Why do you need
to create tables every time you insert rows?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:uFTjXyR7FHA.1188@.TK2MSFTNGP12.phx.gbl...
>I need an Insert trigger to generate new tables when i insert a new
>record..
> new tables will be "MasterSub_[ID]" where ID is the id of new inserted
> parent record..
> I also want a delete triger to remove child table if it has no data in
> it..
> Any help plz'
>
>

create store procedure

I need your help again.
I want to create a store procedure that add new employee name to employee table. Before insert i would like to check wheter there already has this employee name. if so, don't insert.
i have two input parameters (@.fname, @.lname).
Thanks.Searching by Name(s) Bad Idea

how to do this? (don't) if you need to
You can write an insert statement using not exists statement
Insert into tbltest
(lname, fname)
select
'Jones', 'John'
where not exists
(select 'x' from
tbltest
where
lname = 'Jones'
and fname = 'John')

you can write and if statment setting a variable = count(*) of select statement using input values in the where clause and then evaluate the if

set @.var = 0
set @.var = (select count(*) from blah blah blah)

if @.var = 0 then
blah
else blah|||As i create a addemployee asp.net form. I don't want the people add more than once. That is why i need check whether the employee name is already exists. What do you think i need to check? Thanks. I am familar with the store procedure syntax. I believe the store procedure is good for this, right?
Many thanks.|||Can you help me to check the syntax. I got error that syntax error near then.
thanks.

CREATE PROCEDURE [AddNewEmployee]
(@.efname varchar(25),
@.elname varchar(25)
)

AS
decare @.var int

set @.var =0
set @.var =(SELECT count(*) FROM tblEmployee
where EmployeeFName =@.efname and EmployeeLName =@.elname)
if @.var =0 then

insert into tblEmployeeName
(EmployeeFName, EmployeeLName)
values (@.efname,@.elname)|||Thanks. i figured out. There is no then keyword. I still have a question. If the employee name exists in the table. I would like the user knows he or she can't access it. Do i need a return value or string? Thanks.|||Thanks. i figured out. There is no then keyword. I still have a question. If the employee name exists in the table. I would like the user knows he or she can't access it. Do i need a return value or string? Thanks.

Sorry, can't figure out what u want..plz provide some more details..
Joydeep|||Sure. I am creating a web application using asp.net. I have a form called addemployeename.aspx. This form allows us to add new employee. First i created a strore procedure in the previous. Then i want to call this store procedure in addemployee.aspx. In the store procedure, i tried to avoid to add more than one same emaployee name. If you add one more user. the store procedure won't insert the new record. But I would like it can return something so that i can display a message that the employee name you just added is alreday in the database. How could i do this?
Thanks.|||CREATE PROCEDURE [AddNewEmployee]
(@.efname varchar(25),
@.elname varchar(25)
)

AS
if exists (SELECT 1 FROM tblEmployee
where EmployeeFName =@.efname and EmployeeLName =@.elname
)
return (1)

insert into tblEmployeeName
(EmployeeFName, EmployeeLName)
values (@.efname,@.elname)
return (@.@.error)|||Thanks. i will try. The store procedure is amazing. I am going to try to run in asp.net. How could l received the error from web form? Please let me know if you use asp.net. Many thanks.sql

Create SQL INSERT STATEMENT WITHOUT KNOWING THE TABLE NAME... in a tirgger

I have a process I have inherited that requires me to create an insert statement, but the kicker is that I will not know the temp table's name ahead of time until it has been created. Now my code works as is, but it complains about the syntax and I was hoping there might be a better way of doing this. The code I would like to change is in orange.

Anyway, any advice would be greatly appreciated...

Here is the basic trigger:

Code Snippet

ALTER TRIGGER [dbo].[trg_DownloadDataTypes_ins] ON [dbo].[DownloadDataTypes]

INSTEAD OF INSERT

AS

SET NOCOUNT OFF

DECLARE @.TableName varchar(100),

@.FileType varchar(100),

@.FileName varchar(100),

@.CampusID varchar(15),

@.DateCreated DateTime,

@.ParentID int

IF (SELECT COUNT(FileName) FROM INSERTED) = 1

BEGIN

SELECT @.TableName = (SELECT [FileName] FROM INSERTED)

SELECT @.FileName = (SELECT [FileName] FROM INSERTED)

SELECT @.FileType = (SELECT FileType FROM INSERTED)

SELECT @.CampusID = (SELECT CampusID FROM INSERTED)

SELECT @.DateCreated = (SELECT DateDownLoaded FROM INSERTED)

IF @.TableName = 'SyncTest-Deleteme'

BEGIN

DELETE FROM DownloadDataTypes WHERE [FileName] = 'SyncTest-DeleteMe'

END

ELSE

BEGIN

/*Inserting an IF/ELSE statement here dependant on @.FileType to seperate the handling of 'CAMPUS INVENTORY' file types.

IF @.FileType = 'CAMPUS INVENTORY'

BEGIN

DECLARE @.Qry varchar(4000)

--First, insert the record into tblSyncedInventory.

INSERT INTO tblSyncedInventory([FileName], FileType, CampusID, DateCreated)

SELECT [Filename], FileType, CampusID, DateDownloaded FROM INSERTED

SET @.ParentID = @.@.IDENTITY

--Now, go out and get the child records from their temp table, insert them into tblSyncedInventoryDetails, then drop the temp table.

SET @.Qry = 'INSERT INTO tblSyncedInventoryDetails (FileID, ISBN, Copies, Accession, DateCreated, FileName) SELECT ' + str(@.ParentID) + ', dbo.[' + @.TableName + '].ISBN, dbo.[' + @.TableName + '].Copies, NULL, GETDATE(), dbo.[' + @.TableName + '].FileName FROM dbo.[' + @.TableName + ']'

EXEC (@.Qry)

--Now, drop the temp table.

SET @.Qry = 'DROP TABLE [' + @.TableName + ']'

EXEC (@.Qry)

END

ELSE

BEGIN

INSERT INTO dbo.tblSyncedInventory([FileName],[FileType],[CampusID],[DateCreated]) VALUES (@.TableName, @.FileType, @.CampusID, @.DateCreated)

INSERT INTO tblDownloadDataTypes ([FileName], FileType, CampusID, DateCreated) SELECT [Filename], FileType, CampusID, DateDownloaded FROM INSERTED

SET @.ParentID = @.@.IDENTITY

EXEC stpro_ProcessPDAFileFromTrigger @.TableName, @.FileType, @.CampusID, @.ParentID

END

What error(s) are you receiving?

|||

I think you're missing one or more END statements. Each BEGIN should have a matching one

ALso, you can grab all the variables from INSERTED In one query:

SELECT @.TableName =[FileName],

@.FileName = [FileName],

@.FileType = FileType,

@.CampusID = CampusID,

@.DateCreated = DateDownLoaded

FROM INSERTED

Not sure about how temporary these temp tables are either, but without knowing the app or the rest of the code its difficult to suggest anything constructive.

HTH

sql

Create sequential numbers in a column

I have a temp table that's populated with an insert query in as tored
procedure. The temp table has a uniqueID as the primary key.
In that table I have a column SortOrder.
What I want to do is to create a sequential number in SortOrder but
only for records matching a WHERE statement, for example:
(pardon the shorthand...)
Insert *.tblPermanent into tblTemp
If myField = 1 then
SortOrder = 1(2,3,4,5,....etc.)
else
SortOrder = 0
Thanks
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a temp table that's populated with an insert query in as tored
> procedure. The temp table has a uniqueID as the primary key.
> In that table I have a column SortOrder.
> What I want to do is to create a sequential number in SortOrder but
> only for records matching a WHERE statement, for example:
> (pardon the shorthand...)
> Insert *.tblPermanent into tblTemp
> If myField = 1 then
> SortOrder = 1(2,3,4,5,....etc.)
> else
> SortOrder = 0

There are a lot of things that I don't know about, so I have to make
a guess. First, I make the guess that the tblPermanent has a primary-
key column called id. In such case, you can do:

INSERT tblTemp(id, sortorder, ....)
SELECT id, (SELECT COUNT(*)
FROM tblPermanent b
WHERE b.id >= a.id
AND b.myfield = 1
AND a.myfield = 1), ...
FROM tblPermanent

If this does answer your question, please provide the following:

o CREATE TABLE statements for your table.
o INSERT statements with sample data.
o The desired result from the sample data.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Create script to insert 200 rows into table

I have to create a script to install a database, and one of the tables
has about 200 rows of static data... I dont want to have to manually
type in 200 insert statements, so is there a better way to do this? I
thought about maybe exporting the data into a CSV file and using some
sort of procedure to insert the records that way... Any advise?I did some research and discovered the lovely BCP utility. With this
utility i was able to export the data into a basic txt file using this
as a template:

bcp "SELECT * FROM pubs..authors" queryout authors.txt -U garth -P pw -
c

However I can find any resources on how I would go about putting the
data into the table...
If anyone could please use the above example, as the export and
provide me with a proper import that would be absolutely wonderful.|||I figured out that I can just include a bulk insert statement in my
script to do this::

BULK INSERT tmpStList FROM 'c:\TxtFile2.txt' WITH (FIELDTERMINATOR =
',')

however I can't figure out how to use a tab as the field terminator as
opposed to ,|||Well, all I did was change the bcp utility to create a CSV file
instead of the tab seperated file...

But when I try to run the bulk insert statement I get this error:

The BULK INSERT SQL construct or statement is not supported.

Error Message:

Cannont bulk load because the file "C:\scripts\attributes.txt" could
not be opened. Operating system error code 123(The filename,
directory name, or volume lable syntax is incorrect.)|||Perhaps you ran the BCP utility locally, then ran BULK INSERT on the
server?

Have you noticed yet that BCP works in both directions, IN as well as
OUT?

Roy Harvey
Beacon Falls, CT

On Wed, 15 Aug 2007 16:30:22 -0000, rhaazy <rhaazy@.gmail.comwrote:

Quote:

Originally Posted by

>Well, all I did was change the bcp utility to create a CSV file
>instead of the tab seperated file...
>
>But when I try to run the bulk insert statement I get this error:
>
>The BULK INSERT SQL construct or statement is not supported.
>
>
>Error Message:
>
>Cannont bulk load because the file "C:\scripts\attributes.txt" could
>not be opened. Operating system error code 123(The filename,
>directory name, or volume lable syntax is incorrect.)

|||My problem was that I was using the wrong instance of sql server...I
was trying to use sql server express, which doesn't support the bulk
insert. After I changed the instance I had no problem getting it to
work. Thanks for your response though.|||SQL Script Builder is a multiple platform database migration tool, it
create a database sql script (or dump file) from any ODBC data source.
Scripts are available in 5 output formats ; MySql, MS SQL, Oracle,
Pervasive and PostgreSQL. The script produced will migrate the
database (multiple tables selection) or only one table. SQL Script
Builder can be used for example to migrate your Access database to
MySql database, or MySql database to MS SQL database and vice
versa.There's no limits, all you need is the ODBC driver for the
database you wish to import from.

More Info: http://www.sqlscriptbuilder.com
Download URL: http://www.sqlscriptbuilder.com/dow...uildersetup.exe
Screenshot URL: http://www.sqlscriptbuilder.com/images/Interface.jpg
Best regards,
David

Create procedure to insert records for a project

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul

Quote:

Originally Posted by PRAW

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul

if this is one time and you have many records to insert and happens to be in a file (txt or xls), try DTS|||

Quote:

Originally Posted by ck9663

if this is one time and you have many records to insert and happens to be in a file (txt or xls), try DTS

------
No this is not one time and i have to insert this set of 11 records for each of the 40 projects i.e. 40 times.. so i need to be able to create a procedure where i can increment the value of task_taskid for each record and insert the corresponding field values.|||

Quote:

Originally Posted by PRAW

Hi

I am working on a way to Create procedure to insert a set of records for a project in a database.Now there are 11 tasks and they are to be added in each project in a table
Task_Name Task_taskid Project_ID Task_outline_no
01 - Project Management12041180 0.1
02 - Installation 12071180 2
03 - Design Pilot 12081180 3
04 - Integration & programming 12091180 4
05 - Forms & reports 12101180 5
06 - Training 12111180 6
07 - Documentaion 12121180 7
08 - Data Take on 12131180 8
09 - Go Live Spt 12141180 9
10 - Post Go Live Spt 12151180 10
11 Other Out Of Scope12161180 11

I wanna be able to add these 11 for different Project_ID like 1181, 1182,1183 and so on..
I am on SQL 2005

i could get to this only .. need help with procedure... for reducing work..

INSERT INTO [CRMCP].[dbo].[C21_TB_Task]
(task_taskid,task_proj_project_id,TASK_OUTLINE_NUM ,TASK_NAME,task_budgetdollar,task_budgethours)
VALUES(' ','1181','.1','01 - Project Management','10.00','20.00');

thanks
parul

Try below Logic to create a procedure:

1. Have a Cursor that will hold task_name, task_id, task_budjetdollar, task_budjethours
2. have a counter variable initilized to 1
3. LOOP through 1181..1221 becuase u said u need to add for 40 projectids from 1181,1182 and so on
4. With a FOR LOOP, loop through CURSOR data, and for each record (task_name), insert into table with task_name,task_id and projectid(FOR Loop value) and task_outline_num = counter variable that you have declared before in the procedure
5. COMMIT
6. Increment the counter variable by 1
7. End the Cursor LOOP
8. Reset the counter variable to 1
9. End Outer FOR LOOP
10. End Procedure

Create one stored procedure for INSERT/UPDATE/SELECT

Hi!
Instead of creating one stored procedure for insert and another one for
update, i did one with both.
Should I create one stored procedure for each? Should i create one stored
procedure for Insert/Update/Select ?
ALTER PROCEDURE dbo.[Inserir Atualizar Conta Bancaria]
(
@.ContaBancariaID As Int,
@.AgenciaID As VarChar(50),
@.BancoID As Int,
@.Numero As Int
)
AS
DECLARE @.Error as int
DECLARE @.Rowcount as int
IF @.ContaBancariaID IS NULL BEGIN
INSERT INTO ContaBancaria (AgenciaID, BancoID, Numero) Values (@.AgenciaID,
@.BancoID, @.Numero)
SELECT @.Error = @.@.ERROR, @.Rowcount = @.@.ROWCOUNT
If @.ERROR <> 0 OR @.ROWCOUNT = 0
GOTO ERROR
END
ELSE BEGIN
UPDATE ContaBancaria SET AgenciaID = @.AgenciaID, BancoID = @.BancoID,
Numero = @.Numero WHERE ContaBancariaID = @.ContaBancariaID
SELECT @.Error = @.@.ERROR, @.Rowcount = @.@.ROWCOUNT
If @.ERROR <> 0 OR @.ROWCOUNT = 0
GOTO ERROR
END
RETURN 0
ERROR:
RAISERROR ('Erro ao realizar a operao', 16,1)
RETURN @.Error
Thanks you all
Bruno N> Should I create one stored procedure for each?
Personally, I prefer two separate stored procedures. The answer depends on
what your criteria for "should" are...
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||One for Insert / Update and another for Select?
Or
One for Insert and another for Update?
Thans,
Bruno N
"Bruno N" <nylren@.hotmail.com> escreveu na mensagem
news:eYd4kN6OFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi!
> Instead of creating one stored procedure for insert and another one for
> update, i did one with both.
> Should I create one stored procedure for each? Should i create one stored
> procedure for Insert/Update/Select ?
> ALTER PROCEDURE dbo.[Inserir Atualizar Conta Bancaria]
> (
> @.ContaBancariaID As Int,
> @.AgenciaID As VarChar(50),
> @.BancoID As Int,
> @.Numero As Int
> )
> AS
> DECLARE @.Error as int
> DECLARE @.Rowcount as int
>
> IF @.ContaBancariaID IS NULL BEGIN
> INSERT INTO ContaBancaria (AgenciaID, BancoID, Numero) Values
> (@.AgenciaID, @.BancoID, @.Numero)
> SELECT @.Error = @.@.ERROR, @.Rowcount = @.@.ROWCOUNT
> If @.ERROR <> 0 OR @.ROWCOUNT = 0
> GOTO ERROR
> END
> ELSE BEGIN
> UPDATE ContaBancaria SET AgenciaID = @.AgenciaID, BancoID = @.BancoID,
> Numero = @.Numero WHERE ContaBancariaID = @.ContaBancariaID
> SELECT @.Error = @.@.ERROR, @.Rowcount = @.@.ROWCOUNT
> If @.ERROR <> 0 OR @.ROWCOUNT = 0
> GOTO ERROR
> END
> RETURN 0
> ERROR:
> RAISERROR ('Erro ao realizar a operao', 16,1)
> RETURN @.Error
> --
> Thanks you all
> Bruno N
>|||You should definitely be separating SELECT procedures from others, in my
opinion. As for whether to separate INSERT from UPDATE, the answer still
depends on your decision criteria.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Bruno N" <nylren@.hotmail.com> wrote in message
news:uuZFqe6OFHA.164@.TK2MSFTNGP12.phx.gbl...
> One for Insert / Update and another for Select?
> Or
> One for Insert and another for Update?
> Thans,
> Bruno N
>
> "Bruno N" <nylren@.hotmail.com> escreveu na mensagem
> news:eYd4kN6OFHA.1392@.TK2MSFTNGP10.phx.gbl...
stored
>|||To add to what the others have said:
I have separate stored procedures to Insert, Update or Delete contacts.
I have one stored procedure to Insert, Update or Delete attendance.
Why? Because you usually don't work on the same form to Insert a contact and
modify another at the same time.
But you will work on one form to Insert, Update and Delete attendance.
...at least in the company that hired me.
"Bruno N" <nylren@.hotmail.com> wrote in message
news:uuZFqe6OFHA.164@.TK2MSFTNGP12.phx.gbl...
> One for Insert / Update and another for Select?
> Or
> One for Insert and another for Update?
> Thans,
> Bruno N
>
> "Bruno N" <nylren@.hotmail.com> escreveu na mensagem
> news:eYd4kN6OFHA.1392@.TK2MSFTNGP10.phx.gbl...
>

Create index before or after insert?

I have a number of remote sql servers from which I collect data. Said data i
s
inserted into #temp tables, such as for example:
CREATE TABLE dbo.#tmp
(
x int not null primary key
, y int not null
)
INSERT INTO dbo.#tmp (x, y)
SELECT x, y FROM server.db.dbo.table
For sync'ing this data with local tables it is adventageous to have an index
on y, x:
CREATE NONCLUSTERED INDEX IX_tmp
ON dbo.#tmp (y, x)
Does anyone see any reason to create that index before or after inserting
the records? I.e. is scenario (A) or (B) below better?
(A) 1. Create temp table; 2. Define index; 3. Insert records
(B) 1. Create temp table; 2. Insert records; 3. Create index
My gut tells me that it's a wash, and keeping the index definition with the
table (in code) is better for maintainability, but there's probably no
performance benefit either way. Or maybe there is a performance benefit to
one that I can't think of?
Comments?
Thanks - KenKH wrote:
> I have a number of remote sql servers from which I collect data. Said
> data is inserted into #temp tables, such as for example:
> CREATE TABLE dbo.#tmp
> (
> x int not null primary key
> , y int not null
> )
> INSERT INTO dbo.#tmp (x, y)
> SELECT x, y FROM server.db.dbo.table
> For sync'ing this data with local tables it is adventageous to have
Temp table usage in stored procedures can be a source of recompilation.
To avoid recompiles (which are costly), you should try to avoid
interleaving DML and DDL statements related to temp tables. Therefore,
you are better off defining all your temp tables up front and creating
indexes on them before inserting or otherwise manipulating data in the
tables.
David Gugick
Imceda Software
www.imceda.com|||If you are doing Bulk Insert of a large number of records at once...
Then drop and recreate the indices. I quote from Books OnLine, From the
Bulk Insert Entry.
"If nonclustered indexes are also present on the table, drop these before
copying data into the table. It is generally faster to bulk copy data into a
table without nonclustered indexes, and then to re-create the nonclustered
indexes, rather than bulk copy data into a table with the nonclustered
indexes in place."
The only exception is when you have a clustered Index on teh table, AND you
have the luxury of pre-sorting the data in in the same Order as they will be
in the Clusterd Index (Therefore Inserting the records in Clustered Index
Order). In THis special case, leave the CLustered Index on the table during
the Insert.
"David Gugick" wrote:

> KH wrote:
> Temp table usage in stored procedures can be a source of recompilation.
> To avoid recompiles (which are costly), you should try to avoid
> interleaving DML and DDL statements related to temp tables. Therefore,
> you are better off defining all your temp tables up front and creating
> indexes on them before inserting or otherwise manipulating data in the
> tables.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Create full sql script from existing mdf

Hi,

In most books on ADO.NET programming, a sample database is given as a series of sql instructions (create database, create table, insert into table values (..), etc ), thereby creating the complete mdf/database file. The question arises: how does one create such a SQL script file from an existing .mdf using SSMSEE/SQL Server 2005 Express?

Cheers,

Daniel

Take a look at this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320987&SiteID=1

|||

Or just use “generate scripts wizard”:

Right-click on the database,

choose “TASKS-> Generate Scripts” and simply follow the wizard steps.

Regards,

Alfred.

Create full sql script from existing mdf

Hi,

In most books on ADO.NET programming, a sample database is given as a series of sql instructions (create database, create table, insert into table values (..), etc ), thereby creating the complete mdf/database file. The question arises: how does one create such a SQL script file from an existing .mdf using SSMSEE/SQL Server 2005 Express?

Cheers,

Daniel

Take a look at this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320987&SiteID=1

|||

Or just use “generate scripts wizard”:

Right-click on the database,

choose “TASKS-> Generate Scripts” and simply follow the wizard steps.

Regards,

Alfred.

Create file if does not exist but use if it does

I have the following SQL code. How can I put a check in here to see
if the file already exists and create it if not but insert the data
into it if it does? Thanks for your help
--[ Declare Variables ]--
DECLARE @.ThisWeek as smallDatetime
DECLARE @.ThisQtr as Integer
DECLARE @.ThisYear as Integer
DECLARE @.ThisYrQtr as Varchar(4)
DECLARE @.CmdStr as Varchar(1000)
--[ Populate the Variable with Month/Quarter/Year info ]--
Select @.ThisWeek = Mondate, @.ThisQtr = Qtr, @.ThisYear = [Year],
@.ThisYrQtr = Cast( (SubString( Cast([Year] as Char(4)), 3, 2) +
'Q' + Cast([Qtr] as Char(1)) ) AS varChar(4))
>From dbHistory.dbo.tbQtrNdx
Where Mondate = fn_Mondate(GetDate())
--[ Build the SQL script to execute ZZZ123 archive ]--
Select @.CmdStr = Select * Into dbHistory.dbo.tbZZZ123_' + @.ThisYrQtr +
' ' +
'From dbHostdata.dbo.tbZZZ123 ' +
'Where Mondate = ''' + master.dbo.fn_formatDate(@.ThisWeek, 'mm/dd/
yy') + ''''
Exec (@.CmdStr)
--[ Build the SQL script to execute Bills archive ]--
Select @.CmdStr = 'Select * Into dbHistory.dbo.tbBills_' + @.ThisYrQtr +
' ' +
'From dbMetrics.dbo.tbBills ' +
'Where Mondate = ''' + master.dbo.fn_formatDate(@.ThisWeek, 'mm/dd/
yy') + ''''
Exec (@.CmdStr)Why not use
if EXISTS (SELECT ...)
BEGIN
-- Update record
END
ELSE
BEGIN
-- create new record
END
-- End Else
taxidermist@.cableone.net wrote:
> I have the following SQL code. How can I put a check in here to see
> if the file already exists and create it if not but insert the data
> into it if it does? Thanks for your help
> --[ Declare Variables ]--
> DECLARE @.ThisWeek as smallDatetime
> DECLARE @.ThisQtr as Integer
> DECLARE @.ThisYear as Integer
> DECLARE @.ThisYrQtr as Varchar(4)
> DECLARE @.CmdStr as Varchar(1000)
>
> --[ Populate the Variable with Month/Quarter/Year info ]--
> Select @.ThisWeek = Mondate, @.ThisQtr = Qtr, @.ThisYear = [Year],
> @.ThisYrQtr = Cast( (SubString( Cast([Year] as Char(4)), 3, 2) +
> 'Q' + Cast([Qtr] as Char(1)) ) AS varChar(4))
>>From dbHistory.dbo.tbQtrNdx
> Where Mondate = fn_Mondate(GetDate())
>
> --[ Build the SQL script to execute ZZZ123 archive ]--
> Select @.CmdStr = Select * Into dbHistory.dbo.tbZZZ123_' + @.ThisYrQtr +
> ' ' +
> 'From dbHostdata.dbo.tbZZZ123 ' +
> 'Where Mondate = ''' + master.dbo.fn_formatDate(@.ThisWeek, 'mm/dd/
> yy') + ''''
> Exec (@.CmdStr)
>
> --[ Build the SQL script to execute Bills archive ]--
> Select @.CmdStr = 'Select * Into dbHistory.dbo.tbBills_' + @.ThisYrQtr +
> ' ' +
> 'From dbMetrics.dbo.tbBills ' +
> 'Where Mondate = ''' + master.dbo.fn_formatDate(@.ThisWeek, 'mm/dd/
> yy') + ''''
> Exec (@.CmdStr)
>

Create default

Hi All,
Can someone tell me how to create a default that put the current date into a record on insert and current date + 1 year into another record!?
Cheers Wimmouse getdate() in the field where date field is used in insert.|||Originally posted by nhariharan
use getdate() in the field where date field is used in insert.

I tried it, but i keeps the null value.|||use pubs
go
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
joinyear int default datepart(yyyy,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go|||Originally posted by Enigma

use pubs
go
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
joinyear int default datepart(yyyy,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go


Thanx the getdate() works.
I use 2 columns 1 named join date and 1 named enddate ,standard users get 1 year acces to the application so when a new user register the enddate must be automatically set 1 year after the joindate,
do you know how to manage that?

Thanx already.

Cheers Wim

I|||Originally posted by Wimmo
Thanx the getdate() works.
I use 2 columns 1 named join date and 1 named enddate ,standard users get 1 year acces to the application so when a new user register the enddate must be automatically set 1 year after the joindate,
do you know how to manage that?

Thanx already.

Cheers Wim

I
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
Enddate datetime default dateadd(yy,1,getdatE())
)|||Originally posted by harshal_in
create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
Enddate datetime default dateadd(yy,1,getdatE())
)

I tried this but the result seems strange:

joindate 13-2-2004 11:48:45 enddate Feb 13 200|||Originally posted by Wimmo
I tried this but the result seems strange:

joindate 13-2-2004 11:48:45 enddate Feb 13 200

create table #abc
(
fname varchar(10),
joindate datetime default getdate(),
endate datetime default dateadd(yy,1,getdate())
)
go
insert into #abc
(
fname
)
select
'Enigma'
go
select
*
from
#abc
go
drop table #abc
go

create database command in trigger

Hi,

As part of setting up automated replication between two servers, I need an insert trigger on a table in a database on server 1 to run a 'create database xxx' command on server 2. Once I've got that I'm sorted.

I tried using linked servers but didn't get anywhere. Finally, I tried creating a trigger on server 1 which ran a dts package (the dts package contained the SQL to create the database on server 2). The dts pacakge ran on its own (I ran it using dtsrun), but not as part of the trigger.

I know that SQL server doesn't support 'create database' commands in triggers, but I would have thought the dts approach would have got around that. Any suggestions? Here's my trigger

CREATE TRIGGER dblist_trigger
ON dblist
FOR INSERT
AS
commit work
EXEC master..xp_cmdshell 'dtsrun /s mbuksqltst03 /u sa /s /n createdb'

Thanks,

IanPlease explain something more about your process|||Hi,

The application I'm working with uses SQL Server and creates databases as part of its operation. So in essence, I'm trying to replicate an entire server rather than just a particular database. I have a script which will create a full set of replication objects for a given database. The problem I have is that when a database is created on the main server, I can't automatically create a blank database on the replicated server to run my replication objects script against.

I've got the replicated server to maintain a list of databases on the main server (a table called dblist - updated by a trigger on the main server). What I was trying to do was create some sort of trigger which will run a create database command when the dblist table on the replicated server has a row inserted in (indicating a new database has been created on the main server). This syntax works, but not when I use it in a trigger

declare @.sqltxt nvarchar (2000),
@.maxid int,
@.name varchar (256)
set @.maxid=(select max(id) from dblist)
set @.name=(select name from dblist where id=@.maxid)
set @.sqltxt=(select 'create database '+ @.name)
EXEC sp_executesql @.sqlTxt

I have even tried putting this sytax in a separate stored procedure and as a T-SQL object in a dts package. But I still can't get it triggered automatically.

Of course, if there is a more elegant way of setting up the replication, I'm open to suggestions.

I hope this is some use.

Ian