How to insert an automated ID?

Unfortunately don't I know, how I can adjust this or whether SQL code is needed and if, where? I do not know myself unfortunately yet so well with the subject out, from therefore am grateful I for each assistance. Many greetings M-l-g

Hello,

I have a problem with an integration services project of SQL server 2005. I have a text file with various data, which can be read out easily with a dataflow task and a flat file source. The data shall be transferred into a target database which I developed according to a star pattern. The fact table can be generated automatically if and only if all data belonging to a dimension table is inserted by myself. Of course, this procedure is not reasonable while having a vast amount of data. That is the reason why I want to generate the dimension tables automatically, too. A dimension is build e.g. like this: ApplicationID | ApplicationName. So, only two columns are available. But I don't know how to insert an automated ID.

I would be thankful for any help.

M-l-GYou can use the rownumber component to add another column with the row number in it.
Also see http://www.sqlis.com/60.aspx for more row counting components.|||

Thank you for the answer but I already found the solution for this problem myself, after I spent some time to look for it.

While creating an ID-column in the database with SQL Server Management Studio there is a property called "Identity specification" and "Is identity". These properties have to be set on "Yes". Afterwards, you are able to enter values for "ID-Steprange" and "ID-Startvalue".

With regards

M-l-G

|||

M-l-G wrote:

Thank you for the answer but I already found the solution for this problem myself, after I spent some time to look for it.

While creating an ID-column in the database with SQL Server Management Studio there is a property called "Identity specification" and "Is identity". These properties have to be set on "Yes". Afterwards, you are able to enter values for "ID-Steprange" and "ID-Startvalue".

With regards

M-l-G

You are better off NOT using the identity columns in SQL Server, and generating your own in SSIS:

http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

How to INSERT a string that contains single-quotes?

My code results in SQL statements like the following one - and it gives an error because of the extra single-quotes in 'it's great':

UPDATE Comments SET Comment='it's great' WHERE UserID='joe' AND GameID='503'

Here's the error I get when I try this code in SQL Server:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.

I need to know how I can insert a string such as 'it's great' - how do I deal with the extra quotes issue? is there a way to ecape it like this 'it/'s great' ? This doesn't seem to work.

Here's the code that generates the SQL. I'm using a FCKeditor box instead of a TextBox, but I got the same error when I was using the TextBox:

string strUpdate = "UPDATE Comments SET Comment='";
strUpdate = strUpdate + FCKeditor1.Value;
//strUpdate = strUpdate + ThisUserCommentTextBox.Text;
strUpdate = strUpdate + "' WHERE UserID='";
strUpdate = strUpdate + (string)Session["UserID"];
strUpdate = strUpdate + "'";
strUpdate = strUpdate + " AND GameID='";
strUpdate = strUpdate + Request.QueryString["GameID"];
strUpdate = strUpdate + "'";

SqlConnection myConnection = new SqlConnection(...);
SqlCommand myCommand = new SqlCommand(strUpdate, myConnection);

try
{
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
ErrorLabel.Text = "Error: " + ex.Message;

}
finally
{
myCommand.Connection.Close();
}

I'm using SQL Server 2005 and ASP.NET 2.0

Much thanks

ok i wont go through your code...but i can tell you the key point

try run

SELECT 'AAAA' SQL retuen AAAA

SELECT '''AAAA''' SQL return 'AAAA' (you need 3 * ' + AAAA + 3 * ' )

hope this give u idea

|||

You should use parameterized query, this problem will go away.

You can find out why by searching this forum. If you still have question, please post back.

|||

limno:

You should use parameterized query, this problem will go away.

You can find out why by searching this forum. If you still have question, please post back.

This is the best advice you will get today. If you value your applications you will never ever build an Sql statement that way again

string strUpdate = "UPDATE Comments SET Comment= @.Comment WHERE UserID = @.UserID AND GameID = @.GameID'";

SqlConnection myConnection = new SqlConnection(...);
SqlCommand myCommand = new SqlCommand(strUpdate, myConnection);

myCommand.Parameters.AddWithValue( "@.Comment", FCKeditor1.Value );
myCommand.Parameters.AddWithValue( "@.UserID", Session["UserID"] );
myCommand.Parameters.AddWithValue( "@.GameID", Request.QueryString["GameID"]);

try
{
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
ErrorLabel.Text = "Error: " + ex.Message;

}
finally
{
myCommand.Connection.Close();
}

|||

Thanks, it works now.

How to import csv to database

Hi,

How to import csv file to sql server databse?Can anybody have the code?

Regards

Brijesh singh

You may use DTS (in SQL Server 2000) or SSIS (in SQL Server 2005).

How?
Checkout this link:http://www.sqldts.com/276.aspx

Good luck.

|||

you can find hereSpeed Up Copy Operations With SqlBulkCopy - Sql Server - CSV through sqlBulkCopy...[ADO.NET 2.0]

Good Luck./.

How to implement this in SSIS

Greetings SQL friends!

I have the following transact SQL code which I want to change to a set of SSIS components.

SELECT blah, blah

FROM PSTAGE..[stage_OFFER_PRICE_DIVIDEND] AS SOPD
LEFT OUTER JOIN PSTAGE..[stage_PRICE_GRP] AS SPG
ON SOPD.PRICE_GRP_ID = SPG.PRICE_GRP_ID
LEFT OUTER JOIN PSTAGE..[stage_type] AS TYP
ON TYP.TYPE_CD=SPG.PRICE_TYPE_TYPE4_CD
and TYP.TYPE_CL_CD = '0017'

I know I can join two data sets using a merge join (left join) but how do I combine a third merge join? Should I be doing this or should I just stick my code in a SQL Task instead?

Your help would be appreciated.

You can do this in SSIS by adding another data source for your third table and adding a second MergeJoin downstream of the first - joining the output of the first merge join to the output of the third data source.

It may be more efficient to push this on to your server using the query you give in a source component, especially as you are performing outer joins.

When doing an outer join in a client such as SSIS, you will pull all the data from the server just to throw some away as unjoined - that's somewhat inefficient use of the server-client transport. It may be more efficient to perform the join in SQL and only pull onto the client data that you will processing further.

Donald