Creating a table with values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating a table with values

Author

Topic

kitfox69
Starting Member

13 Posts

Posted - 2008-05-15 : 18:33:54

I want to create this table:

create table new_price (
new_price_item_id VARCHAR(25),
new_price_item_lst_lnd_cost NUMERIC(7,2),
new_price_item_prc_2 NUMERIC(7,2) NOTNULL)
values (
" 000001010045 " " $127.05 " " $329.99 "
" 000001031565 " " $147.38 " " $379.99 "
" 000001050014 " " $117.61 " " $299.99 "
" 000001050023 " " $274.45 " " $679.99 "
" 000001050035 " " $296.30 " " $749.99 ")

Is this the proper SYNTAX to insert multiple rows of values or is there something I missed to differentiate between each row of values?

Brooks C. Davis
IT Administrator\Logistics Manager SFTF LLC dba Ashley Furniture Homestores
DELL POWEREDGE 2850 Dual Core Xeon x3 = 1xDB 1xSQL 1xTS | DELL POWEREDGE 2950 Quad Core Xeon = 1xTS | SERVER 2003 | MS SQL 2005 | PERVASIVE EMBEDDED V.9

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-15 : 18:44:03

You can't do this in one command. Use CREATE TABLE to create the table and INSERT INTO to add your data.

But are you even using SQL Server? Your query is so odd, I wonder if you are using something else.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

kitfox69
Starting Member

13 Posts

Posted - 2008-05-15 : 18:52:50

Not sure what you mean by odd?

I do realize mistructured the values. It should be this if it worked like that:

create table new_price (
new_price_item_id VARCHAR(25),
new_price_item_lst_lnd_cost NUMERIC(7,2),
new_price_item_prc_2 NUMERIC(7,2) NOTNULL)
values (
" 000001010045 "," 127.05 "," 329.99 "
" 000001031565 "," 147.38 "," 379.99 "
" 000001050014 "," 117.61 "," 299.99 "
" 000001050023 "," 274.45 "," 679.99 "
" 000001050035 "," 296.30 "," 749.99 "
" 000001050038 "," 314.24 "," 779.99 "
" 000001060021 "," 218.54 "," 549.99 "
" 000001060035 "," 243.95 "," 629.99 ")

Brooks C. Davis
IT Administrator\Logistics Manager SFTF LLC dba Ashley Furniture Homestores
DELL POWEREDGE 2850 Dual Core Xeon x3 = 1xDB 1xSQL 1xTS | DELL POWEREDGE 2950 Quad Core Xeon = 1xTS | SERVER 2003 | MS SQL 2005 | PERVASIVE EMBEDDED V.9

kitfox69
Starting Member

13 Posts

Posted - 2008-05-15 : 19:05:40

What would be the best way to import this data out of excel or an XML document and into the table I want to create?

Brooks C. Davis
IT Administrator\Logistics Manager SFTF LLC dba Ashley Furniture Homestores
DELL POWEREDGE 2850 Dual Core Xeon x3 = 1xDB 1xSQL 1xTS | DELL POWEREDGE 2950 Quad Core Xeon = 1xTS | SERVER 2003 | MS SQL 2005 | PERVASIVE EMBEDDED V.9

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-15 : 19:06:52

By odd, I mean it's not close to valid T-SQL syntax. What tool are you using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

kitfox69
Starting Member

13 Posts

Posted - 2008-05-15 : 19:12:14

I am not using a tool to write the script.

I do, however, use Embedded Pervasive V.9 Control Center to execute all my commands.

Dropping the values part of the statement, is there anything wrong with the create table statement?

FYI I have been learning SQL using an Oracle database and teaching myself how to use MySQL, but the database where I work is SQL Server 2005 with Pervasive.

Brooks C. Davis
IT Administrator\Logistics Manager SFTF LLC dba Ashley Furniture Homestores
DELL POWEREDGE 2850 Dual Core Xeon x3 = 1xDB 1xSQL 1xTS | DELL POWEREDGE 2950 Quad Core Xeon = 1xTS | SERVER 2003 | MS SQL 2005 | PERVASIVE EMBEDDED V.9

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-15 : 19:26:36

Yes there is something wrong with your statement. You can't do that in T-SQL. They must be two separate commands as mentioned in my first post. Here is an example:

CREATE TABLE table1 (Column1 int NOT NULL, Column2 varchar(50) NULL)

INSERT INTO table1 VALUES(1, 'Tara')
INSERT INTO table1 VALUES(99, 'Kizer')

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-15 : 19:30:53

Is it possible for you to use SQL Server Management Studio to do your queries so that we can rule out Pervasive?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

kitfox69
Starting Member

13 Posts

Posted - 2008-05-15 : 19:57:50

I am not familiar with that yet but I will start using it and learn.

/on another note... what if I had another database... on a sepearate server but on the same network... using MySQL.

Would I be able to query for the data from there?

The database is called TEST and the table is called PRICE_MAY.

As an example the server IP is 192.168.1.172. Would I be able to use a proper create table statement and embed a select statement pulling from this separate database?

CREATE TABLE PRICE_NEW (
price_new_item_id VARCHAR(25) NOT NULL,
price_new_prc_2 NUMERIC(7,2) NULL)
as SELECT ?????

columns in the MySQL database are in DB TEST, table is PRICE_MAY, columns are item_id, item_prc_2.

Brooks C. Davis
IT Administrator\Logistics Manager SFTF LLC dba Ashley Furniture Homestores
DELL POWEREDGE 2850 Dual Core Xeon x3 = 1xDB 1xSQL 1xTS | DELL POWEREDGE 2950 Quad Core Xeon = 1xTS | SERVER 2003 | MS SQL 2005 | PERVASIVE EMBEDDED V.9

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-15 : 20:03:07

I don't know enough about MySQL to help out with that. But you wouldn't be able to do your CREATE TABLE/SELECT syntax in SQL Server. For MySQL support, you should post your questions in the MySQL forum over at dbforums.com.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2008-05-15 : 21:22:32

Ask here

http://www.dbforums.com/forumdisplay.php?f=5

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources