Database

Sushant Duggal wrote:

whenever i create a table in my SQL Server 2000 database, it is created of the type "System".

SELECT 'INSERT INTO MyTable(Column1, Column2) VALUES ('+Column1+', '+Column2+')'
FROM MyTable

ma se wrote:

Please explain what you mean by +Column1+, is this the coloumn name??

ma se wrote:

Or a value of a column?

ma se wrote:

Does this create a whole bunch of Insert statements?

ma se wrote:

Does it work?

ma se wrote:

You have my MyTable listed twice, is this correct?

ma se wrote:

I haven't tried this out yet, going to do so soon, but I was wondering if there wasn't a faster method? What happens if I have like 500 tables (for example)??

ma se wrote:

How can I export my tables and stored procedures to the online database with setting the Identity Specification to No, and keeping it Yes?

The gist: Use scope_identity() rather than @@IDENTITY or you might not get what you're looking for.

"ManyTSQL books show you how to use @@Identity to get the identity of the most recently added row. Many articles online, or in magazines show the same. What you might not know is that it is potentially a source for some very hard to trace bugs in your application.

@@Identity is potentially a very, very bad thing! In almost every case, you should use scope_identity() instead.

Why? @@Identity returns the most recently created identity for your current connection. When you first use it, it might be fine. Until someone adds a trigger. If the trigger causes another identity to be created, guess which identity you'll get in your call to @@Identity? Not nice.

DECLARE @UidAgentGroupID VARCHAR(100)
 
SET @UidAgentGroupID= N'1,2,3,4'
 
DECLARE @sql NVARCHAR(1000)
SET @sql = N'SELECT markup_id FROM Agent_Markup_Fare 
             WHERE UidAgentGroupID IN(?INVARIABLES?)'
 
SET @sql = REPLACE(@sql,'?INVARIABLES?',@UidAgentGroupID)
 
EXEC(@sql)

DECLARE @sqlStatement NVARCHAR(1000)
 
SET @sqlStatement='SELECT AgentsSubGroupID 
                   FROM Agent_Markup_Fare 
                   WHERE AgentsSubGroupID IN(INVARIABLES)'
 
SET @sqlStatement = REPLACE(@sqlStatement,'INVARIABLES',@AgentPreferencesIDS)
 
CREATE TABLE #tmpTbl (AgentsSubGroupId INT NOT NULL)
 
INSERT INTO #tmpTbl
EXEC(@sqlStatement)
 
SELECT 
    @adultmarkup=SUM(dbo.CalculateMarkup(@fareprice,adultMarkup)),
    @childmarkup=SUM(dbo.CalculateMarkup(@fareprice,childMarkup)),
    @infantmarkup=SUM(dbo.CalculateMarkup(@fareprice,infantMarkup)),
    @studentmarkup=SUM(dbo.CalculateMarkup(@fareprice,studentMarkup))
FROM 
    Agent_Markup_Fare 
WHERE 
    fareid=@fareid AND 
    AgentsSubGroupID IN (SELECT AgentsSubGroupID FROM #tmpTbl)
 
DROP TABLE #tmpTbl

DECLARE @Id int;
SELECT @Id = 1;
WHILE @Id IS NOT NULL
BEGIN
    -- Do stuff you need to do here.
 
    -- The next line moves to the next item in the linked list.
    SELECT @Id = NextId FROM MyTable WHERE Id = @Id;
END

Rob Philpott wrote:

Why do you say this isn't really what Sql is designed to do? It seems perfectly acceptable, desirable even (well to me at least!) to have an abstraction where a database can maintain a linked list of data. My operations would be 'return in order', 'delete item in list', 'insert item in list'.