bcp utility

I am trying to create a flat file from SQL server and here is
what my query looks like:
bcp "select intakeid from tblintake" queryout d:\out.txt -c
I got this error: "Line 1: Incorrect syntax near 'queryout'."
when I run it in query analyzer. Can anyone please tell me what
I did wrong? I
Thanks.
MLPMLP,
> I am trying to create a flat file from SQL server and here is
> what my query looks like:
> bcp "select intakeid from tblintake" queryout d:\out.txt -c
> I got this error: "Line 1: Incorrect syntax near 'queryout'."
> when I run it in query analyzer. Can anyone please tell me what
> I did wrong? I
Bcp is a command line utility. You need to run it from a command
prompt (aka DOS window). It cannot be run in Query Analyzer.
Linda|||If you want to run BCP from QA, you need to use
xp_cmdshell.
>--Original Message--
>I am trying to create a flat file from SQL server and
here is
>what my query looks like:
>bcp "select intakeid from tblintake" queryout
d:\out.txt -c
>I got this error: "Line 1: Incorrect syntax
near 'queryout'."
>when I run it in query analyzer. Can anyone please tell
me what
>I did wrong? I
>Thanks.
>MLP
>.
>

bcp select querylist error

Hi,
Does BCP Select work in MS SQL Server 7.0, because when I run this
command :

bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname"
queryout Authors.txt -c -SWIN2K -Usa

I always get this error :

output
--------------------
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character
type]
[-N keep non-text native] [-6 6x file format] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]

(12 row(s) affected)

MS SQL SERVER (7.00.623) is on WIN2000 SP4 Server

Thanks in advance
Nipon WongtrakulYou can create a view (with only the selected fields) and export the
result of the view using BCP. That will solve your problem.|||Nipon (niponw@.yahoo.com) writes:
> Hi,
> Does BCP Select work in MS SQL Server 7.0, because when I run this
> command :
> bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname"
> queryout Authors.txt -c -SWIN2K -Usa

That command looks fine to me, except that you are missing the -P option
to specify a password. (Or use -T to specify trusted connection rather
than -Usa.)

From which context are you running this command? A command-line window?
SQL Agent? Or something else?

And just to be sure: above you have split the command on two lines, but
this is only because of the format for news articles. You have a single
line in real life, don't you?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BCP results in 3 rows

Good Day

I want to know if bcp can do this for me,(I get my results nicely from fetch statement) lets say I do this
Select Fileid, FileNo, VersionNo, Fieldname, Fileid from TableA
and my results are

1 80 6.0 34

I want my text file that I will use bcp to recreate to be like in this format
VersonNo
Fileid
Select Fileid, FileNo, VersionNo, Fieldname, Fileid from TableA

6.0
34
1 80 6.0 34Refer to the books online for BCP Format file option to get the task.|||Refer to the books online for BCP Format file option to get the task.sql

BCP queryout

Hello,
How can I include the columns titles in a bcp SELECT QUERYOUT command? (I
wish I could obtain the same result than in Query Analyzer)
Is there an other way to export the result of a select statement?
Best regardswhy don't use a view to bcp out!
Edwar Bishara
www.sqlcare.com
edwarb@.sqlcare.com|||BCP will not output the column headings. Only the data. You can get the
column headings by using command line osql with the -h parameter.
Rand
This posting is provided "as is" with no warranties and confers no rights.

BCP Once again...

I have to output into an external file for more than one query out put. Presently what i am doing is :-

bcp "SELECT l.Name, j.Name FROM msdb..sysjobs j INNER JOIN master..syslogins l ON l.sid = j.owner_sid" queryout %5\\JOBS.xls -c -S%1 -U%3 -P %4

bcp "select l.name from master..syslogins l with (nolock) inner join %2..tbluser j on l.name = j.tloginname" queryout %5\\SQLUSERS.xls -c -S%1 -U%3 -P %4

The above are the two queries and i export into two different excel files.

Is there any way i can append the second select query data into the already created excel file?

--Sandui don't think you can do it straight from bcp, but you can add a third line to your batch file:

copy first_file.xls + second_file.xls third_file.xls|||Originally posted by ms_sql_dba
i don't think you can do it straight from bcp, but you can add a third line to your batch file:

copy first_file.xls + second_file.xls third_file.xls

That was cool solution...

Now a few more questions :-)

1. Can i give any spacing between the first and second file data in the third file? Or can i give any heading before the data of the first and the second file?

2. After copying the data into the third file how can i delete the first and the second file through the batch file?

--Sandu_Bangalore|||1. echo. >> third_file.xls

2. del first_file.xls & del second_file.xls

BCP in stored procedure

Hi,
I am using the following statements in a atored procedure to be executed in SQL Server 2000.

SET @.QUERY = 'bcp "SELECT * FROM FDWSTGD.DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'

SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''

EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE

I get the following error:
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@.L_CURRENTDATE'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Can someone help me out?

Regards,
Bharathram GHi,
I am using the following statements in a atored procedure to be executed in SQL Server 2000.

SET @.QUERY = 'bcp "SELECT * FROM FDWSTGD.DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'

SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''

EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE

I get the following error:
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@.L_CURRENTDATE'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Can someone help me out?

Regards,
Bharathram G

It seems many things are missing,plz provide full code of the stored pro.
Joydeep|||Hi,
Following is the full code:
create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)
DECLARE @.L_OUT_FILENAME VARCHAR(100)
DECLARE @.QUERY NVARCHAR(4000)

SET @.L_CURRENTDATE = GETDATE()
SET @.L_OUT_PATH = '"C:\'
SET @.L_OUT_FILENAME = 'out.txt'

SET @.QUERY = '"SELECT * FROM DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE"'
SET @.QUERY = 'bcp "'+@.QUERY+'" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''
EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE

END|||Hi,
Following is the full code:
create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)

Hi ,
Try this ...

create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)
DECLARE @.L_OUT_FILENAME VARCHAR(100)
DECLARE @.QUERY NVARCHAR(4000)

SET @.L_CURRENTDATE = GETDATE()
SET @.L_OUT_PATH = 'C:\'
SET @.L_OUT_FILENAME = 'out.txt'

SET @.QUERY = 'SELECT * FROM test.dbo.PACK_DELETION_LOG WHERE convert(varchar(12),delete_dt,101) =convert(varchar(12),getdate(),101)'
print @.query

SELECT * FROM PACK_DELETION_LOG WHERE day(DELETE_DT) = day(@.L_CURRENTDATE)

SET @.QUERY = 'bcp "'+@.QUERY+'" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+' -c -P '
print @.query
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''

EXECUTE SP_EXECUTESQL @.QUERY

end

Joydeep

BCP help -Null values in select

I am using a bcp command to cretae a text file.

set @.bcpcommand = 'bcp "select med + replicate('' '',10-datalength(med)),ml+ replicate('' '',20-datalength(ml)),iname + replicate('' '',10-datalength(iname)) from copy_tbl" queryout "'+ @.filename + '" -U -P -c'

exec master..xp_cmdshell @.bcpCommand

Some of the values in the select statement are Null values and are getting skipped in the text file .My output looks like
At A02 At1E
AtE A03 At2E
c100 c1230
I want them to allign but the third row has a null in the middle so it skips it and put the third value in the seconds placeset @.bcpcommand = 'bcp "select COALESCE(med,'') + replicate('' '',10-datalength(COALESCE(med,''))),COALESCE(ml,'') + replicate('' '',20-datalength(COALESCE(ml,''))),COALESCE(iname,'') + replicate('' '',10-datalength(COALESCE(iname,''))) from copy_tbl" queryout "'+ @.filename + '" -U -P -c'

This will replace any NULLs with ''|||Thanks a lot. It works just great.

Thank You

BCP Error

Hi there,

Does anyone know why this won't work:

bcp "SELECT DISTINCT Label From GMS_48hrAccess..tbl_SurgerySlot" queryout "c:\Labels.txt" -c -S@.@.SERVERNAME

It gives me the following error message:

Server: Msg 170, Level 15, State 1, Procedure sproc_ExportLabels, Line 9
Line 9: Incorrect syntax near 'bcp'.I'd expect a "SQL Server does not exists or access denied" errormessage but yours even suggests an error in a procedure. I really wonder how the sp comes into play, I'd suggest you remove the @.@.Servername and change it into the name of the server instead. @.@.Servername is not (directly) available from a command-prompt.|||Thanks , but I've already tried inserting the server name directly with/without quotation marks and it still doesn't work...I can get it to work if I do the following:

DECLARE @.cmdshell varChar(255)

SET @.cmdShell = 'bcp "SELECT DISTINCT Label From GMS_48hrAccess..tbl_SurgerySlot" queryout c:\Labels.txt -c -S' + @.@.SERVERNAME

exec master..xp_cmdshell @.cmdShell

However, this only seems to work on SQL Server 2000 and I need it to work on SQL Server 6.5 as well....hence why I was trying to get the previous to work.|||That could explain why the errormessage indicates a fault in the sp: you're running it from the sp. I have a case where @.@.servername is NULL btw, don't know how bad that is, but concatting a null to var/char usually results in a null var/char. The parameter -T might also help authenticating. Other than that, I don't see what's wrong but I don't know much about 6.5.|||You need to indicate if it's a trusted connection -T, or supply the user name and the password...|||That's an SQL error message. BCP is intended to be executed from the Windoze command prompt, not from within Query Analyzer. Where are you trying to execute that command?

-PatP|||Thanks Pat, that explains it! So my xp..cmdShell example is the correct way to do it, any ideas why this wouldn't work on a 6.5 server?|||Thanks Pat, that explains it! So my xp_cmdShell example is the correct way to do it, any ideas why this wouldn't work on a 6.5 server?|||A lot would depend on what error the 6.5 machine coughed up when you tried to run it... For one thing, the queryout parameter wasn't supported on 6.5, and it was a lot touchier about authentication so you'd have to provide /U and /P values.

-PatP

bcp copying,but no file

DECLARE @.query NVARCHAR(2000)
SELECT @.query = 'bcp "SELECT * from dbname..tablename" queryout c:\test.txt -c -Slocalhost -Usa -Ppassword'
EXEC master.dbo.xp_cmdshell @.query

I have tried doing this with a test table and this is the output
NULL
Starting copy...
NULL
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 16
NULL

and I did have 3 row,but file is still not created, I have put the server name,login,pasword inside "" and without it,no error,no file.

Could somebody please help meWhere are you looking for the file?

bcp command

if i use:
--code--
DECLARE @.CMD VARCHAR(8000)
SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"' +
@.@.servername + '" -T'
exec master..xp_cmdshell @.CMD
--end code--
how could i put the column headers in this spreadsheet?
or
is there a better way of getting a sql statement to save directly to excel?Have a look here:
76c9997" target="_blank">http://groups.google.de/group/micro...
76c9997
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"UNOTech" <UNOTech@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CDA8B4C3-3CB1-418A-A649-B928CB8FB275@.microsoft.com...
> if i use:
> --code--
> DECLARE @.CMD VARCHAR(8000)
> SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"'
> +
> @.@.servername + '" -T'
> exec master..xp_cmdshell @.CMD
> --end code--
> how could i put the column headers in this spreadsheet?
> or
> is there a better way of getting a sql statement to save directly to
> excel?|||I'd advise to do it directly from Excel using the menu choices in Excel. go
to Data...Get Extranal Data...New Database Query... It is very clean. You
will need to set up an ODBC connection to do this.
"UNOTech" wrote:
> if i use:
> --code--
> DECLARE @.CMD VARCHAR(8000)
> SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"'
+
> @.@.servername + '" -T'
> exec master..xp_cmdshell @.CMD
> --end code--
> how could i put the column headers in this spreadsheet?
> or
> is there a better way of getting a sql statement to save directly to excel?[/color
]

bcp and xp_cmdshell

Hi Experts,
When I execute the command
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
from tempdb..EventsArchive" queryout
"c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
From the command line the bcp works fine. (I need the full path for the
bcp because we have Sybase as well on the machine)
However, when I execute the code below it form a sp it fails.
declare @.statement varchar(2000)
set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
"select Severity from tempdb..EventsArchive" queryout
c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
exec master..xp_cmdshell @.statement
Thanks,
Avi
Hi
What does BOL say about more than one double quote?
Syntax
xp_cmdshell {'command_string'} [, no_output]
Arguments
'command_string'
Is the command string to execute at the operating-system command shell.
command_string is varchar(8000) or nvarchar(4000), with no default.
command_string cannot contain more than one set of double quotation marks. A
single pair of quotation marks is necessary if any spaces are present in the
file paths or program names referenced by command_string. If you have trouble
with embedded spaces, consider using FAT 8.3 file names as a workaround.
no_output
Is an optional parameter executing the given command_string, and does not
return any output to the client.
Regards
Mike
"Avi" wrote:

> Hi Experts,
> When I execute the command
> "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
> from tempdb..EventsArchive" queryout
> "c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
> From the command line the bcp works fine. (I need the full path for the
> bcp because we have Sybase as well on the machine)
> However, when I execute the code below it form a sp it fails.
> declare @.statement varchar(2000)
> set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
> "select Severity from tempdb..EventsArchive" queryout
> c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
> exec master..xp_cmdshell @.statement
> Thanks,
> Avi
>
>
>
|||Hi Avi,
You can also use it the following way;
SET @.conn = '" -U <username> -P <pwd> -c'
SET @.filename = '\\servername\foldername\test'+'.txt')
SET @.sql1 = 'BCP "SELECT * FROM <table name>)" QUERYOUT "'
SET @.sql1 = @.sql1 + @.filename + @.conn
EXEC master..xp_cmdshell @.sql1
You don't have to give full path of BCP utility.
Thanks
Yogish

bcp and xp_cmdshell

Hi Experts,
When I execute the command
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
from tempdb..EventsArchive" queryout
"c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
From the command line the bcp works fine. (I need the full path for the
bcp because we have Sybase as well on the machine)
However, when I execute the code below it form a sp it fails.
declare @.statement varchar(2000)
set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
"select Severity from tempdb..EventsArchive" queryout
c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
exec master..xp_cmdshell @.statement
Thanks,
AviHi
What does BOL say about more than one double quote?
Syntax
xp_cmdshell {'command_string'} [, no_output]
Arguments
'command_string'
Is the command string to execute at the operating-system command shell.
command_string is varchar(8000) or nvarchar(4000), with no default.
command_string cannot contain more than one set of double quotation marks. A
single pair of quotation marks is necessary if any spaces are present in the
file paths or program names referenced by command_string. If you have troubl
e
with embedded spaces, consider using FAT 8.3 file names as a workaround.
no_output
Is an optional parameter executing the given command_string, and does not
return any output to the client.
Regards
Mike
"Avi" wrote:

> Hi Experts,
> When I execute the command
> "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
> from tempdb..EventsArchive" queryout
> "c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
> From the command line the bcp works fine. (I need the full path for the
> bcp because we have Sybase as well on the machine)
> However, when I execute the code below it form a sp it fails.
> declare @.statement varchar(2000)
> set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp
"
> "select Severity from tempdb..EventsArchive" queryout
> c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
> exec master..xp_cmdshell @.statement
> Thanks,
> Avi
>
>
>|||Hi Avi,
You can also use it the following way;
SET @.conn = '" -U <username> -P <pwd> -c'
SET @.filename = '\\servername\foldername\test'+'.txt')
SET @.sql1 = 'BCP "SELECT * FROM <table name> )" QUERYOUT "'
SET @.sql1 = @.sql1 + @.filename + @.conn
EXEC master..xp_cmdshell @.sql1
You don't have to give full path of BCP utility.
Thanks
Yogish

bcp and xp_cmdshell

Hi Experts,
When I execute the command
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
from tempdb..EventsArchive" queryout
"c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
From the command line the bcp works fine. (I need the full path for the
bcp because we have Sybase as well on the machine)
However, when I execute the code below it form a sp it fails.
declare @.statement varchar(2000)
set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
"select Severity from tempdb..EventsArchive" queryout
c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
exec master..xp_cmdshell @.statement
Thanks,
AviHi
What does BOL say about more than one double quote?
Syntax
xp_cmdshell {'command_string'} [, no_output]
Arguments
'command_string'
Is the command string to execute at the operating-system command shell.
command_string is varchar(8000) or nvarchar(4000), with no default.
command_string cannot contain more than one set of double quotation marks. A
single pair of quotation marks is necessary if any spaces are present in the
file paths or program names referenced by command_string. If you have trouble
with embedded spaces, consider using FAT 8.3 file names as a workaround.
no_output
Is an optional parameter executing the given command_string, and does not
return any output to the client.
Regards
Mike
"Avi" wrote:
> Hi Experts,
> When I execute the command
> "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity
> from tempdb..EventsArchive" queryout
> "c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210
> From the command line the bcp works fine. (I need the full path for the
> bcp because we have Sybase as well on the machine)
> However, when I execute the code below it form a sp it fails.
> declare @.statement varchar(2000)
> set @.statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp"
> "select Severity from tempdb..EventsArchive" queryout
> c:\aaabbbcc.txt" -c -t"," -C -Usa -P1210'
> exec master..xp_cmdshell @.statement
> Thanks,
> Avi
>
>
>|||Hi Avi,
You can also use it the following way;
SET @.conn = '" -U <username> -P <pwd> -c'
SET @.filename = '\\servername\foldername\test'+'.txt')
SET @.sql1 = 'BCP "SELECT * FROM <table name>)" QUERYOUT "'
SET @.sql1 = @.sql1 + @.filename + @.conn
EXEC master..xp_cmdshell @.sql1
You don't have to give full path of BCP utility.
--
Thanks
Yogish

BCP

Hi,
I am not able to export the result of sys.assemblies using BCP. The output
file is created but it is empty (the result of SELECT in SSMS is not empty).
BCP displays a message indicating that 0 rows were exported. It seems there
is no error!
Thanks,
LeilaLeila (Leilas@.hotpop.com) writes:
> I am not able to export the result of sys.assemblies using BCP. The
> output file is created but it is empty (the result of SELECT in SSMS is
> not empty). BCP displays a message indicating that 0 rows were exported.
> It seems there is no error!
Did you specify the database? I tried this on my machine:
bcp slasketti.sys.assemblies out e:\temp\slask.bcp -T -c -S .\NELJ
slasketti is my database, and .\NELJ is an instance that runs SQL 2005.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland,
Yes I did :(
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97252351C54Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
> Did you specify the database? I tried this on my machine:
> bcp slasketti.sys.assemblies out e:\temp\slask.bcp -T -c -S .\NELJ
> slasketti is my database, and .\NELJ is an instance that runs SQL 2005.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Batch insert

I'm using ASP.net to do a Select and I want to insert all the results into a table that is stored locally
I can put an SP local but cant put on the other DB
How would i achieve this batch insert? is it possible?
thanks
MarkI am confused:confused: What is it that you need?

If you're talking about being able to do BULK INSERT from a stored procedure, you can do that, but you'll have to grant the User ID that will execute it Bulk Insert Administrator server role (bulkadmin.)|||Sorry
I have 2 tables
table 1 - remote, cant use an SP
table 2 - local, can use a SP

I do a select on Table1 and it returns the results,

I want to insert all these results into table 2

whats the best way to do that? (most efficient)

Cheers
Mark|||The problem is that the first table is really slow to access, I have a string of booking Id's i need to pass it and it keeps timing out...its the DB! its on rubbish HW and is badly designed but I cant do anything about that so I have to use the most efficent method of getting the data out, or simply copying the required records between the 2 tables

remember the rubbish DB is not local and I only have read access to it!

whats the most efficent method I should use?

thanks
Mark|||Create a stored procedure on the remote server that retrieves rows via SELECT, and call it instead of doing SELECT from your local server.

Batch Insert

I am using a following statement in a sproc
insert into destination_table
select col1,col2,col3,col4 from source_table
For a certain row in the source_table the above insert
fails.
But its out of some 350 total records in source_table
How can I find which specific row/s is failing the
insert ?
ThanxPlease post the DDL of both tables plus the error message.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Anup B" <anonymous@.discussions.microsoft.com> wrote in message
news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
I am using a following statement in a sproc
insert into destination_table
select col1,col2,col3,col4 from source_table
For a certain row in the source_table the above insert
fails.
But its out of some 350 total records in source_table
How can I find which specific row/s is failing the
insert ?
Thanx|||Thanks for your help.
INSERT t_pick_detail ( order_number, line_number, type,
uom, work_type, status,item_number, lot_number,
unplanned_quantity,planned_quantity, pick_location,
picking_flow, staging_location,zone, wave_id,
load_id, load_sequence, stop_id, pick_area, wh_id)
SELECT orm.order_number, ord.line_number, 'PP', NULL,
NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
ord.host_wave_id, orm.load_id, orm.load_seq,
NULL, NULL, ord.wh_id
FROM t_order orm
JOIN t_order_detail ord
ON (orm.order_number = ord.order_number AND
orm.wh_id = ord.wh_id)
AND orm.status like @.in_status
AND ord.wh_id = @.in_WHID
t_order definition
--
wh_id varchar no 10
order_number varchar no 30
store_order_number varchar no 40
type_id int no 4
customer_id char no 15
cust_po_number varchar no 30
customer_name varchar no 100
customer_phone varchar no 30
customer_fax varchar no 30
customer_email varchar no 50
department char no 10
load_id varchar no 30
load_seq int no 4
bol_number char no 10
pro_number varchar no 20
master_bol_number char no 10
carrier varchar no 30
carrier_scac varchar no 4
freight_terms varchar no 10
rush char no 5
priority varchar no 3
order_date datetime no 8
arrive_date datetime no 8
actual_arrival_date datetime no 8
date_picked datetime no 8
date_expected datetime no 8
promised_date datetime no 8
weight float no 8
cubic_volume float no 8
containers int no 4
backorder char no 1
pre_paid char no 10
cod_amount float no 8
insurance_amount float no 8
pip_amount float no 8
freight_cost float no 8
region varchar no 5
bill_to_code char no 15
bill_to_name varchar no 30
bill_to_addr1 varchar no 30
bill_to_addr2 varchar no 30
bill_to_addr3 varchar no 30
bill_to_city varchar no 30
bill_to_state varchar no 3
bill_to_zip varchar no 12
bill_to_country_code char no 5
bill_to_country_name varchar no 30
bill_to_phone varchar no 30
ship_to_code char no 15
ship_to_name varchar no 30
ship_to_addr1 varchar no 30
ship_to_addr2 varchar no 30
ship_to_addr3 varchar no 30
ship_to_city varchar no 30
ship_to_state varchar no 3
ship_to_zip varchar no 12
ship_to_country_code char no 5
ship_to_country_name varchar no 30
ship_to_phone varchar no 30
delivery_name varchar no 30
delivery_addr1 varchar no 30
delivery_addr2 varchar no 30
delivery_addr3 varchar no 30
delivery_city varchar no 30
delivery_state varchar no 3
delivery_zip varchar no 12
delivery_country_code char no 5
delivery_country_name varchar no 30
delivery_phone varchar no 30
bill_frght_to_code char no 15
bill_frght_to_name varchar no 30
bill_frght_to_addr1 varchar no 30
bill_frght_to_addr2 varchar no 30
bill_frght_to_addr3 varchar no 30
bill_frght_to_city varchar no 30
bill_frght_to_state varchar no 3
bill_frght_to_zip varchar no 12
bill_frght_to_country_code char no 5
bill_frght_to_country_name varchar no 30
bill_frght_to_phone varchar no 30
return_to_code char no 30
return_to_name varchar no 30
return_to_addr1 varchar no 30
return_to_addr2 varchar no 30
return_to_addr3 varchar no 30
return_to_city varchar no 30
return_to_state varchar no 3
return_to_zip varchar no 12
return_to_country_code char no 5
return_to_country_name varchar no 30
return_to_phone varchar no 30
rma_number varchar no 40
rma_expiration_date datetime no 8
carton_label varchar no 10
ver_flag char no 4
full_pallets int no 4
haz_flag char no 10
order_wgt float no 8
status varchar no 20
zone varchar no 10
drop_ship char no 1
lock_flag varchar no 10
partial_order_flag char no 1
earliest_ship_date datetime no 8
latest_ship_date datetime no 8
actual_ship_date datetime no 8
earliest_delivery_date datetime no 8
latest_delivery_date datetime no 8
actual_delivery_date datetime no 8
route varchar no 30
order_amount float no 8
pick_type char no 1
invoiced_amount float no 8
t_pick_detail definition
--
pick_id int no 4
order_number varchar no 20
line_number varchar no 5
type char no 2
uom varchar no 10
work_q_id varchar no 30
work_type varchar no 2
label_number varchar no 22
status varchar no 10
item_number varchar no 30
lot_number varchar no 15
serial_number varchar no 30
unplanned_quantity float no 8
planned_quantity float no 8
picked_quantity float no 8
staged_quantity float no 8
loaded_quantity float no 8
pick_location varchar no 10
picking_flow varchar no 10
staging_location varchar no 10
zone varchar no 20
wave_id varchar no 20
load_id varchar no 30
load_sequence int no 4
stop_id varchar no 20
container_id varchar no 22
pick_category varchar no 10
user_assigned varchar no 10
bulk_pick_flag char no 1
stacking_sequence int no 4
pick_area varchar no 10
wh_id varchar no 10
requested_quantity float no 8
request_returned_qty float no 8
>--Original Message--
>Please post the DDL of both tables plus the error
message.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>I am using a following statement in a sproc
>insert into destination_table
>select col1,col2,col3,col4 from source_table
>For a certain row in the source_table the above insert
>fails.
>But its out of some 350 total records in source_table
>How can I find which specific row/s is failing the
>insert ?
>Thanx
>.
>|||1) You left out the definition for t_order_detail.
2) You did not give actual DDL - i.e. CREATE TABLE statements.
3) You did not post the error message.
4) Of the tables you did present, you have mismatched datatypes, which
may be the source of your problem.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Anup B" <anonymous@.discussions.microsoft.com> wrote in message
news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
Thanks for your help.
INSERT t_pick_detail ( order_number, line_number, type,
uom, work_type, status,item_number, lot_number,
unplanned_quantity,planned_quantity, pick_location,
picking_flow, staging_location,zone, wave_id,
load_id, load_sequence, stop_id, pick_area, wh_id)
SELECT orm.order_number, ord.line_number, 'PP', NULL,
NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
ord.host_wave_id, orm.load_id, orm.load_seq,
NULL, NULL, ord.wh_id
FROM t_order orm
JOIN t_order_detail ord
ON (orm.order_number = ord.order_number AND
orm.wh_id = ord.wh_id)
AND orm.status like @.in_status
AND ord.wh_id = @.in_WHID
t_order definition
--
wh_id varchar no 10
order_number varchar no 30
store_order_number varchar no 40
type_id int no 4
customer_id char no 15
cust_po_number varchar no 30
customer_name varchar no 100
customer_phone varchar no 30
customer_fax varchar no 30
customer_email varchar no 50
department char no 10
load_id varchar no 30
load_seq int no 4
bol_number char no 10
pro_number varchar no 20
master_bol_number char no 10
carrier varchar no 30
carrier_scac varchar no 4
freight_terms varchar no 10
rush char no 5
priority varchar no 3
order_date datetime no 8
arrive_date datetime no 8
actual_arrival_date datetime no 8
date_picked datetime no 8
date_expected datetime no 8
promised_date datetime no 8
weight float no 8
cubic_volume float no 8
containers int no 4
backorder char no 1
pre_paid char no 10
cod_amount float no 8
insurance_amount float no 8
pip_amount float no 8
freight_cost float no 8
region varchar no 5
bill_to_code char no 15
bill_to_name varchar no 30
bill_to_addr1 varchar no 30
bill_to_addr2 varchar no 30
bill_to_addr3 varchar no 30
bill_to_city varchar no 30
bill_to_state varchar no 3
bill_to_zip varchar no 12
bill_to_country_code char no 5
bill_to_country_name varchar no 30
bill_to_phone varchar no 30
ship_to_code char no 15
ship_to_name varchar no 30
ship_to_addr1 varchar no 30
ship_to_addr2 varchar no 30
ship_to_addr3 varchar no 30
ship_to_city varchar no 30
ship_to_state varchar no 3
ship_to_zip varchar no 12
ship_to_country_code char no 5
ship_to_country_name varchar no 30
ship_to_phone varchar no 30
delivery_name varchar no 30
delivery_addr1 varchar no 30
delivery_addr2 varchar no 30
delivery_addr3 varchar no 30
delivery_city varchar no 30
delivery_state varchar no 3
delivery_zip varchar no 12
delivery_country_code char no 5
delivery_country_name varchar no 30
delivery_phone varchar no 30
bill_frght_to_code char no 15
bill_frght_to_name varchar no 30
bill_frght_to_addr1 varchar no 30
bill_frght_to_addr2 varchar no 30
bill_frght_to_addr3 varchar no 30
bill_frght_to_city varchar no 30
bill_frght_to_state varchar no 3
bill_frght_to_zip varchar no 12
bill_frght_to_country_code char no 5
bill_frght_to_country_name varchar no 30
bill_frght_to_phone varchar no 30
return_to_code char no 30
return_to_name varchar no 30
return_to_addr1 varchar no 30
return_to_addr2 varchar no 30
return_to_addr3 varchar no 30
return_to_city varchar no 30
return_to_state varchar no 3
return_to_zip varchar no 12
return_to_country_code char no 5
return_to_country_name varchar no 30
return_to_phone varchar no 30
rma_number varchar no 40
rma_expiration_date datetime no 8
carton_label varchar no 10
ver_flag char no 4
full_pallets int no 4
haz_flag char no 10
order_wgt float no 8
status varchar no 20
zone varchar no 10
drop_ship char no 1
lock_flag varchar no 10
partial_order_flag char no 1
earliest_ship_date datetime no 8
latest_ship_date datetime no 8
actual_ship_date datetime no 8
earliest_delivery_date datetime no 8
latest_delivery_date datetime no 8
actual_delivery_date datetime no 8
route varchar no 30
order_amount float no 8
pick_type char no 1
invoiced_amount float no 8
t_pick_detail definition
--
pick_id int no 4
order_number varchar no 20
line_number varchar no 5
type char no 2
uom varchar no 10
work_q_id varchar no 30
work_type varchar no 2
label_number varchar no 22
status varchar no 10
item_number varchar no 30
lot_number varchar no 15
serial_number varchar no 30
unplanned_quantity float no 8
planned_quantity float no 8
picked_quantity float no 8
staged_quantity float no 8
loaded_quantity float no 8
pick_location varchar no 10
picking_flow varchar no 10
staging_location varchar no 10
zone varchar no 20
wave_id varchar no 20
load_id varchar no 30
load_sequence int no 4
stop_id varchar no 20
container_id varchar no 22
pick_category varchar no 10
user_assigned varchar no 10
bulk_pick_flag char no 1
stacking_sequence int no 4
pick_area varchar no 10
wh_id varchar no 10
requested_quantity float no 8
request_returned_qty float no 8
>--Original Message--
>Please post the DDL of both tables plus the error
message.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>I am using a following statement in a sproc
>insert into destination_table
>select col1,col2,col3,col4 from source_table
>For a certain row in the source_table the above insert
>fails.
>But its out of some 350 total records in source_table
>How can I find which specific row/s is failing the
>insert ?
>Thanx
>.
>|||Thanks for looking at it , Tom
I have suggested the folks to use a loop method to insert
rather than the current method.
Thanks again.
>--Original Message--
>1) You left out the definition for t_order_detail.
>2) You did not give actual DDL - i.e. CREATE TABLE
statements.
>3) You did not post the error message.
>4) Of the tables you did present, you have mismatched
datatypes, which
>may be the source of your problem.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
>Thanks for your help.
>
>INSERT t_pick_detail ( order_number, line_number, type,
>uom, work_type, status,item_number, lot_number,
>unplanned_quantity,planned_quantity, pick_location,
>picking_flow, staging_location,zone, wave_id,
>load_id, load_sequence, stop_id, pick_area, wh_id)
>SELECT orm.order_number, ord.line_number, 'PP', NULL,
>NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
>(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
>ord.host_wave_id, orm.load_id, orm.load_seq,
>NULL, NULL, ord.wh_id
>FROM t_order orm
> JOIN t_order_detail ord
> ON (orm.order_number = ord.order_number AND
> orm.wh_id = ord.wh_id)
>AND orm.status like @.in_status
>AND ord.wh_id = @.in_WHID
>t_order definition
>--
>wh_id varchar no 10
>order_number varchar no 30
>store_order_number varchar no 40
>type_id int no 4
>customer_id char no 15
>cust_po_number varchar no 30
>customer_name varchar no 100
>customer_phone varchar no 30
>customer_fax varchar no 30
>customer_email varchar no 50
>department char no 10
>load_id varchar no 30
>load_seq int no 4
>bol_number char no 10
>pro_number varchar no 20
>master_bol_number char no 10
>carrier varchar no 30
>carrier_scac varchar no 4
>freight_terms varchar no 10
>rush char no 5
>priority varchar no 3
>order_date datetime no 8
>arrive_date datetime no 8
>actual_arrival_date datetime no 8
>date_picked datetime no 8
>date_expected datetime no 8
>promised_date datetime no 8
>weight float no 8
>cubic_volume float no 8
>containers int no 4
>backorder char no 1
>pre_paid char no 10
>cod_amount float no 8
>insurance_amount float no 8
>pip_amount float no 8
>freight_cost float no 8
>region varchar no 5
>bill_to_code char no 15
>bill_to_name varchar no 30
>bill_to_addr1 varchar no 30
>bill_to_addr2 varchar no 30
>bill_to_addr3 varchar no 30
>bill_to_city varchar no 30
>bill_to_state varchar no 3
>bill_to_zip varchar no 12
>bill_to_country_code char no 5
>bill_to_country_name varchar no 30
>bill_to_phone varchar no 30
>ship_to_code char no 15
>ship_to_name varchar no 30
>ship_to_addr1 varchar no 30
>ship_to_addr2 varchar no 30
>ship_to_addr3 varchar no 30
>ship_to_city varchar no 30
>ship_to_state varchar no 3
>ship_to_zip varchar no 12
>ship_to_country_code char no 5
>ship_to_country_name varchar no 30
>ship_to_phone varchar no 30
>delivery_name varchar no 30
>delivery_addr1 varchar no 30
>delivery_addr2 varchar no 30
>delivery_addr3 varchar no 30
>delivery_city varchar no 30
>delivery_state varchar no 3
>delivery_zip varchar no 12
>delivery_country_code char no 5
>delivery_country_name varchar no 30
>delivery_phone varchar no 30
>bill_frght_to_code char no 15
>bill_frght_to_name varchar no 30
>bill_frght_to_addr1 varchar no 30
>bill_frght_to_addr2 varchar no 30
>bill_frght_to_addr3 varchar no 30
>bill_frght_to_city varchar no 30
>bill_frght_to_state varchar no 3
>bill_frght_to_zip varchar no 12
>bill_frght_to_country_code char no 5
>bill_frght_to_country_name varchar no 30
>bill_frght_to_phone varchar no 30
>return_to_code char no 30
>return_to_name varchar no 30
>return_to_addr1 varchar no 30
>return_to_addr2 varchar no 30
>return_to_addr3 varchar no 30
>return_to_city varchar no 30
>return_to_state varchar no 3
>return_to_zip varchar no 12
>return_to_country_code char no 5
>return_to_country_name varchar no 30
>return_to_phone varchar no 30
>rma_number varchar no 40
>rma_expiration_date datetime no 8
>carton_label varchar no 10
>ver_flag char no 4
>full_pallets int no 4
>haz_flag char no 10
>order_wgt float no 8
>status varchar no 20
>zone varchar no 10
>drop_ship char no 1
>lock_flag varchar no 10
>partial_order_flag char no 1
>earliest_ship_date datetime no 8
>latest_ship_date datetime no 8
>actual_ship_date datetime no 8
>earliest_delivery_date datetime no 8
>latest_delivery_date datetime no 8
>actual_delivery_date datetime no 8
>route varchar no 30
>order_amount float no 8
>pick_type char no 1
>invoiced_amount float no 8
>
>t_pick_detail definition
>--
>
>pick_id int no 4
>order_number varchar no 20
>line_number varchar no 5
>type char no 2
>uom varchar no 10
>work_q_id varchar no 30
>work_type varchar no 2
>label_number varchar no 22
>status varchar no 10
>item_number varchar no 30
>lot_number varchar no 15
>serial_number varchar no 30
>unplanned_quantity float no 8
>planned_quantity float no 8
>picked_quantity float no 8
>staged_quantity float no 8
>loaded_quantity float no 8
>pick_location varchar no 10
>picking_flow varchar no 10
>staging_location varchar no 10
>zone varchar no 20
>wave_id varchar no 20
>load_id varchar no 30
>load_sequence int no 4
>stop_id varchar no 20
>container_id varchar no 22
>pick_category varchar no 10
>user_assigned varchar no 10
>bulk_pick_flag char no 1
>stacking_sequence int no 4
>pick_area varchar no 10
>wh_id varchar no 10
>requested_quantity float no 8
>request_returned_qty float no 8
>>--Original Message--
>>Please post the DDL of both tables plus the error
>message.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com
>>
>>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>>I am using a following statement in a sproc
>>insert into destination_table
>>select col1,col2,col3,col4 from source_table
>>For a certain row in the source_table the above insert
>>fails.
>>But its out of some 350 total records in source_table
>>How can I find which specific row/s is failing the
>>insert ?
>>Thanx
>>.
>.
>|||Reconsider your approach. It will take _much_ longer. It will be much
faster if you simply post the DDL and the error message.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
<anonymous@.discussions.microsoft.com> wrote in message
news:0a5501c4e39e$f909e650$a401280a@.phx.gbl...
Thanks for looking at it , Tom
I have suggested the folks to use a loop method to insert
rather than the current method.
Thanks again.
>--Original Message--
>1) You left out the definition for t_order_detail.
>2) You did not give actual DDL - i.e. CREATE TABLE
statements.
>3) You did not post the error message.
>4) Of the tables you did present, you have mismatched
datatypes, which
>may be the source of your problem.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e8401c4e38e$f1bad130$a601280a@.phx.gbl...
>Thanks for your help.
>
>INSERT t_pick_detail ( order_number, line_number, type,
>uom, work_type, status,item_number, lot_number,
>unplanned_quantity,planned_quantity, pick_location,
>picking_flow, staging_location,zone, wave_id,
>load_id, load_sequence, stop_id, pick_area, wh_id)
>SELECT orm.order_number, ord.line_number, 'PP', NULL,
>NULL, 'UNPLANNED',item_number, lot_number, (qty - ISNULL
>(pkd_planned,0)),0, NULL, 0 , NULL,NULL,
>ord.host_wave_id, orm.load_id, orm.load_seq,
>NULL, NULL, ord.wh_id
>FROM t_order orm
> JOIN t_order_detail ord
> ON (orm.order_number = ord.order_number AND
> orm.wh_id = ord.wh_id)
>AND orm.status like @.in_status
>AND ord.wh_id = @.in_WHID
>t_order definition
>--
>wh_id varchar no 10
>order_number varchar no 30
>store_order_number varchar no 40
>type_id int no 4
>customer_id char no 15
>cust_po_number varchar no 30
>customer_name varchar no 100
>customer_phone varchar no 30
>customer_fax varchar no 30
>customer_email varchar no 50
>department char no 10
>load_id varchar no 30
>load_seq int no 4
>bol_number char no 10
>pro_number varchar no 20
>master_bol_number char no 10
>carrier varchar no 30
>carrier_scac varchar no 4
>freight_terms varchar no 10
>rush char no 5
>priority varchar no 3
>order_date datetime no 8
>arrive_date datetime no 8
>actual_arrival_date datetime no 8
>date_picked datetime no 8
>date_expected datetime no 8
>promised_date datetime no 8
>weight float no 8
>cubic_volume float no 8
>containers int no 4
>backorder char no 1
>pre_paid char no 10
>cod_amount float no 8
>insurance_amount float no 8
>pip_amount float no 8
>freight_cost float no 8
>region varchar no 5
>bill_to_code char no 15
>bill_to_name varchar no 30
>bill_to_addr1 varchar no 30
>bill_to_addr2 varchar no 30
>bill_to_addr3 varchar no 30
>bill_to_city varchar no 30
>bill_to_state varchar no 3
>bill_to_zip varchar no 12
>bill_to_country_code char no 5
>bill_to_country_name varchar no 30
>bill_to_phone varchar no 30
>ship_to_code char no 15
>ship_to_name varchar no 30
>ship_to_addr1 varchar no 30
>ship_to_addr2 varchar no 30
>ship_to_addr3 varchar no 30
>ship_to_city varchar no 30
>ship_to_state varchar no 3
>ship_to_zip varchar no 12
>ship_to_country_code char no 5
>ship_to_country_name varchar no 30
>ship_to_phone varchar no 30
>delivery_name varchar no 30
>delivery_addr1 varchar no 30
>delivery_addr2 varchar no 30
>delivery_addr3 varchar no 30
>delivery_city varchar no 30
>delivery_state varchar no 3
>delivery_zip varchar no 12
>delivery_country_code char no 5
>delivery_country_name varchar no 30
>delivery_phone varchar no 30
>bill_frght_to_code char no 15
>bill_frght_to_name varchar no 30
>bill_frght_to_addr1 varchar no 30
>bill_frght_to_addr2 varchar no 30
>bill_frght_to_addr3 varchar no 30
>bill_frght_to_city varchar no 30
>bill_frght_to_state varchar no 3
>bill_frght_to_zip varchar no 12
>bill_frght_to_country_code char no 5
>bill_frght_to_country_name varchar no 30
>bill_frght_to_phone varchar no 30
>return_to_code char no 30
>return_to_name varchar no 30
>return_to_addr1 varchar no 30
>return_to_addr2 varchar no 30
>return_to_addr3 varchar no 30
>return_to_city varchar no 30
>return_to_state varchar no 3
>return_to_zip varchar no 12
>return_to_country_code char no 5
>return_to_country_name varchar no 30
>return_to_phone varchar no 30
>rma_number varchar no 40
>rma_expiration_date datetime no 8
>carton_label varchar no 10
>ver_flag char no 4
>full_pallets int no 4
>haz_flag char no 10
>order_wgt float no 8
>status varchar no 20
>zone varchar no 10
>drop_ship char no 1
>lock_flag varchar no 10
>partial_order_flag char no 1
>earliest_ship_date datetime no 8
>latest_ship_date datetime no 8
>actual_ship_date datetime no 8
>earliest_delivery_date datetime no 8
>latest_delivery_date datetime no 8
>actual_delivery_date datetime no 8
>route varchar no 30
>order_amount float no 8
>pick_type char no 1
>invoiced_amount float no 8
>
>t_pick_detail definition
>--
>
>pick_id int no 4
>order_number varchar no 20
>line_number varchar no 5
>type char no 2
>uom varchar no 10
>work_q_id varchar no 30
>work_type varchar no 2
>label_number varchar no 22
>status varchar no 10
>item_number varchar no 30
>lot_number varchar no 15
>serial_number varchar no 30
>unplanned_quantity float no 8
>planned_quantity float no 8
>picked_quantity float no 8
>staged_quantity float no 8
>loaded_quantity float no 8
>pick_location varchar no 10
>picking_flow varchar no 10
>staging_location varchar no 10
>zone varchar no 20
>wave_id varchar no 20
>load_id varchar no 30
>load_sequence int no 4
>stop_id varchar no 20
>container_id varchar no 22
>pick_category varchar no 10
>user_assigned varchar no 10
>bulk_pick_flag char no 1
>stacking_sequence int no 4
>pick_area varchar no 10
>wh_id varchar no 10
>requested_quantity float no 8
>request_returned_qty float no 8
>>--Original Message--
>>Please post the DDL of both tables plus the error
>message.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com
>>
>>"Anup B" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:0e7301c4e38a$f40be9f0$a601280a@.phx.gbl...
>>I am using a following statement in a sproc
>>insert into destination_table
>>select col1,col2,col3,col4 from source_table
>>For a certain row in the source_table the above insert
>>fails.
>>But its out of some 350 total records in source_table
>>How can I find which specific row/s is failing the
>>insert ?
>>Thanx
>>.
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Thanks for looking at it , Tom
> I have suggested the folks to use a loop method to insert
> rather than the current method.
> Thanks again.
>
Are you the OP? If so, are you suggesting that a looping construct
inserting a row at a time will be faster than a single insert statement?
Before commiting to that design, some testing is in order.
--
David Gugick
Imceda Software
www.imceda.com

batch file help

I wish to have a batch file(test.bat) that does an OSQL as below. The OSQL
statement below needs some modification as well.
OSQL -E -S%1 -Q"select @.@.servername" -oc:\xyz\%1output.txt
When i enter test.bat, i want it to prompt for servername so i can add it at
the prompt
Also i would like the prompted servername to be filled in %1 parameter in
the OSQL . Can this be done ?Hi Hassan,
This is not the right place to ask any batch file related questions.
Anyway try this ....
You can specify server name in the command propmt. If you haven't specify
the server name in the command prompt it will prompt for it.
Regards,
Suhanthan, V.
suhan@.jhc.lk
----
@.ECHO OFF
IF NOT _%1_ == __ GOTO WITHPARAM
GOTO WITHOUTPARAM
:WITHPARAM
OSQL -E -S%1 -Q"select @.@.servername" -oc:\xyz\%1output.txt
GOTO EXITTHIS
:WITHOUTPARAM
SET /P SERVER="Enter SQL server name :"
OSQL -E -S%SERVER% -Q"select @.@.servername" -oc:\xyz\%1output.txt
SET SERVER=
:EXITTHIS
----
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O2$uiVRnDHA.1084@.tk2msftngp13.phx.gbl...
> I wish to have a batch file(test.bat) that does an OSQL as below. The OSQL
> statement below needs some modification as well.
> OSQL -E -S%1 -Q"select @.@.servername" -oc:\xyz\%1output.txt
> When i enter test.bat, i want it to prompt for servername so i can add it
at
> the prompt
> Also i would like the prompted servername to be filled in %1 parameter in
> the OSQL . Can this be done ?
>
>

Batch execution

Hello All,
I have a table TableX with one column SQL varchar(8000). This column
contains about 50 records (rows) like
insert into tablename1 select datetime,...From Tbl1 Where...
insert into tablename2 select datetime,...From Tbl2 Where...
insert into tablename3 select datetime,...From Tbl3 Where...
...
My question is, is there any way to run these dml statements from tableX all
at one time as a batch rather than looping through TableX and executing them
one by one.
Thanks
RickyHere goes:
set nocount on;
use tempdb;
go
drop table t1, tablex;
go
create table t1(col1 int);
go
create table tablex(sql varchar(8000) not null);
insert into tablex values('insert into t1 values(1);');
insert into tablex values('insert into t1 values(2);');
insert into tablex values('insert into t1 values(3);');
go
exec master..xp_execresultset
N'select sql from tablex;',
N'tempdb'
go
select * from t1;
-- Output:
col1
--
1
2
3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:667B6820-D2A8-451C-9BDD-38D763298CD2@.microsoft.com...
> Hello All,
> I have a table TableX with one column SQL varchar(8000). This column
> contains about 50 records (rows) like
> insert into tablename1 select datetime,...From Tbl1 Where...
> insert into tablename2 select datetime,...From Tbl2 Where...
> insert into tablename3 select datetime,...From Tbl3 Where...
> ...
> My question is, is there any way to run these dml statements from tableX
> all
> at one time as a batch rather than looping through TableX and executing
> them
> one by one.
> Thanks
> Ricky|||Thank You Itzik Ben-Gan.
"Brilliant as always...!!"
"Itzik Ben-Gan" wrote:

> Here goes:
> set nocount on;
> use tempdb;
> go
> drop table t1, tablex;
> go
> create table t1(col1 int);
> go
> create table tablex(sql varchar(8000) not null);
> insert into tablex values('insert into t1 values(1);');
> insert into tablex values('insert into t1 values(2);');
> insert into tablex values('insert into t1 values(3);');
> go
> exec master..xp_execresultset
> N'select sql from tablex;',
> N'tempdb'
> go
> select * from t1;
> -- Output:
> col1
> --
> 1
> 2
> 3
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rick" <ricky.arora@.metc.state.mn.us> wrote in message
> news:667B6820-D2A8-451C-9BDD-38D763298CD2@.microsoft.com...
>
>|||Anybody know how to get the value of an output parameter from a proc
executed with xp_ExecuteResultSet?
For Example:
set nocount on
use tempdb
go
create table T (t text default '')
go
create proc P (@.T1 text, @.Message varchar(255) Out)
as
begin
select @.T1
set @.Message = 'Your error goes here'
end
go
DECLARE @.SQL nvarchar(4000)
DECLARE @.ErrorMessage nvarchar(4000)
SET @.SQL = 'exec p ''This is a test.'', @.ErrorMessage'
exec master..xp_execresultset @.SQL, N'tempdb'
select @.ErrorMessage
go
drop table T
drop proc P
Message posted via http://www.webservertalk.com|||--It would go something like this.
set nocount on
use tempdb
go
Create Table ##ErrorMessage (F1 [varchar] (8000))
go
create proc P (@.T1 nvarchar(4000), @.Message varchar(255) Out)
as
begin
select
T1 = @.T1,
Message = @.Message
set @.Message = 'Your error goes here'
end
go
DECLARE @.SQL nvarchar(4000)
SET @.SQL = 'EXECUTE(''' + char(13) + char(10) +
'DECLARE @.ErrorMessage nvarchar(4000) ' + char(13) + char(10) +
'exec p ''''This is a test.'''', @.ErrorMessage OUT' + char(13) + char(10)
+
'insert ##ErrorMessage values(@.ErrorMessage)' + char(13) + char(10) +
''')'
--exec sp_executesql @.SQL
SET @.SQL = 'SELECT ''' + Replace(@.SQL, '''', ''') + ''''
exec master..xp_execresultset @.SQL, N'tempdb'
select Message = F1 from ##ErrorMessage
go
drop table ##ErrorMessage
drop proc P
Message posted via http://www.webservertalk.com

Basic SQL: Multiple AND/OR nightmare in SELECT statement

This is more of a basic SQL question rather than anything specific for T-SQL, but I assume someone here can help me...

I have a problem with properly combining a lot of AND and ORs in a SELECT statement in a stored procedure in order to get the desired results. The problem is that I want to have all results that fullfill all of the supplied conditions: InstitutionCode, CollectionCode, ScientificName, Locality (unless they are null, hence 'coalesce') and the Parentid, that can be in one of eight columns.

SELECT *
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' AND
ParentID1 = COALESCE(@.taxparent, ParentID3) OR
ParentID2 = COALESCE(@.taxparent, ParentID2) OR
ParentID3 = COALESCE(@.taxparent, ParentID3) OR
ParentID4 = COALESCE(@.taxparent, ParentID4) OR
ParentID5 = COALESCE(@.taxparent, ParentID5) OR
ParentID6 = COALESCE(@.taxparent, ParentID6) OR
ParentID7 = COALESCE(@.taxparent, ParentID7) OR
ParentID8 = COALESCE(@.taxparent, ParentID8)

The current construction, however, gives me all results that fullfill either on of the four conditions, or the parentid in one of the columns. putting parentheses around parentid part gives me zero query results. I understand that the ORs should be restricted to the parentids and not the rest, but putting parentheses around parentid part gives me zero query results.

Has anyone got a good tip to help me resolve this puzzle?

Hi

You can also use IN and NOT IN as well as Having caluse to verify condition. As ( and ) paranthesis can also help you to verify condition on a condition,

|||Hi Akbar,

Sorry, but that is not really helpful.

As far as I understood, IN is used to test multiple values against a single column. I am testing a single value against multiple columns.

HAVING is used with aggregate values. I am not using those.

As I already wrote, using parenthesis does not work for me, or I do not know how to properly apply them in this particular case.

I am still with my hands in my hair on finding a solution to this, so I would appreciate any help.|||All right, fair enough, after some study, I was able to simplify the code using 'IN', that -new to me- could also be used for testing a single value against multiple columns. But I am stuck with the same problem that it won't combine with the rest of the conditions in order to yield the desired results!

SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' OR
@.taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8)

Gives me too many results and

SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%' AND
@.taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8)

Gives me no results....

|||OK, another discovery! When I comment out:

ScientificName LIKE '%' + @.binomen + '%' AND
Locality LIKE '%' + @.locality + '%'

It does actually succesfully combine the different criteria!

This must mean something goes wrong with those lines only...

Here is the entire code of the stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [petrander].[DynamicQuery]
@.taxparent int = NULL,
@.museum int = NULL,
@.collection int = NULL,
@.binomen Nvarchar(254) = NULL,
@.locality Nvarchar(254) = NULL
AS
SELECT ID, SpecimenNr, ScientificName, Locality, Taxon
FROM QueryView
WHERE
InstitutionCode = COALESCE(@.museum, InstitutionCode) AND
CollectionCode = COALESCE(@.collection, CollectionCode) AND
ScientificName LIKE 'N%' + @.binomen + '%' AND
Locality LIKE 'N%' + @.locality + '%' AND
@.taxparent IN (ParentID1,
ParentID2,
ParentID3,
ParentID4,
ParentID5,
ParentID6,
ParentID7,
ParentID8)

Could the problem lie in combining null values with the LIKE 'N%' + statements?|||Problem lay somewhere else and solution can be seen in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=620363&SiteID=1

Basic SQL question...

Hello, folks.

I have the following query that I run that pulls up the count of the number of orders of a product.

SELECT Product, Count(Product) AS Total
FROM Orders_View
GROUP BY Product, Year([DateOpened]), Month([DateOpened])
HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
ORDER BY Product;

This query is fine but the COUNT() function only coughs-up the non-zero results. How can I get this query to cough-up the zero counts as well for all products listed in the Product table?

TIA...Count(Product) will count non-null columns. To count null columns
either use ISNULL function or count(*).

SELECT Product, Count(isnull(Product,0)) AS Total
FROM Orders_View
GROUP BY Product, Year([DateOpened]), Month([DateOpened])
HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
ORDER BY Product;

SELECT Product, Count(*) AS Total
FROM Orders_View
GROUP BY Product, Year([DateOpened]), Month([DateOpened])
HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
ORDER BY Product;|||Blue Streak wrote:
> Hello, folks.
> I have the following query that I run that pulls up the count of the
number of orders of a product.
> SELECT Product, Count(Product) AS Total
> FROM Orders_View
> GROUP BY Product, Year([DateOpened]), Month([DateOpened])
> HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
> ORDER BY Product;
> This query is fine but the COUNT() function only coughs-up the
non-zero results. How can I get this query to cough-up the zero counts
as well for all products listed in the Product table?
> TIA...

SELECT Product,
(SELECT COUNT(*)
FROM Orders_View
WHERE Orders_View.Product = Product.Product
AND Year([DateOpened])=2004
AND Month([DateOpened])=10) AS ProductSales
FROM Product

--
David Rowland
http://dbmonitor.tripod.com|||Blue Streak (anonymous@.msn.com) writes:
> I have the following query that I run that pulls up the count of the
> number of orders of a product.
> SELECT Product, Count(Product) AS Total
> FROM Orders_View
> GROUP BY Product, Year([DateOpened]), Month([DateOpened])
> HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
> ORDER BY Product;
> This query is fine but the COUNT() function only coughs-up the non-zero
> results. How can I get this query to cough-up the zero counts as well
> for all products listed in the Product table?

It is always adviceable for this type of question to post:

1) CREATE TABLE statement for your tables (and in this case also the view)
2) INSERT statements with sample data.
3) The desired output given the sample data.

That permits anyone answering your question to easily cut and paste into
to Query Analyzer and develop a tested solution.

The solution suggested by David Rowland should give you the desired
result, but it may not be that performant.

An alternative is to explorr GROUP BY ALL, but with out the table and
view definitions, I can't test.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You might want work on better names for data elements. A name should
tell us what the data element is, and not how it is stored (i.e. VIEW);
it ought to tell us the attribute involved (product what? Weight? Size?
Id?)

Since you did not bother with DDL, I am going to assume that there is
Products table somewhere and that this will work.

SELECT P.product_id, COUNT(O.product_id) AS total
FROM Products AS P
LEFT OUTER JOIN
Orders AS O
ON P.product_id = O.product_id
WHERE O.date_opened BETWEEN '2004-10-01 00:00:00.000'
AND '2004-10-31 23:59:59.99'
GROUP BY I.product_id;

Hint about temporal data: do not split it up into pieces to do numeric
or string operations on the conversions. Think of it as a data type in
its own right, with its own operators. This is not just a matter of the
extra overhead, but the way you approach a problem.|||10Q very much!

"louis" <louisducnguyen@.gmail.com> wrote in message
news:1108077906.182098.130540@.f14g2000cwb.googlegr oups.com...
> Count(Product) will count non-null columns. To count null columns
> either use ISNULL function or count(*).
> SELECT Product, Count(isnull(Product,0)) AS Total
> FROM Orders_View
> GROUP BY Product, Year([DateOpened]), Month([DateOpened])
> HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
> ORDER BY Product;
> SELECT Product, Count(*) AS Total
> FROM Orders_View
> GROUP BY Product, Year([DateOpened]), Month([DateOpened])
> HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
> ORDER BY Product;|||Mercy Buckets!!!!

This one worked!
"dbmonitor" <dbmonitor_support@.hotmail.com> wrote in message
news:1108087375.715820.99720@.g14g2000cwa.googlegro ups.com...
> Blue Streak wrote:
> > Hello, folks.
> > I have the following query that I run that pulls up the count of the
> number of orders of a product.
> > SELECT Product, Count(Product) AS Total
> > FROM Orders_View
> > GROUP BY Product, Year([DateOpened]), Month([DateOpened])
> > HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
> > ORDER BY Product;
> > This query is fine but the COUNT() function only coughs-up the
> non-zero results. How can I get this query to cough-up the zero counts
> as well for all products listed in the Product table?
> > TIA...
> SELECT Product,
> (SELECT COUNT(*)
> FROM Orders_View
> WHERE Orders_View.Product = Product.Product
> AND Year([DateOpened])=2004
> AND Month([DateOpened])=10) AS ProductSales
> FROM Product
> --
> David Rowland
> http://dbmonitor.tripod.com