|
Please suggest me how to generate sql script for tables with data?
I hava some master tables contains data in it. I want to generate sql script with data. i.e., on run the script it should create the table structure and insert the data too.
Please suggest me...
|
|
|
|
|
dhananjayamurthy wrote: I want to generate sql script with data. i.e., on run the script it should create the table structure and insert the data too.
You can query the data structure with the views on INFORMATION_SCHEMA. Although, that is quite complex task - I usually leave it to the Enterprise Manager to generate the creation scripts for me.
Data population scripts are slightly easier. You SELECT the data from the table, but concat' it all into one columns
SELECT 'INSERT INTO TableName(PK, Col1, Col2) VALUES ('+PK+', '+Col1+', '+Col2+')'
FROM TableName
|
|
|
|
|
wrote:
You can use DTS package wizard to get the backup of table's backup.
Hope this helps you. ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Knock out 't' from can't,
You can if you think you can
|
|
|
|
|
i am usind dataadapter and dataset objects to bind some data from the database in the textbox control. the code i m using is as follows:
textbox1.databindigs.add("text",dataset.tables(0),"Name")
This code is able to retrieve the field 'Name' from the database and puts it in this textbox. Now i want that if some change is made in this textbox,then that should be updated to the database table as well. for this purpose i m using dataset.acceptchange method. But this is not giving result.
please provide code if possible....
thanks..
-- modified at 14:40 Monday 19th March, 2007
|
|
|
|
|
Hi
I have a stored proc that creates a job with a job step that runs an integration services package.
It all works perfectly fine if the stored proc is on the server where the job needs to be created. It creates the job on the server where the stored proc is located and thus called from. What would I need to change to be able to run this stored proc on the server where it is located, but have it create the job on another server?
Thanks,
RainFox - cunning, with some cloudy clueless moments, yet cool and fresh
|
|
|
|
|
Hey all,
We use primary keys to enforce uniqueness and thus provide a handle where you can get a particular row out of a table (and all the relationship stuff with other tables etc.) When the primary key is a compound key of several fields, what are the guidelines on their order within the primary key? I presume it is best to keep the more important ones (principle identifier, dates etc.) as the top fields in the key, and the lesser used ones lower down.
So, if I have a primary key which is say AssetId, [Date], DataSource, UserId in that order would my table benefit with a non-clustered index just on AssetId and [Date], or do you in effect get pretty much this through the primary key?
Hope that makes sense.
Regards,
Rob Philpott.
|
|
|
|
|
Hi Rob
If your first index is on AssetId, Date, DataSource and UserId (in that order) then you don't really need a separate index on AssetId and Date because they are the leading columns in you primary key.
The conventional wisdom is that you should put your most unique columns at the front of indexes. I tend to recommend that you put the columns that are most commonly searched upon (which may be different).
If you also needed to frequently search on UserId and Date then you would consider using an additional index.
Note that (by default) SQL-Server uses a clustered index to enforce primary keys. This has the side-effect of appending your primary key columns to every non-clustered index that you create on the table. If you are using this primary key to join tables then you might want to consider using a separate identity column as the primary key on this table, and a separate non-clustered index on the four columns that you noted in your question.
Regards
Andy
|
|
|
|
|
Thanks Andy,
Pretty much as I thought then, although your point about non-clustered indexes appending the primary key on is an interesting one. I had assumed that the non-clustered index would have some sort of 'pointer' which could read straight into one of the data files rather than a primary key value to lookup.
I would imagine this could make the indexes very large, especially as in my table there are 6 columns in the primary key. Worth thinking about!
Thanks again.
Regards,
Rob Philpott.
|
|
|
|
|
For a table with a clustered index, the pointer into the base table is the clustered index columns. If you don't like this, don't use clustered indexes.
A clustered index means that the actual data rows are stored in the leaf levels of the index.
If the non-clustered indexes stored some physical location data as the pointer value, as they do for 'heaps' (tables with no clustered index), the indexes would have to be updated as rows moved within a page or between pages due to additional records being inserted. This wouldn't be easy.
|
|
|
|
|
Hi friends
i need to perform by using stored processors.
can ne1 tell me how do i do it?
thanks
Pathan
GOD HELP THOSE WHO HELP THEMSELF
|
|
|
|
|
Hi
Do you mean that you want to pass current page, no. of records to display per page, total number of records etc....
This can be done.
Harini
|
|
|
|
|
One approach is to pass in the id of the last row of the previous page and select rows based on this value. For instance, to retrieve 20 rows at a time:
SELECT TOP 20 ....
FROM Table1
WHERE MyID > @ID To get the first batch, you would set @ID to 0.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi,
I would like to retrieve specific region names, where a region code is less then 3 characters. I cant just do a normal select query, as some of the codes are more then 3 characters. Here is my sql query, but its not returning anything.
SELECT RegionName, RegionCode
FROM Region
WHERE DATALENGTH(RegionCode) < 3
ORDER BY RegionName
|
|
|
|
|
Change query as below and try
SELECT RegionName, RegionCode
FROM Region
WHERE len(RegionCode) < 3
ORDER BY RegionName
|
|
|
|
|
Thanks alot. It worked. I never thought of LEN()...
|
|
|
|
|
hi everyone,
i need sample or tutorial of an application that work with ms access database (*.mdb) or sqlserver that installed in server and clients could run application with shortcut and work
i wanna create a networking application
many thanks
|
|
|
|
|
hamid_m wrote: clients could run application with shortcut and work
What does that mean?
hamid_m wrote: i wanna create a networking application
And what do you mean by "networking application"? To me a "networking application" means an application that configures networks.
|
|
|
|
|
hi,
i have two databases which are used for different projects. but i need to update a column of one database with the data from another database.
is there a way to do that using SPs? or any other way?
thanks.
regards.
|
|
|
|
|
You can do this job by these statement.
1)Declare a Variable like v
2)Select the record you want use by SELECT Statement and assign it to the v by = operator
3)Update the record you want by UPDATE Statement and set it to the v.
I hope this solution is useful
|
|
|
|
|
Here we go!!
Use joins (normal, left or right) between tables across the databases. You can call different databases in TSQL with dot operators (..).
Eg:-
Select n.name from northwind..authors n, pubs..aurhors p
where p.authorid = n.authorid
|
|
|
|
|
You need to prefix the schema.tablename with the name of the database. For instance:
Update database1.myschema.table1 set value = 10 where id = 20
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
hi
i want to see an example that how to use trigger in sql server 2005,thanks ..
|
|
|
|
|
Now, repeat after me:
Google is my friend. Google is my friend.
Clickety[^]
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I use SQL Server 2005 and Visual Basic 2005.
I write these Statement in SQL Server but it executes these with error and the record dose not inserted into table Project.
CREATE PROCEDURE Insert_Project
(@pNumber_M int,
@pRow_Agreement int,
@pRow_Machine int,
@pRow_Language int)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
DECLARE @Number_O int;
SELECT @Number_O=Number-sum(Number_A) FROM Machine_View WHERE Row=@pRow_Machine GROUP BY Number
SET @Number_O=@Number_O-@pNumber_M;
INSERT INTO Project
(Number_A,Number_D,Row_Agreement,Row_Machine,Row_Language)
VALUES
(@pNumber_M,@Number_O,@pRow_Agreement,@pRow_Machine,@pRow_Language)
UPDATE Project
SET Number_D=@Number_O
WHERE Row_Machine=@pRow_Machine
END TRY
BEGIN CATCH
SELECT ERROR_PROCEDURE() AS ErrorProcedure;
END CATCH
END
GO
Please help me.
Thank you.
|
|
|
|
|
mghiassi wrote: it executes these with error
What error?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
