Showing posts with label empty. Show all posts
Showing posts with label empty. Show all posts

Wednesday, March 28, 2012

How to insert empty string

Dear All,
I got a SQL statement like that
Insert into TableA (columnA,columnB,ColumnC) select
VariableA, VariableB,VariableC
Where columnA,columnB,ColumnC are Varchar. VariableA,
VariableB,VariableC are variables return by VB Function. If the
VariableA = A, VariableC = C, VariableB contains no chacracter then
the statemnet would become as follows:
Insert into TableA (columnA,columnB,ColumnC) select A,,C
The above Insert statement is not valid because two comma exist after
select A (,,). Therefore I need to make VariableB contains an empty
space, i.e VariableB = " " . But I do not want VariableB contains a
space. My question is how to make "Insert into TableA
(columnA,columnB,ColumnC) select A,,C"
valid without adding a space character to VariableB. That means how I
can insert VariableB without adding a space character to VariableB?
ThanksWhat about using single quotation --> '' ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||You can't get SQL server to accept a syntactically incorrect statement.
What you should be doing is a little interpretation on the VB side
before you fire off the T-SQL statement to the SQL server. My VB is a
little rusty but it would be something like:
dim a,b,c
dim SQL
..
SQL = "insert into tableA (colA, colB, colC) values ("
if (a = "") then SQL = SQL & "null, "
else SQL = SQL & a & ", "
if (b = "") then SQL = SQL & "null, "
else SQL = SQL & b & ", "
if (c = "") then SQL = SQL & "null)"
else SQL = SQL & c & ")"
..
send your T-SQL string off to the server now
..
It should be dead simple to write around particular cases when you're
preparing the SQL statement on the VB side. Alternately you could
specify "''" instead of "null" if you wanted an empty string in that
column rather than a null. There are a number of different ways you can
do it but they're all fiddling with the SQL string using VB before you
send it to the server.
*mike hodgson*
http://sqlnerd.blogspot.com
hon123456 wrote:

>Dear All,
> I got a SQL statement like that
>
> Insert into TableA (columnA,columnB,ColumnC) select
>VariableA, VariableB,VariableC
>Where columnA,columnB,ColumnC are Varchar. VariableA,
>VariableB,VariableC are variables return by VB Function. If the
>VariableA = A, VariableC = C, VariableB contains no chacracter then
>the statemnet would become as follows:
> Insert into TableA (columnA,columnB,ColumnC) select A,,C
>The above Insert statement is not valid because two comma exist after
>select A (,,). Therefore I need to make VariableB contains an empty
>space, i.e VariableB = " " . But I do not want VariableB contains a
>space. My question is how to make "Insert into TableA
>(columnA,columnB,ColumnC) select A,,C"
>valid without adding a space character to VariableB. That means how I
>can insert VariableB without adding a space character to VariableB?
>Thanks
>
>|||or omit that column
Insert into TableA (columnA,ColumnC) select A,C
Madhivanan|||Yes, but that would take slightly more effort on the VB side so I didn't
bother showing that one.
*mike hodgson*
http://sqlnerd.blogspot.com
Madhivanan wrote:

>or omit that column
>Insert into TableA (columnA,ColumnC) select A,C
>Madhivanan
>
>

Friday, March 9, 2012

How to import a .sql file in ms sql server database ?

Hi,
I am currently new to microsoft sql server, so following a book I need to
create a empty database (is ok), afterwards I need to run a script
(name.sql) against this created database.
The problem is that I don't know how to do this (after having struggled for
one hour with the import / export function).
Who can help me ?
Cheers, Jan
ps. the content of the .sql script :
CREATE TABLE Company(
CompanyID int IDENTITY(1,1),
CompanyName varchar(40) NOT NULL,
Address varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State char(2) NOT NULL,
ZipCode varchar(10) NOT NULL,
Comments text NULL,
CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED (CompanyID)
)
CREATE TABLE Employee(
SSN char(11) NOT NULL,
CompanyID int NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Salary numeric(12, 2) NOT NULL,
DateOfBirth datetime NOT NULL,
CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (SSN)
)
CREATE TABLE Temp(
SSN char(11) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
CompanyName varchar(40) NULL,
CONSTRAINT PK_Temp PRIMARY KEY NONCLUSTERED (SSN)
)
ALTER TABLE Employee ADD CONSTRAINT FK__Employee_Company
FOREIGN KEY (CompanyID)
REFERENCES Company(CompanyID)You can run the script in Query Analyzer (in your SQL Server program group).
Have a read of Books Online before you try that. Here's a link but it should
also be installed with the client tools on your machine:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/qryanlzr/qryanlzr_1zqq.asp?frame=true
--
David Portas
SQL Server MVP
--|||Hi,
Creating the Database:-
1. Open Enterprise Manager
2. Expand the SQL Server groups and select the server name
3. Expand the server and right click above database
4. Choose New database
5. Give the database name and path for MDF AND LDF
6. Click OK
7. Right click above the new database and select options...Choose the
recovery model and click ok. See the details of recovery model in books
online
Executing .SQL file
1. Login to Query Analyzer (Choose from SQL server program groups)
2. OPEN the .SQL file using File -- open
3. Choose the database created from database list
4. Press F5 button in keyboard to execute the script
Thanks
Hari
SQL Server Mvp
"Jan" <janjansenbe@.gmail.com> wrote in message
news:OWU50uRlFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am currently new to microsoft sql server, so following a book I need to
> create a empty database (is ok), afterwards I need to run a script
> (name.sql) against this created database.
> The problem is that I don't know how to do this (after having struggled
> for one hour with the import / export function).
> Who can help me ?
> Cheers, Jan
> ps. the content of the .sql script :
> CREATE TABLE Company(
> CompanyID int IDENTITY(1,1),
> CompanyName varchar(40) NOT NULL,
> Address varchar(30) NOT NULL,
> City varchar(20) NOT NULL,
> State char(2) NOT NULL,
> ZipCode varchar(10) NOT NULL,
> Comments text NULL,
> CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED (CompanyID)
> )
> CREATE TABLE Employee(
> SSN char(11) NOT NULL,
> CompanyID int NULL,
> Firstname varchar(20) NOT NULL,
> Lastname varchar(20) NOT NULL,
> Salary numeric(12, 2) NOT NULL,
> DateOfBirth datetime NOT NULL,
> CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (SSN)
> )
> CREATE TABLE Temp(
> SSN char(11) NOT NULL,
> Firstname varchar(20) NOT NULL,
> Lastname varchar(20) NOT NULL,
> CompanyName varchar(40) NULL,
> CONSTRAINT PK_Temp PRIMARY KEY NONCLUSTERED (SSN)
> )
>
> ALTER TABLE Employee ADD CONSTRAINT FK__Employee_Company
> FOREIGN KEY (CompanyID)
> REFERENCES Company(CompanyID)
>
>

How to import a .sql file in ms sql server database ?

Hi,
I am currently new to microsoft sql server, so following a book I need to
create a empty database (is ok), afterwards I need to run a script
(name.sql) against this created database.
The problem is that I don't know how to do this (after having struggled for
one hour with the import / export function).
Who can help me ?
Cheers, Jan
ps. the content of the .sql script :
CREATE TABLE Company(
CompanyID int IDENTITY(1,1),
CompanyName varchar(40) NOT NULL,
Address varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State char(2) NOT NULL,
ZipCode varchar(10) NOT NULL,
Comments text NULL,
CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED (CompanyID)
)
CREATE TABLE Employee(
SSN char(11) NOT NULL,
CompanyID int NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Salary numeric(12, 2) NOT NULL,
DateOfBirth datetime NOT NULL,
CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (SSN)
)
CREATE TABLE Temp(
SSN char(11) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
CompanyName varchar(40) NULL,
CONSTRAINT PK_Temp PRIMARY KEY NONCLUSTERED (SSN)
)
ALTER TABLE Employee ADD CONSTRAINT FK__Employee_Company
FOREIGN KEY (CompanyID)
REFERENCES Company(CompanyID)You can run the script in Query Analyzer (in your SQL Server program group).
Have a read of Books Online before you try that. Here's a link but it should
also be installed with the client tools on your machine:
http://msdn.microsoft.com/library/d...asp?frame=true
David Portas
SQL Server MVP
--|||Hi,
Creating the Database:-
1. Open Enterprise Manager
2. Expand the SQL Server groups and select the server name
3. Expand the server and right click above database
4. Choose New database
5. Give the database name and path for MDF AND LDF
6. Click OK
7. Right click above the new database and select options...Choose the
recovery model and click ok. See the details of recovery model in books
online
Executing .SQL file
1. Login to Query Analyzer (Choose from SQL server program groups)
2. OPEN the .SQL file using File -- open
3. Choose the database created from database list
4. Press F5 button in keyboard to execute the script
Thanks
Hari
SQL Server Mvp
"Jan" <janjansenbe@.gmail.com> wrote in message
news:OWU50uRlFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am currently new to microsoft sql server, so following a book I need to
> create a empty database (is ok), afterwards I need to run a script
> (name.sql) against this created database.
> The problem is that I don't know how to do this (after having struggled
> for one hour with the import / export function).
> Who can help me ?
> Cheers, Jan
> ps. the content of the .sql script :
> CREATE TABLE Company(
> CompanyID int IDENTITY(1,1),
> CompanyName varchar(40) NOT NULL,
> Address varchar(30) NOT NULL,
> City varchar(20) NOT NULL,
> State char(2) NOT NULL,
> ZipCode varchar(10) NOT NULL,
> Comments text NULL,
> CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED (CompanyID)
> )
> CREATE TABLE Employee(
> SSN char(11) NOT NULL,
> CompanyID int NULL,
> Firstname varchar(20) NOT NULL,
> Lastname varchar(20) NOT NULL,
> Salary numeric(12, 2) NOT NULL,
> DateOfBirth datetime NOT NULL,
> CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (SSN)
> )
> CREATE TABLE Temp(
> SSN char(11) NOT NULL,
> Firstname varchar(20) NOT NULL,
> Lastname varchar(20) NOT NULL,
> CompanyName varchar(40) NULL,
> CONSTRAINT PK_Temp PRIMARY KEY NONCLUSTERED (SSN)
> )
>
> ALTER TABLE Employee ADD CONSTRAINT FK__Employee_Company
> FOREIGN KEY (CompanyID)
> REFERENCES Company(CompanyID)
>
>

How to import a .sql file in ms sql server database ?

Hi,
I am currently new to microsoft sql server, so following a book I need to
create a empty database (is ok), afterwards I need to run a script
(name.sql) against this created database.
The problem is that I don't know how to do this (after having struggled for
one hour with the import / export function).
Who can help me ?
Cheers, Jan
ps. the content of the .sql script :
CREATE TABLE Company(
CompanyID int IDENTITY(1,1),
CompanyName varchar(40) NOT NULL,
Address varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State char(2) NOT NULL,
ZipCode varchar(10) NOT NULL,
Comments text NULL,
CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED (CompanyID)
)
CREATE TABLE Employee(
SSN char(11) NOT NULL,
CompanyID int NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Salary numeric(12, 2) NOT NULL,
DateOfBirth datetime NOT NULL,
CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (SSN)
)
CREATE TABLE Temp(
SSN char(11) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
CompanyName varchar(40) NULL,
CONSTRAINT PK_Temp PRIMARY KEY NONCLUSTERED (SSN)
)
ALTER TABLE Employee ADD CONSTRAINT FK__Employee_Company
FOREIGN KEY (CompanyID)
REFERENCES Company(CompanyID)
You can run the script in Query Analyzer (in your SQL Server program group).
Have a read of Books Online before you try that. Here's a link but it should
also be installed with the client tools on your machine:
http://msdn.microsoft.com/library/de...asp?frame=true
David Portas
SQL Server MVP
|||Hi,
Creating the Database:-
1. Open Enterprise Manager
2. Expand the SQL Server groups and select the server name
3. Expand the server and right click above database
4. Choose New database
5. Give the database name and path for MDF AND LDF
6. Click OK
7. Right click above the new database and select options...Choose the
recovery model and click ok. See the details of recovery model in books
online
Executing .SQL file
1. Login to Query Analyzer (Choose from SQL server program groups)
2. OPEN the .SQL file using File -- open
3. Choose the database created from database list
4. Press F5 button in keyboard to execute the script
Thanks
Hari
SQL Server Mvp
"Jan" <janjansenbe@.gmail.com> wrote in message
news:OWU50uRlFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am currently new to microsoft sql server, so following a book I need to
> create a empty database (is ok), afterwards I need to run a script
> (name.sql) against this created database.
> The problem is that I don't know how to do this (after having struggled
> for one hour with the import / export function).
> Who can help me ?
> Cheers, Jan
> ps. the content of the .sql script :
> CREATE TABLE Company(
> CompanyID int IDENTITY(1,1),
> CompanyName varchar(40) NOT NULL,
> Address varchar(30) NOT NULL,
> City varchar(20) NOT NULL,
> State char(2) NOT NULL,
> ZipCode varchar(10) NOT NULL,
> Comments text NULL,
> CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED (CompanyID)
> )
> CREATE TABLE Employee(
> SSN char(11) NOT NULL,
> CompanyID int NULL,
> Firstname varchar(20) NOT NULL,
> Lastname varchar(20) NOT NULL,
> Salary numeric(12, 2) NOT NULL,
> DateOfBirth datetime NOT NULL,
> CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (SSN)
> )
> CREATE TABLE Temp(
> SSN char(11) NOT NULL,
> Firstname varchar(20) NOT NULL,
> Lastname varchar(20) NOT NULL,
> CompanyName varchar(40) NULL,
> CONSTRAINT PK_Temp PRIMARY KEY NONCLUSTERED (SSN)
> )
>
> ALTER TABLE Employee ADD CONSTRAINT FK__Employee_Company
> FOREIGN KEY (CompanyID)
> REFERENCES Company(CompanyID)
>
>