|
To this and your other question below.
What is the row delimiter used in the file? If it's a newline, just add
BULK INSERT dbo.tb_DEMO
FROM 'C:\DEMO08Q1.TXT'
WITH
(
FIELDTERMINATOR ='$'
ROWTERMINATOR = '$\n'
)
BTW, does your file have only 1 row?
|
|
|
|
|
hello SimulationofSai,
Thanks for the reply. Its working now. File contains almost 104411 rows.
I did this :
BULK INSERT dbo.tb_DEMO
FROM 'C:\DEMO08Q1.TXT'
WITH
(
FIRSTROW = 2
,FIELDTERMINATOR ='$'
,ROWTERMINATOR = '$\n'
)
FirstRow = 2 solved my first problem and ur suggestion helped me to solve my 2nd problem.
Thanks again... 
|
|
|
|
|
Anyone know or use a tool that let's you point it at a table in a DB and it will then generate a SQL script for you that drops/creates all the indexes on the table? Thanks. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Chris Meech wrote: Anyone know or use a tool that let's you point it at a table in a DB and it will then generate a SQL script for you that drops/creates all the indexes on the table? Thanks.
SQL Management Studio?
You've to script twice, once for drop and once for create. And when you script a table, it'll also script CREATE TABLE which you've to remove manually.
Yeah, it can't be done in one step. But it's not too complicated either...
|
|
|
|
|
Sorry, I should have mentioned that it's not for SQL Server, but for Oracle specifically, though I was hoping for a tool that would be database 'agnostic'.
I done lots of Google searching and found lots of scripts that will tell me how to determine what indexes are defined, but I'm searching for something that goes a step further and puts a drop/create script together for me. Though I also know that may be kind of tough when it comes to unique/primary keys for a table. Thanks, though.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
There are a number of tools out there, TOAD is one, used to be for Oracle only but I think it is agnostic now. A goole search should turn up plenty (paid), I don't know of any free ones.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: TOAD is one, used to be for Oracle only but I think it is agnostic now.
Yes, it also works with MS SQL Server now. A nice tool to have around
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Hello All,
I am developing a Windows Service in which I download some data from net. Data which I download is in text file. Text file contains the data and column name of the Table and is seperated by $.
Something like this :
C1$C2$C3$C4$C5$C6$C7$C8$C9$C10$C11$C12$C13$C14$C15$C16$C17$C18$C19$C20$C21$C22$C23$
5476771$6638853$I$$5476771-2$$$20071003$DIR$$$$$$N$$$20071003$OT$$N$N$UNITED STATES$
5476957$6689344$I$$5476957-7$20070927$$20071002$DIR$$$59$YR$F$N$142$LBS$20070930$OT$$N$Y$UNITED STATES$
I worte the following Query for Bulk Insert:
BULK INSERT dbo.tb_DEMO
FROM 'C:\DEMO08Q1.TXT'
WITH
(
FIELDTERMINATOR ='$'
)
I am facing 2 problems in this:
1: I have to delete the column Names from the file and then fire this query.
2: If I delete the column Names then it works but in the last column the data is inserted with $. example: UNITED STATES$
Please help me....
Thanks in Advance.
|
|
|
|
|
How to write Query to get the datatype of Fields or Columns of a table.
and How to write query to get all the table names in a database.
Thanks,
Preethi.
|
|
|
|
|
How to write query to get all the table names in a database.
Ans 1><code>select name as [Tablename] from sysobjects where xtype='u'</code>
How to write Query to get the datatype of Fields or Columns of a table
Ans 2> <code>SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName ORDER BY ORDINAL_POSITION</code>
Reasons are not Important but Results are Important.
Swati
|
|
|
|
|
Hi,
Getting table names from database is giving results. Thanks For Great support.
But, The next Query is giving problems Please look at this
i have a table name Customers in northwind, So i modified the Query which you given like this
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS
WHERE <b>TABLE_NAME = Customers </b>ORDER BY ORDINAL_POSITION
But it is giving Error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Customers'.
Thanks,
Preethi
|
|
|
|
|
hey please look
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers' ORDER BY ORDINAL_POSITION
OR
declare @tablename varchar(20)
set @tablename = 'Customers'
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename ORDER BY ORDINAL_POSITION
Reasons are not Important but Results are Important.
Swati
|
|
|
|
|
Hi ,
I applied
declare @tablename varchar(20)
set @tablename = 'Customers'
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'Customers' ORDER BY ORDINAL_POSITION
But still iam getting Error in execution
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '='.
Thanks Swati for your Kind support.
|
|
|
|
|
Preethi in your query there is error please put space between
INFORMATION_SCHEMA.COLUMNSWHERE
thats why you getting error of '=' sign
declare @tablename varchar(20)
set @tablename = 'Customers'
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = tablename ORDER BY ORDINAL_POSITION
Reasons are not Important but Results are Important.
Swati Tripathi
|
|
|
|
|
Thanks Swati,
Iam feeling shame to not caught this Synax error.
Once again Thanks for support.
Bye,
Preethi
|
|
|
|
|
I would assume you have SQL 2005, look into the information schema views, in management studio under views|system views
If you are in an earlier version you are going to have to make them up using sysobjects and syscolumns.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
iam using Sql server 2000.
|
|
|
|
|
Hi
I have result set as
table name=ID
ID1 ID2 ID3
1 3 1
2 6 1
1 1 1
I want output as
Name1 Name2 Name3
A C A
B D A
A A A
I have one table for name is Name field of this table are:Id,Name.
I m trying query as
Select Name.Name as Name1 Name ,Name.Name as Name2 from Name,Name.Name as Name3
from Name
inner join ID on Name.ID=ID1
Inner join ID on Name.ID=ID2
inner join ID on Name.ID=ID3
above query is giving error. Plz help in this
thanx
Care Carrer
|
|
|
|
|
Man thats ugly, got to be one on the lousiest data structures around.
Joins are not going to do it. You will need to use sub selects.
select <br />
(Select name from name where ID = ID1) name1,<br />
(Select name from name where ID = ID2) name2, <br />
(Select name from name where ID = ID3) name3<br />
from ID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i have a table with thousands of records. i have to change the status of the record if 3 new records entered in the table. for eg if i have 4 records in the table i shoud change the first record and for the second record status change, i should have three more new records except already existing records. Please help
Thank You!
|
|
|
|
|
Need more info but just from the sound of it you should be using 1 record with nested records in XML using XML data type. If you are using a queue you are using xml for the conversation.
|
|
|
|
|
What SQL language? Can make SQL Server downtime (Dead, paralysis)
I was only joking and friends
|
|
|
|
|
ChinaTJBoy wrote: I was only joking and friends
Ohh!!! I thought that some Chinese is practicing English. 
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
So a Rabbi, a Monk and a Priest walk into a bar and the bartender says "What is this some kind of joke?"
//I am available for birthday party's and barmitzfas....
|
|
|
|
|
i think this is not right place for sharing your jokes...
Reasons are not Important but Results are Important.
Swati
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
