INSERT Record in SQL Server

I have six tables in my database; their schema is as follows:

1) postad

userid  varchar(20) 
cityid  varchar(20) 
Ucategory_id    varchar(20) 
ad_id   varchar(20) 
title   varchar(MAX)    
ad_description  varchar(MAX)    
img_id  int username    varchar(20) 
 video  varchar(MAX)    
subcategory_id  varchar(50) 
date_creation   datetime    
postid  int 

2) user details

userpwd varchar(20) 
emailid varchar(30)
mobile  numeric(10, 0)  
squestion   varchar(MAX)    
sanswer varchar(MAX)    
gender  varchar(20)
status_user varchar(20)

3)sub category

subact_id   varchar(50) 
category_id varchar(20) 
subcat_name varchar(50) 

4) imagegallery

img_id  int Unchecked
image_size  bigint  
image_content   image

5) Adtype

adid    varchar(20) 
adtype  varchar(20)

6) category

cat开发者_StackOverflow_id  varchar(20) 
cat_name    char(150)

I want to insert a record into the postad table. How would I go about doing this? Any help will be appreciated.

---

Use the INSERT statement.

In general, one way to formulate your query is like this:

INSERT INTO tablename (columnlist) VALUES (valuelist);

You'd replace tablename with the name of the table you wish to insert into (in this case, postad). Also, you'd replace "columnlist" with the names of the columns you want to insert values into (separated by commas), and replace "valuelist" with the values that correspond to those columns (also separated by columns).

For example, to insert the values of 1 and 2 for userid and cityid, you'd do the following:

INSERT INTO postad (userid, cityid) VALUES (1, 2);

---

In sqlserver create procedure as:

CREATE PROCEDURE PSetPostAd
    (
    @userid  varchar(20),
    @cityid  varchar(20), 
    @Ucategory_id    varchar(20), 
    @ad_id   varchar(20), 
    @title   varchar(MAX),    
    @ad_description  varchar(MAX),   
    @img_id  int,
    @username    varchar(20), 
    @video  varchar(MAX),    
    @subcategory_id  varchar(50), 
    @date_creation   datetime,   
    @postid  int 
    )
    AS
    BEGIN
        INSERT into postad(userid, cityid, Ucategory_id, ad_id, title, ad_description, img_id, ...) values
        (@userid, @cityid, ......)
    END
    GO

In your c# code do something like this:

SqlConnection cn;
public int saveData(string userid, string cityId, ......)
        {
            cn = new SqlConnection(connectionString);
            SqlCommand cmd = getCommand();
            cmd.Parameters["@userid"].Value = userid;
            cmd.Parameters["@cityId"].Value = cityId;
            cmd.Parameters["@Ucategory_id"].Value = UCategory_id;
            //and so on
            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                cn.Close();
            }
        }

        private SqlCommand getCategoryCommand()
        {
            cmd = new SqlCommand("PSetPostAd", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@userid", SqlDbType.VarChar, 20);
            cmd.Parameters.Add("@cityId", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("Ucategory_id", SqlDbType.Varchar, 20);
            //and so on
            return cmd;
        }

---

There are mainly two styles of using insert

Style 1: INSERT INTO PostAd(col1,col2,col3,col4...) VALUES (valforcol1,valforcol2,valforcol3..)

Style 2: INSERT INTO PostAd SELECT col1,col2,col3 FROM <someothertable> 

Considering your table :

userid  varchar(20) 
cityid  varchar(20) 
Ucategory_id    varchar(20) 
ad_id   varchar(20) 
title   varchar(MAX)    
ad_description  varchar(MAX)    
img_id  int username    varchar(20) 
 video  varchar(MAX)    
subcategory_id  varchar(50) 
date_creation   datetime    
postid  int 

If you choose style1:

INSERT INTO PostAd (userid, cityid, Ucategory_id, ad_id,title,ad_description,img_id, username,video, subcategory_id,date_creation,postid)
VALUES (1,123,443,33,'My Ad','This is a great ad',123,'bradpitt','www.youtube.com/v=23e232',12,GETDATE(),1234)

Just make sure here that all the IDs e.g. userid, cityid, ucategory_id are valid ids and come from the master tables that you have created like category or adtype.

Style 2 is normally used when the data you want to insert in the current table has to come through a select query. The output of the select query is inserted as it is into the target table. One thing to make sure in using style 2 is that the output of the select query must contain exactly the same number of columns as the table in which you are trying to insert