Collation problem: insert

Showing posts with label insert. Show all posts

Tuesday, March 27, 2012

Combining PIVOT and INSERT queries

Can someone please help me modify the following pivot query into an INSERT INTO query (i.e. results are exported into a new table)...

SELECT RespondantID, [1]As Q1, [2]As Q2, [3]As Q3, [4]As Q4, [5]As Q5, [6]As Q6, [7]As Q7, [8]As Q8, [9]As Q9, [10]As Q10FROM (SELECT RespondantID, QuestionID, AnswerFROM [3_Temp]WHERE SurveyID=1)AS preData PIVOT (MAX(Answer)FOR QuestionIDIN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) )AS dataORDER BY RespondantID

Thanks,

Martin

You can use a CTE and a SELECT into to get your pivot result to a new table. You need to remove ORDER BY RespondantID clause first.

Here is the sql script.

WITH mycte

(SELECT RespondantID, [1]AS Q1, [2]AS Q2, [3]AS Q3, [4]AS Q4, [5]AS Q5, [6]AS Q6, [7]AS Q7, [8]AS Q8, [9]AS Q9, [10]AS Q10

FROM(SELECT RespondantID, QuestionID, Answer

FROM [3_Temp]

WHERE SurveyID= 1)AS preDataPIVOT(MAX(Answer)FOR QuestionIDIN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]))AS data

)

SELECT RespondantID, [Q1], [Q2], [Q3], [Q4], [Q5], [Q6], [Q7], [Q8], [Q9], [Q10]INTO [NewtableResult]FROM mycte

Thursday, March 22, 2012

combining 2 queries?

HI I have two queries I am trying to combine
the first one simply returns several integers
for the second part I am trying to insert these integers into another table.
Code below does not work together. Do I need an integer array for @.logids
SELECT @.logids field1 FROM dbo.table1
WHERE POC_ID = 15
INSERT INTO table2
(field2)
VALUES
(@.logids)
--
Paul G
Software engineer.You got it backwards, try
INSERT INTO table1 (field1)
SELECT table2.field2 FROM table2;
"Paul" wrote:
> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another table.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.|||Paul,
If I understand your question correctly, it would be just
insert into table2 (field2)
SELECT field1 FROM dbo.table1
WHERE POC_ID = 15
hth
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another
> table.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.|||Hi thanks for the response,
tried this below, but nothing is getting inserted,
SET @.pocid = 15
INSERT INTO table2
(field2 )
SELECT field1 FROM table1 WHERE POC_ID = @.pocid
since I have no Nulls allowed for table to I get the error can not insert
NULL.
so it is trying to insert a NULL
"Ash" wrote:
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.|||Hi thanks for the response. I tried this but it tries to insert NULL
get error statement Cannot insert the value NULL into column 'field2', table
'table2'; column does not allow nulls. INSERT fails.
The statement has been terminated.
When I try the select statement without the insert it returns 83 values in
the
database output.
"Quentin Ran" wrote:
> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another
> > table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.
>
>|||It is working now thanks for the help.
"Ash" wrote:
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.|||it is working now thanks for the help.
"Quentin Ran" wrote:
> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another
> > table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.
>
>|||You need to make sure that if the field you are inserting into doesnt allow
nulls that the field you are selecting from also doesn't allow null.
Or unique...etc (ex. Primarykey)
INSERT into table2 (field2) SELECT field1 FROM dbo.table1 WHERE POC_ID = 15
If you have for example in table2 'field1' that is a PK then you need to
consider that.
ex INSERT into table2 (field1,field2) SELECT field1,field2 FROM dbo.table1
WHERE POC_ID = 15
But what you did is that you tried poplulating a row without assigning the
PK value.
"Paul" wrote:
> Hi thanks for the response. I tried this but it tries to insert NULL
> get error statement Cannot insert the value NULL into column 'field2', table
> 'table2'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> When I try the select statement without the insert it returns 83 values in
> the
> database output.
> "Quentin Ran" wrote:
> > Paul,
> >
> > If I understand your question correctly, it would be just
> >
> > insert into table2 (field2)
> > SELECT field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > hth
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> > > HI I have two queries I am trying to combine
> > > the first one simply returns several integers
> > > for the second part I am trying to insert these integers into another
> > > table.
> > > Code below does not work together. Do I need an integer array for @.logids
> > >
> > > SELECT @.logids field1 FROM dbo.table1
> > > WHERE POC_ID = 15
> > >
> > > INSERT INTO table2
> > > (field2)
> > > VALUES
> > > (@.logids)
> > >
> > > --
> > > Paul G
> > > Software engineer.
> >
> >
> >sql sql

combining 2 queries?

CombinedID

Hi,

Please find the SQL script to create a mini version of my database and some data at the bottom of this post. Please insert the data in the order I posted below.

Here's a quick narative of what the database is all about. This is a database that allows our company to keep track of all new contracts -- we call them deals -- and our progress on these contracts. You can think of it as a project management system. The key point here is that we have different types of projects so each contract goes through a different set of phases as we work on it. The phases a certain type of project goes through are
determined in tblPhaseType. So when we have a new deal, we put its data into tblDeals table and select the PhaseTypeID for that deal based on the kind of project it is.

Now, here's my question. Determining what phases we've completed for a specific deal is real easy. All I have to do is just, select the completed phases from tblProduction and INNER JOIN it with tblDeals and tblCompany and I'm done.

My challange is determining the remaining phases. For this, I have to first determine all phases for a specific contract then subtract out the ones that are already completed. To achieve this, I came up with a method but I'm not sure if that's the best way to do it. I combine the the Deal and Phase ID's to come up with a unique ID which I call CombinedID. Then I tell my stored procedure to exclude all CombinedID's that are in the sub-query which comes from tblProduction.

Here's the code for that stored procedure.
--------


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE PROCEDURE dbo.spRemainingPhases
AS SELECT dbo.tblDeals.DealID, dbo.tblPhase.PhaseID,
CAST(dbo.tblDeals.DealID AS varchar(2)) + CAST(dbo.tblPhase.PhaseID AS
varchar(2))
 AS CombinedID
FROM dbo.tblDeals INNER JOIN
 dbo.tblPhaseType ON dbo.tblPhaseType.PhaseTypeID =
dbo.tblDeals.PhaseTypeID INNER JOIN
 dbo.tblPhase ON dbo.tblPhaseType.PhaseTypeID =
dbo.tblPhase.PhaseTypeID
WHERE (NOT ((CAST(dbo.tblDeals.DealID AS varchar(2)) +
CAST(dbo.tblPhase.PhaseID AS varchar(2))) IN
 (SELECT CAST(tblProduction.DealID AS
varchar(2)) + CAST(tblProduction.PhaseID AS varchar(2)) AS CombinedID
 FROM tblProduction)))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--------

My question is: Is this the best way to handle this. I'm just not so crazy
about this CombinedID business. I should be able use and AND construct and
use DealID and PhaseID as they are. I just couldn't get this to work though.

Here's the SQL Script create all the necessary tables in the database
----------


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblDeals_tblCompany]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblCompany
GOif exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblDeals_tblPhaseType]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblPhase_tblPhaseType]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPhase] DROP CONSTRAINT FK_tblPhase_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblProduction_tblDeals]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblDeals
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblProduction_tblPhase]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblPhase
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/10/2003
10:25:26 AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblProduction]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblProduction]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/10/2003 10:25:26
AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDeals]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/10/2003 10:25:26
AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblPhase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhase]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/10/2003
10:25:26 AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCompany]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/10/2003
10:25:26 AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblPhaseType]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblPhaseType]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/10/2003
10:25:27 AM ******/
CREATE TABLE [dbo].[tblCompany] (
 [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
 [CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/10/2003
10:25:28 AM ******/
CREATE TABLE [dbo].[tblPhaseType] (
 [PhaseTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [Desription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/10/2003 10:25:29
AM ******/
CREATE TABLE [dbo].[tblDeals] (
 [DealID] [int] IDENTITY (1, 1) NOT NULL ,
 [CompanyID] [int] NOT NULL ,
 [DealDate] [smalldatetime] NOT NULL ,
 [PhaseTypeID] [tinyint] NOT NULL ,
 [CashAmount] [smallmoney] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/10/2003 10:25:29
AM ******/
CREATE TABLE [dbo].[tblPhase] (
 [PhaseID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [PhaseTypeID] [tinyint] NOT NULL ,
 [PhaseDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
 [PhasePercentage] [float] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/10/2003
10:25:29 AM ******/
CREATE TABLE [dbo].[tblProduction] (
 [TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
 [DealID] [int] NOT NULL ,
 [PhaseID] [tinyint] NOT NULL ,
 [TransactionTimeStamp] [smalldatetime] NOT NULL ,
 [Comments] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompany] ADD
 CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED
 (
 [CompanyID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhaseType] ADD
 CONSTRAINT [PK_tblPhaseType] PRIMARY KEY CLUSTERED
 (
 [PhaseTypeID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] ADD
 CONSTRAINT [PK_tblDeals] PRIMARY KEY CLUSTERED
 (
 [DealID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhase] ADD
 CONSTRAINT [PK_tblPhase] PRIMARY KEY CLUSTERED
 (
 [PhaseID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblProduction] ADD
 CONSTRAINT [DF_tblProduction_TransactionTimeStamp] DEFAULT (getdate()) FOR
[TransactionTimeStamp],
 CONSTRAINT [PK_tblProduction] PRIMARY KEY CLUSTERED
 (
 [TransactionID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] ADD
 CONSTRAINT [FK_tblDeals_tblCompany] FOREIGN KEY
 (
 [CompanyID]
 ) REFERENCES [dbo].[tblCompany] (
 [CompanyID]
 ),
 CONSTRAINT [FK_tblDeals_tblPhaseType] FOREIGN KEY
 (
 [PhaseTypeID]
 ) REFERENCES [dbo].[tblPhaseType] (
 [PhaseTypeID]
 )
GO
ALTER TABLE [dbo].[tblPhase] ADD
 CONSTRAINT [FK_tblPhase_tblPhaseType] FOREIGN KEY
 (
 [PhaseTypeID]
 ) REFERENCES [dbo].[tblPhaseType] (
 [PhaseTypeID]
 )
GO
ALTER TABLE [dbo].[tblProduction] ADD
 CONSTRAINT [FK_tblProduction_tblDeals] FOREIGN KEY
 (
 [DealID]
 ) REFERENCES [dbo].[tblDeals] (
 [DealID]
 ),
 CONSTRAINT [FK_tblProduction_tblPhase] FOREIGN KEY
 (
 [PhaseID]
 ) REFERENCES [dbo].[tblPhase] (
 [PhaseID]
 )
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the type of
phase structure this deal will go through', N'user', N'dbo', N'table',
N'tblDeals', N'column', N'PhaseTypeID'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the percentage
value of the phase', N'user', N'dbo', N'table', N'tblPhase', N'column',
N'PhasePercentage'
GO
exec sp_addextendedproperty N'MS_Description', null, N'user', N'dbo',
N'table', N'tblProduction', N'column', N'TransactionTimeStamp'
GO

=========================================

And here's the data
----------


INSERT INTO [tblCompany] ([CompanyName])VALUES('Johnny''s Remodeling')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Perfect Cut Lawncare')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Useless Ideas Unlimited')INSERT INTO [tblPhaseType] ([Desription])VALUES('TV Commercial - 4 Phases')
INSERT INTO [tblPhaseType] ([Desription])VALUES('Full Campaign - 6 Phases')
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Customer
Info',1.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Write
script',2.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Shoot',3.50000
0000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Edit
commercial',2.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Customer
info',1.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Write
script',1.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Design print
ad',1.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Shoot',1.50000
0000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Edit',2.000000
000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Publish',2.000
000000000000e-001)
INSERT INTO [tblDeals]
([CompanyID],[DealDate],[PhaseTypeID],[CashAmount])VALUES(1,'Aug 5 2003
12:00:00:000AM',1,120.0000)
INSERT INTO [tblDeals]
([CompanyID],[DealDate],[PhaseTypeID],[CashAmount])VALUES(2,'Sep 9 2003
12:00:00:000AM',2,150.0000)
INSERT INTO [tblProduction]
([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,1,'Nov 10
2003 10:23:00:000AM','Received company logo')
INSERT INTO [tblProduction]
([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,2,'Nov 10
2003 10:23:00:000AM','Finished writing script')
INSERT INTO [tblProduction]
([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,5,'Nov 10
2003 10:23:00:000AM','Just received company info')
INSERT INTO [tblProduction]
([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,7,'Nov 10
2003 10:24:00:000AM','Finished designing ad copy')

The key here is a LEFT OUTER JOIN to your tblProduction. This query should get you the remaining phases for all of your deals:


SELECT
dbo.tblDeals.DealID,
dbo.tblPhase.PhaseID
FROM
dbo.tblPhaseType
INNER JOIN
dbo.tblDeals ON dbo.tblDeals.PhaseTypeID = dbo.tblPhaseType.PhaseTypeID
INNER JOIN
dbo.tblPhase ON dbo.tblPhaseType.PhaseTypeID = dbo.tblPhase.PhaseTypeID
LEFT OUTER JOIN
dbo.tblProduction ON dbo.tblProduction.DealID = dbo.tblDeals.DealID AND dbo.tblProduction.PhaseID = dbo.tblPhase.PhaseID
WHERE
 dbo.tblProduction.DealID IS NULL

Terri|||That's exactly it. Thank you very much Terri. Your help is very much appreciated.

combine update statements....help...

Hi guys! Is there a way to combine these update statements?

Dim update_phase As New SqlCommand("INSERT INTO TE_shounin_zangyou (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_zangyou WHERE [syain_No] = @.syain_No", cnn)

Dim update_phase2 As New SqlCommand(" UPDATE TE_shounin_zangyou SET " & " phase=2, phase_states2=06,syounin2_sysd=CONVERT(VARCHAR(10),GETDATE(),101) WHERE [syain_No] = @.syain_No", cnn)

The same table is updated so I think it would be better to have just one update statement. But the problem is that, the first update statement retrieves values from another table, whereas the update values of the second statement is fixed. Is there a way to combine these two statements. I tried to do so but it does not update. Here's my code...

Dim update_phase As New SqlCommand("UPDATE TE_shounin_zangyou SET TE_shounin_zangyou.syain_No=TE_zangyou.syain_No, TE_shounin_zangyou.date_kyou=TE_zangyou.date_kyou, TE_shounin_zangyou.time_kyou=TE_zangyou.time_kyou FROM TE_zangyou WHERE TE_zangyou.syain_No = TE_shounin_zangyou.syain_No", cnn)

Please help me. Thanks.

Audrey

You can do it in one statement. Understand the consequences first. Lets say you already have some records (say 5) in table TE_shounin_zangyou, your first INSERT will add some more rows to it. Your second UPDATE will update the rows from the insert as well as the existing rows. However, if you combine both the INSERT and the UPDATE into one statement you will only modofy the rows being INSERTED with the SELECT statement. Any pre-existing rows will not be affected. If, in your case, there would be NO pre-existing rows with the condition [syain_No] = @.syain_No, then you can do it all in one statement as follows:

Try this:

INSERT INTO TE_shounin_zangyou (syain_No,date_kyou,time_kyou,phase,phase_states2,syounin2_sysd)

SELECT syain_No,date_kyou,time_kyou,2,'06',CONVERT(VARCHAR(10),GETDATE(),101) FROM TE_zangyou WHERE [syain_No] = @.syain_No

Sunday, March 11, 2012

Columns_Updated () and Update() functions

Hello all,

I'm trying to apply an audit DB trigger on a master-detail tables ,I do not need to insert record in the audit master every time an update happend in the audit detail,I tried to use columns_updated() > 0 but it didn't work the way I axpected it to be ..it stopped inserting into the audit master even if an update was applied against the master table ...any help please and I use the Update() function ? is their any major difference?

your help is appreciated

Thanks

Alaa M

COLUMNS_UPDATED() returns a value as a VARBINARY datatype, so for your comparison to work you need to convert the value to an INT first, like this:

CAST(COLUMNS_UPDATED() AS INT)

Chris

|||

Thanks CHRIS

Actually it didn't work ..

or may be i'm missing something here ,anyway this is the code I'm using if you or anyone can point the wrong thing that I'm doing here will be great.

this is the trigger for the master table ,and I only need to insert the changes applied against this table into Audit master no matter how many updates applied to the detail table.

ALTER Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d
LEFT JOIN [Master] A
ON d.MasterID = A.MasterID
)

if (CAST(COLUMNS_UPDATED() AS INT ) & 255) > 0

begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType],
[CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MastertId

end;

thank

|||

I get the feeling that you might be trying to over-complicate things. I can't see the point of explicitly checking for updated columns using the COLUMNS_UPDATED() function - the fact that at least one row has been updated is implied by the existence of rows in the 'deleted' virtual table.

I'm also confused by the WHERE clause in the INSERT statement:

WHERE del.MasterID = MastertId

Does the MastertId column exist in the 'deleted' virtual table? If so does MastertId ever equal del.MasterID?

Would the re-worked example below work for you?

Chris

ALTER TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

IF EXISTS (SELECT 1 FROM deleted)

BEGIN

INSERT INTO [dbo].[syslogAuditMaster]
([MasterID],
[Name],
[CodeIDType],
[DateStarted],
[DateDue],
[CodeIDReminderType],
[CodeIDAuditStatus],
[NoteID],
[DeletedOnDate],
[Event])
SELECT del.[MasterID],
del.[Name],
del.[CodeIDType],
del.[Datestarted],
del.[Datedue],
del.[CodeIDReminderType],
del.[CodeIDAuditStatus],
del.[NoteID],
del.[DeletedOnDate],
'UPDATE'
FROM deleted del

END

|||

Thnaks for your replay,

I think you are missing the whole point of my question here ,which is to avoid the repetition in the Header or Master table when any update done against the Detailed table ..

I'm not sure if the "deleted and insered" tables hold a data record at the time or do they hold a set of records till the commit time .

it helped when I compared the "deleted " against the "inserted" data and by setting flag for any changes the insert to the Audit table was done.

although I'm not quite sure that is a good solution ,that I always think of using the functions will be more reliable and that was my resaon of asking about the Columns_Updated () and how far can I use it .

Thanks for your replies ..

|||

Just to clarify your requirement could you post examples of data in your Master table before and after the data has been updated, along with an example of the data that you would expect to be inserted into the syslogAuditMaster table as a result of the update?

Also if you could post the DDL statements used to create the Master and syslogAuditMaster tables then that would be extremely useful.

Thanks

Chris

|||

Hi Chris,

Thanks for your reply,

the DDL statements used to create the Master ,Detail and syslogAuditMaster are

--for the Master table

CREATE TABLE [dbo].[Master] (
[MasterID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL
) ON [PRIMARY]

--for the Detail table

CREATE TABLE [dbo].[Detail] (
[DetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MasterID] [int] NOT NULL ,
[Datestarted] [datetime] NULL , [NoteID] [int] NULL,[ColA] ...[ColB]..etc

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Detail] ADD
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED
(
[DetailID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AuditDetail] ADD
CONSTRAINT [Master_Detail_FK1] FOREIGN KEY
(
[MasterID]
) REFERENCES [dbo].[Master] (
[MasterID]
)GO

-- for the Audit table

CREATE TABLE [dbo].[syslogAuditMaster] (
[MasterID] [int] NOT NULL,
[Name] [nvarchar] (254) NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL ,
[ChangedOnDate] [datetime] NOT NULL DEFAULT GETDATE(),
[UserName] [nvarchar] (150) NOT NULL DEFAULT suser_sname(),
[HostName] [nvarchar] (150) NOT NULL DEFAULT HOST_NAME(),
[Event] [nvarchar] (150) NOT NULL
) ON [PRIMARY]

when the user applied anychanges to the detail table through the .NET application , the store proc sp_Update which has the UPDATE statement for both the Master and Detail tables will executed,and as a result the DB trigget will be fired .

--Currently

if the field [NoteId] for [MasterID] = 3344 and [DetailID]=1122 has been changed in the Detail table ,I'll get the following in the syslogAuditMaster

[MasterID] = 3344,with the event type 'UPDATE'

--what to excpect

I should not get anything in the syslogAuditMaster ,cuz there is nothing new with the Master table to be added only if I changed anything within the Master I should get a record inserted in the AuditMaster

this is the DB trigger I'm using currently

Create Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d inner join inserted i on i.MasterID = d.MasterID
where ltrim(rtrim(i.MasterID))+ltrim(rtrim(isnull(i.Name,'')))+ltrim(rtrim(isnull(i.CodeIDtype,'')))+ltrim(rtrim(isnull(i.DateStarted,'')))+ltrim(rtrim(isnull(i.DateDue,'')))+ltrim(rtrim(isnull(i.codeIDReminderType,'')))+ltrim(rtrim(isnull(i.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(i.NoteID,'')))+ltrim(rtrim(isnull(i.DeletedonDate,'')))
<> ltrim(rtrim(d.MasterID))+ltrim(rtrim(isnull(d.Name,'')))+ltrim(rtrim(isnull(d.CodeIDtype,'')))+ltrim(rtrim(isnull(d.DateStarted,'')))+ltrim(rtrim(isnull(d.DateDue,'')))+ltrim(rtrim(isnull(d.codeIDReminderType,'')))+ltrim(rtrim(isnull(d.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(d.NoteID,'')))+ltrim(rtrim(isnull(d.DeletedonDate,'')))
)
begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType], [CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MasterId

end;

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

hope this will help ..

Thank again

Ala'a M

|||

Right, I think I now understand where you are going with this.

Basically, even though you are issuing an UPDATE statement to update a row in the Master table, you might actually not be changing any of the values - in which case you don't want to write an audit row to the syslogAuditMaster table. You, therefore, only want to write a row to the syslogAuditMaster table if at least one of the columns values changes as a result of the update. Is this correct?

The problem with the COLUMNS_UPDATED() and UPDATED() functions is that they will report a column as being updated even if the column's value doesn't actually change as a result of the UPDATE statement. For this reason, if the solution you are using works fine then I see no reason why you shouldn't continue to use it.

Personally, I would move away from the text-based comparison that you are doing and would have explicit checks for each of the columns, see the example below. Note that there is no need for the IF EXISTS statement in this example. Also, if you update several Master rows at a time then the example below will only write audit rows for the Master rows that have actually changed.

Chris

CREATE TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

INSERT INTO [dbo].[syslogAuditMaster](

[MasterID],
[Name],
[CodeIDType],
[DateStarted],
[DateDue],
[CodeIDReminderType],
[CodeIDAuditStatus],
[NoteID],
[DeletedOnDate],
[Event])

SELECT del.[MasterID],

del.[Name],
del.[CodeIDType],
del.[Datestarted],
del.[Datedue],
del.[CodeIDReminderType],
del.[CodeIDAuditStatus],
del.[NoteID],
del.[DeletedOnDate],
'UPDATE'

FROM deleted del

INNER JOIN inserted ins ON ins.MasterID = del.MasterID

WHERE ((del.[Name] <> ins.[Name])

OR (del.CodeIDType <> ins.CodeIDType)
OR ((del.[DateStarted] <> ins.[DateStarted]) OR (del.[DateStarted] IS NULL AND ins.[DateStarted] IS NOT NULL) OR (del.[DateStarted] IS NOT NULL AND ins.[DateStarted] IS NULL))
OR ((del.[DateDue] <> ins.[DateDue]) OR (del.[DateDue] IS NULL AND ins.[DateDue] IS NOT NULL) OR (del.[DateDue] IS NOT NULL AND ins.[DateDue] IS NULL))
OR ((del.[CodeIDReminderType] <> ins.[CodeIDReminderType]) OR (del.[CodeIDReminderType] IS NULL AND ins.[CodeIDReminderType] IS NOT NULL) OR (del.[CodeIDReminderType] IS NOT NULL AND ins.[CodeIDReminderType] IS NULL))
OR ((del.[CodeIDAuditStatus] <> ins.[CodeIDAuditStatus]) OR (del.[CodeIDAuditStatus] IS NULL AND ins.[CodeIDAuditStatus] IS NOT NULL) OR (del.[CodeIDAuditStatus] IS NOT NULL AND ins.[CodeIDAuditStatus] IS NULL))
OR ((del.[NoteID] <> ins.[NoteID]) OR (del.[NoteID] IS NULL AND ins.[NoteID] IS NOT NULL) OR (del.[NoteID] IS NOT NULL AND ins.[NoteID] IS NULL))
OR ((del.[DeletedOnDate] <> ins.[DeletedOnDate]) OR (del.[DeletedOnDate] IS NULL AND ins.[DeletedOnDate] IS NOT NULL) OR (del.[DeletedOnDate] IS NOT NULL AND ins.[DeletedOnDate] IS NULL)))

|||

Thanks alot for giving me the chance to think loudly and exchange thoughts helped me alot ..

thanx Chris

Columns_Updated () and Update() functions

Hello all,

your help is appreciated

Thanks

Alaa M

COLUMNS_UPDATED() returns a value as a VARBINARY datatype, so for your comparison to work you need to convert the value to an INT first, like this:

CAST(COLUMNS_UPDATED() AS INT)

Chris

|||

Thanks CHRIS

Actually it didn't work ..

or may be i'm missing something here ,anyway this is the code I'm using if you or anyone can point the wrong thing that I'm doing here will be great.

this is the trigger for the master table ,and I only need to insert the changes applied against this table into Audit master no matter how many updates applied to the detail table.

ALTER Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d
LEFT JOIN [Master] A
ON d.MasterID = A.MasterID
)

if (CAST(COLUMNS_UPDATED() AS INT ) & 255) > 0

end;

thank

|||

I'm also confused by the WHERE clause in the INSERT statement:

WHERE del.MasterID = MastertId

Does the MastertId column exist in the 'deleted' virtual table? If so does MastertId ever equal del.MasterID?

Would the re-worked example below work for you?

Chris

ALTER TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

IF EXISTS (SELECT 1 FROM deleted)

BEGIN

INSERT INTO [dbo].[syslogAuditMaster]
([MasterID],
[Name],
[CodeIDType],
[DateStarted],
[DateDue],
[CodeIDReminderType],
[CodeIDAuditStatus],
[NoteID],
[DeletedOnDate],
[Event])
SELECT del.[MasterID],
del.[Name],
del.[CodeIDType],
del.[Datestarted],
del.[Datedue],
del.[CodeIDReminderType],
del.[CodeIDAuditStatus],
del.[NoteID],
del.[DeletedOnDate],
'UPDATE'
FROM deleted del

END

|||

Thnaks for your replay,

I think you are missing the whole point of my question here ,which is to avoid the repetition in the Header or Master table when any update done against the Detailed table ..

I'm not sure if the "deleted and insered" tables hold a data record at the time or do they hold a set of records till the commit time .

it helped when I compared the "deleted " against the "inserted" data and by setting flag for any changes the insert to the Audit table was done.

Thanks for your replies ..

|||

Also if you could post the DDL statements used to create the Master and syslogAuditMaster tables then that would be extremely useful.

Thanks

Chris

|||

Hi Chris,

Thanks for your reply,

the DDL statements used to create the Master ,Detail and syslogAuditMaster are

--for the Master table

--for the Detail table

CREATE TABLE [dbo].[Detail] (
[DetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MasterID] [int] NOT NULL ,
[Datestarted] [datetime] NULL , [NoteID] [int] NULL,[ColA] ...[ColB]..etc

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Detail] ADD
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED
(
[DetailID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AuditDetail] ADD
CONSTRAINT [Master_Detail_FK1] FOREIGN KEY
(
[MasterID]
) REFERENCES [dbo].[Master] (
[MasterID]
)GO

-- for the Audit table

--Currently

if the field [NoteId] for [MasterID] = 3344 and [DetailID]=1122 has been changed in the Detail table ,I'll get the following in the syslogAuditMaster

[MasterID] = 3344,with the event type 'UPDATE'

--what to excpect

this is the DB trigger I'm using currently

Create Trigger trig_Audit_Upd
on [dbo].[Master]

end;

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

hope this will help ..

Thank again

Ala'a M

|||

Right, I think I now understand where you are going with this.

Chris

CREATE TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

INSERT INTO [dbo].[syslogAuditMaster](

[MasterID],
[Name],
[CodeIDType],
[DateStarted],
[DateDue],
[CodeIDReminderType],
[CodeIDAuditStatus],
[NoteID],
[DeletedOnDate],
[Event])

SELECT del.[MasterID],

del.[Name],
del.[CodeIDType],
del.[Datestarted],
del.[Datedue],
del.[CodeIDReminderType],
del.[CodeIDAuditStatus],
del.[NoteID],
del.[DeletedOnDate],
'UPDATE'

FROM deleted del

INNER JOIN inserted ins ON ins.MasterID = del.MasterID

WHERE ((del.[Name] <> ins.[Name])

OR (del.CodeIDType <> ins.CodeIDType)
OR ((del.[DateStarted] <> ins.[DateStarted]) OR (del.[DateStarted] IS NULL AND ins.[DateStarted] IS NOT NULL) OR (del.[DateStarted] IS NOT NULL AND ins.[DateStarted] IS NULL))
OR ((del.[DateDue] <> ins.[DateDue]) OR (del.[DateDue] IS NULL AND ins.[DateDue] IS NOT NULL) OR (del.[DateDue] IS NOT NULL AND ins.[DateDue] IS NULL))
OR ((del.[CodeIDReminderType] <> ins.[CodeIDReminderType]) OR (del.[CodeIDReminderType] IS NULL AND ins.[CodeIDReminderType] IS NOT NULL) OR (del.[CodeIDReminderType] IS NOT NULL AND ins.[CodeIDReminderType] IS NULL))
OR ((del.[CodeIDAuditStatus] <> ins.[CodeIDAuditStatus]) OR (del.[CodeIDAuditStatus] IS NULL AND ins.[CodeIDAuditStatus] IS NOT NULL) OR (del.[CodeIDAuditStatus] IS NOT NULL AND ins.[CodeIDAuditStatus] IS NULL))
OR ((del.[NoteID] <> ins.[NoteID]) OR (del.[NoteID] IS NULL AND ins.[NoteID] IS NOT NULL) OR (del.[NoteID] IS NOT NULL AND ins.[NoteID] IS NULL))
OR ((del.[DeletedOnDate] <> ins.[DeletedOnDate]) OR (del.[DeletedOnDate] IS NULL AND ins.[DeletedOnDate] IS NOT NULL) OR (del.[DeletedOnDate] IS NOT NULL AND ins.[DeletedOnDate] IS NULL)))

|||

Thanks alot for giving me the chance to think loudly and exchange thoughts helped me alot ..

thanx Chris

Thursday, March 8, 2012

Column-conscious bulk insert

I am trying to bulk insert a text file. The file has fixed-length fields
with no field terminators. BOL says that field terminators are only
needed when the data does *not* contain fixed-length fields, which
implies they are optional -- so I made a format file without any (two
consecutive tabs with nothing between them). The following message
resulted:

Server: Msg 4827, Level 16, State 1, Line 1
Could not bulk insert. Invalid column terminator for column number
1 in format file

That sounds like I am required to have some sort of terminator in the
format file, even though there aren't any in the data file. Unfortunately,
the documentation on bcp/bulk copy and format files does not directly
address this point, and I would appreciate some help.

BTW, putting '""' (empty string) for the terminator also leads to errors,
with the first field overflowing -- bulk insert can't figure out where
it ends.

Thanks,
Jim Geissman
Countrywide Home Loansjim_geissman@.countrywide.com (Jim Geissman) wrote in message news:<b84bf9dc.0401281622.34aa0e42@.posting.google.com>...
> I am trying to bulk insert a text file. The file has fixed-length fields
> with no field terminators. BOL says that field terminators are only
> needed when the data does *not* contain fixed-length fields, which
> implies they are optional -- so I made a format file without any (two
> consecutive tabs with nothing between them). The following message
> resulted:
> Server: Msg 4827, Level 16, State 1, Line 1
> Could not bulk insert. Invalid column terminator for column number
> 1 in format file
> That sounds like I am required to have some sort of terminator in the
> format file, even though there aren't any in the data file. Unfortunately,
> the documentation on bcp/bulk copy and format files does not directly
> address this point, and I would appreciate some help.
> BTW, putting '""' (empty string) for the terminator also leads to errors,
> with the first field overflowing -- bulk insert can't figure out where
> it ends.
> Thanks,
> Jim Geissman
> Countrywide Home Loans

Jim,

Just a thought, but have you tried using the "-c" flag with the BCP IN
command instead of using a format file? Create a target table where
the column widths exactly match the fields in your file, and give it a
try. ("-c" takes no parameters). Assuming you've got record
terminators in the correct place, I think this should work.
Personally, I hate using format files and avoid them like the plague
if I can.

bcp <db>..<target_tbl> in <datafile> -Uuser -Ppass -Sserver -c

Phil|||Thanks, Phil.

I wish that were true. However it seems that -c assumes \t (tab)
separators. At least it doesn't work. Putting in -t (specify separator
but don't provide one) causes bcp to just sit there and do nothing.
I'm going to use DTS and specify column by column where they all end.
It's such a waste of effort, though, because the data is from the Census
and the input exactly matches the table, character by character.

Thanks again
Jim

> Jim,
> Just a thought, but have you tried using the "-c" flag with the BCP IN
> command instead of using a format file? Create a target table where
> the column widths exactly match the fields in your file, and give it a
> try. ("-c" takes no parameters). Assuming you've got record
> terminators in the correct place, I think this should work.
> Personally, I hate using format files and avoid them like the plague
> if I can.
> bcp <db>..<target_tbl> in <datafile> -Uuser -Ppass -Sserver -c
> Phil|||Jim Geissman (jim_geissman@.countrywide.com) writes:
> I am trying to bulk insert a text file. The file has fixed-length fields
> with no field terminators. BOL says that field terminators are only
> needed when the data does *not* contain fixed-length fields, which
> implies they are optional -- so I made a format file without any (two
> consecutive tabs with nothing between them). The following message
> resulted:
> Server: Msg 4827, Level 16, State 1, Line 1
> Could not bulk insert. Invalid column terminator for column number
> 1 in format file
> That sounds like I am required to have some sort of terminator in the
> format file, even though there aren't any in the data file.
> Unfortunately, the documentation on bcp/bulk copy and format files does
> not directly address this point, and I would appreciate some help.

You must specify the separator in quotes, but it can be the empty
string, "". The tabs does not mean anything to BCP, as far as I know.
At least it never complain about lack of tabs in my format files.

>BTW, putting '""' (empty string) for the terminator also leads to errors,
>with the first field overflowing -- bulk insert can't figure out where
>it ends.

What about posting:

o CREATE TABLE statement for your table.
o The format file. (The one with "" in it.)
o A sample file to bulk-load.

That makes it a little easier to have a guess of what is going on.

If the data file is more than 75 characters wide, you are probably
better of putting it an attachment.

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

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

Friday, February 24, 2012

column name as group in a table

Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
MangeshGroup is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
Mangesh|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:
> Group is a reserved word. It's a best practice not to use column names that
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what'
> Mangesh
>|||"Mangesh Deshpande" schrieb:
> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller|||Thanks a lot.
"doller" wrote:
> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>

Column insert help

Hi,

I have some values I want put into a table, but the values are from other sources and I dont know how to retrieve them..

I'llshow my code, and the bold is explaining what I want inserted and wherefrom. I'd apprechiate if someone could help me with syntax etc. Thereare 2 about getting value from another table and one about just puttingin straight forward text..:

command.CommandText ="INSERT INTO Messages (sendername,recievername,message,Date,subject)VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";

command.Parameters.Add("@.sendername", System.Web.HttpContext.Current.User.Identity.Name)

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);

command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);

command.Parameters.Add("@.date", DateTime.Now.ToString());

command.ExecuteNonQuery();

Thanks alot if anyone can help me with those three things..

Jon

Pls help!

|||

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

Could you further explain that.

Thanks

|||

Hi, sorry not very well explained!

The bold writing means:

I have a talbe called 'Transactions' - and for every row in which Itemid = Itemid, the username is retrieved. So this could be 1 username, or many, depending on how many people have the same Itemid in their row. I guess the usernames would have to be separated by commas.

Thanks!

Jon

|||

command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";

command.Parameters.Add("@.sendername", System.Web.HttpContext.Current.User.Identity.Name)

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);

command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);

command.Parameters.Add("@.date", DateTime.Now.ToString());

command.ExecuteNonQuery();

You are trying to do sub query's with your parameters. You cant do that. Also, I would think that you would want one record for each value. I would probably create a table with one record for each user, another Table (ex. Transactions with a UserId) with one record for each transactions and another table (ex. Items) with a TransactionId. If not you are going to have a hard time with your data. Then you could just simply do one select. Something like this:
SELECT ui.UserName, t.TransactionId, i.Item FROM UserId ui INNER JOIN Transactions t ON t.UserId = ui.UserID INNER JOIN Items i ON i.TransactionId = t.TransactionId WHERE ui.UserId = @.UserId

That would get you every record based on the userid with out having to do all that funky stuff. You dont really ever want to insert values(espcially with orders) by CSV's. Maintenance nightmare.

Does that make sense?

|||

Hi, I'll have to go through what you said and ask in more detail sorry.

What do you mean by sub query's?

What do you mean by one record for each value? Create a table for each value?

I will explain my scenario maybe it will help:

I have a message system within the site, and the method above is to send a 'bulk message' - that is sending the same message to many people.

My messages table has the columns @.sendername, @. recievername etc.. From this people check the messages through a formview which shoes the messages which reciever name is their username.

This mass message is sent by a user. He presses a button on a formview which contains an Itemid value. - then the button adds all the users in the transactions table which contain the same itemid value.

The second bold bit - this is to insert the value of the paymentinstructions (from 'items' table) of the user (who is sending the message).

Last bit - just universal text that gets inserted every time the same.

Hope this deeper explanation helps things, thanks for helping

Jon

|||

SO i would create a UserMessage(Or something like that) table that has a foreign key UserId in one table, and then create a second table called Messages which has a messageId (foreign key to UserMessage) and the other columns would be SenderName, ReceiverName, Message, PaymentInstructios etc...

Then you can get all messages by sending in one USerId and when you insert you can simply provide one UserId.

Make sense?

|||

Hi, before I try your method, can your hear this out -

I have a page with gridviews that retrieve the data that I want to insert - from 'SqlDataSource1' and 2 etc etc.

Can I just set the parameter to SqlDataSource1? Its on the same page..

I.e.:

command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";
command.Parameters.AddWithValue("@.sendername", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.AddWithValue("@.recievername", SqlDataSource2);
command.Parameters.AddWithValue("@.message", SqlDataSource3);
command.Parameters.AddWithValue("@.subject", TextBox1.Text);
command.Parameters.AddWithValue("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();

Thanks

Jon

|||

Yes, you can do it that way, but you are hitting the DB 3 times to retrieve the data you need. That is a sign of your tables not being normalized. I would think that you would want a one to many with Users and MessageId table and then a one to many table with MessageId table and Messages. This would allow you to only hit the db one time and retrieve the data you want.

|||

Hi thanks for your help. I think im nearly there so im going to post each individual error message that I have up, and see what people make of them!

Thanks again,

Jon

Sunday, February 19, 2012

Column does not allow nulls

Hi,

This should be straight forward, but I've searched high and low on the net.

I have a FORM which allows me to INSERT data, SQL Server 2005 backend. I populate the all the mandatory fields.

But when I click on the Insert button, it won't let me save and says:

Column 'PERSONAL_ID' does not allow nulls

I'musing tableadapters, business logic layers etc. and pausing clearlyshows that the values from the form are being passed to the procedurethat I have created "AddNewRecord". And PERSONAL_ID is definitely notNULL!

It fails on

Line 971: Me.Rows.Add(row)

PERSONAL_IDis a primary key which I generate. The pause also shows that forPERSONAL_ID it says" {"Conversion from type 'DBNull' to type 'String'is not valid."}

Function AddNewRecord looks like this:

<System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, True)> _
Public FunctionAddNewRecord(ByVal PERSONAL_ID As String, ByVal SURNAMEAs String, ByVal CHRISTIAN_NAME As String, ByVal SEX As String, _
ByVal FAMILY_POSITION As String, ByVal FAMILY_ID As String, ByValADDRESS_1 As String, ByVal ADDRESS_2 As String, _
ByVal ADDRESS_3As String, ByVal ADDRESS_4 As String, ByVal ADDRESS_5 As String, ByValADDRESS_6 As String, ByVal POSTCODE As String, ByVal COUNTRY As String,ByVal ORG_ID As String) As Boolean
' create a new details row instance
Dim details As New smDetails.smTbl_DetailsIDDataTable
Dim detail As smDetails.smTbl_DetailsIDRow = details.NewsmTbl_DetailsIDRow

details.AddsmTbl_DetailsIDRow(detail)
.
.
.
my formview code is this:

<InsertItemTemplate>
PERSONAL_ID:
<asp:TextBox ID="PERSONAL_IDTextBox" runat="server"
Text='<%# Bind("PERSONAL_ID") %>' AutoPostBack="True" />
 
SURNAME:
<asp:TextBox ID="SURNAMETextBox" runat="server" Text='<%# Bind("SURNAME") %>'
AutoPostBack="True" />
 
CHRISTIAN_NAME:
<asp:TextBox ID="CHRISTIAN_NAMETextBox" runat="server"
Text='<%# Bind("CHRISTIAN_NAME") %>' />
 
SEX:
<asp:TextBox ID="SEXTextBox" runat="server" Text='<%# Bind("SEX") %>' />
 
FAMILY_POSITION:
<asp:TextBox ID="FAMILY_POSITIONTextBox" runat="server"
Text='<%# Bind("FAMILY_POSITION") %>' />
 
FAMILY_ID:
<asp:TextBox ID="FAMILY_IDTextBox" runat="server"
Text='<%# Bind("FAMILY_ID") %>' />
 
ADDRESS_1:
<asp:TextBox ID="ADDRESS_1TextBox" runat="server"
Text='<%# Bind("ADDRESS_1") %>' />
 
ADDRESS_2:
<asp:TextBox ID="ADDRESS_2TextBox" runat="server"
Text='<%# Bind("ADDRESS_2") %>' />
 
ADDRESS_3:
<asp:TextBox ID="ADDRESS_3TextBox" runat="server"
Text='<%# Bind("ADDRESS_3") %>' />
 
ADDRESS_4:
<asp:TextBox ID="ADDRESS_4TextBox" runat="server"
Text='<%# Bind("ADDRESS_4") %>' />
 
ADDRESS_5:
<asp:TextBox ID="ADDRESS_5TextBox" runat="server"
Text='<%# Bind("ADDRESS_5") %>' />
 
ADDRESS_6:
<asp:TextBox ID="ADDRESS_6TextBox" runat="server"
Text='<%# Bind("ADDRESS_6") %>' />
 
POSTCODE:
<asp:TextBox ID="POSTCODETextBox" runat="server"
Text='<%# Bind("POSTCODE") %>' />
 
COUNTRY:
<asp:TextBox ID="COUNTRYTextBox" runat="server" Text='<%# Bind("COUNTRY") %>' />
 
ORG_ID:
<asp:TextBox ID="ORG_IDTextBox" runat="server" Text='<%# Bind("ORG_ID") %>' />
 
<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True"
CommandName="Insert" Text="Insert" />
<asp:LinkButton ID="InsertCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel" Text="Cancel" />
</InsertItemTemplate

Any pointers in the right direction would be appreciated.

Thanks in advance

Tushar

Can you post the stored procedure/sql statement that performs the insert?

|||

INSERT INTO smTbl_DetailsID (PERSONAL_ID, SURNAME, CHRISTIAN_NAME, SEX, FAMILY_POSITION, FAMILY_ID, ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, ADDRESS_5, ADDRESS_6, POSTCODE, COUNTRY, ORG_ID)VALUES (@.PERSONAL_ID,@.SURNAME,@.CHRISTIAN_NAME,@.SEX,@.FAMILY_POSITION,@.FAMILY_ID,@.ADDRESS_1,@.ADDRESS_2,@.ADDRESS_3,@.ADDRESS_4,@.ADDRESS_5,@.ADDRESS_6,@.POSTCODE,@.COUNTRY,@.ORG_ID)

Execute mode is Scalar

thanks

Tushar

|||

I've found the solution is to replace

details.AddsmTbl_DetailsIDRow(detail)

inFunction AddNewRecord in the BLL

with

Adapter1.InsertQuery_TAM(PERSONAL_ID, SURNAME, CHRISTIAN_NAME, SEX, _
FAMILY_POSITION, FAMILY_ID, ADDRESS_1, ADDRESS_2, _
ADDRESS_3, ADDRESS_4, ADDRESS_5, ADDRESS_6, POSTCODE, COUNTRY, ORG_ID)

i.e. my user defined insert query.

For some reason adding a new row directly causes the SELECT routine to run, which expects a value for the PERSONAL_ID (primary key). I'm sure I'll discover why in due course.

Also the above is not really very clear in the tutorials.

HTH

Tushar Cool

Thursday, February 16, 2012

Column Data truncation , how to identify column?

Hi There

This one has bothered me ever since sql server 2000.

When you do an insert into a table with literally hundreds of char or varchar columns and you get the error that the insert failed due to data loss/truncation on a column.

Is there anyway in 2005 to actually find out what column ? Since there are hundreds is is literally a long process of going though each column 1 by 1 manually.

The database engine surely MUST know what column this occurred on so why can it not tell you which column the truncation occurred on ?

Can this be done in 2005 if not will this information be available in 2008 ?

Thanx

Nope. It is not available on any version (2000,2005 & 2008).

The SQL Server message will be more generic than the specific, bcs the error caused by the engine treat all the object same, there is no special error handler written for object based.

Here you want to throw an error – for the table with specific column name. These are data definition & data error. These can be controlled.

|||

Hi Manivannan

Thank you for the reply, please could you elaborate on

"Here you want to throw an error - for the table with specific column name"

How exactly would one do that, as far as i know the try catch metod will return the same error without a column name ?

Thanx

|||

You can check insert operations like in following example :

The test table:

CREATE TABLE [dbo].[Atable](

Angel [varchar](5) NULL,

[varchar](5) NULL

) ON [secondary]

use following sp :

create procedure CheckInsert

@.i varchar(5000),@.j varchar(5000)

BEGIN

DECLARE @.COLLENGTH int

declare @.GoodRow bit

set @.COLLENGTH=0

set @.goodrow=1

select @.COLLENGTH =(SELECT sys.columns.max_length

FROM sys.columns INNER JOIN

sys.tables ON sys.columns.object_id = sys.tables.object_id

WHERE (sys.tables.name = N'ATABLE') and (sys.columns.name='a'))

if len(@.i)>@.collength

begin

print 'a has a big value'

print @.i

set @.goodrow=0

end

-- ...

if @.goodrow=1

INSERT INTO [test].[dbo].[Atable](,) VALUES (@.i,@.j)

END

if you run :

exec checkinsert '12345678','12'

the output is:

a has a big value

12345678

|||

Hi ggciubuc

Thanx for the reply.

Correct me if i am wrong but your proc will only check if you are inserting a value bigger than the max length of the largest column of a table.

So for example if i have a table with a hundred varchar columns most if which are over 100 in length , but my insert is inserting a char(6) value in to 1 of 30 char(5) columns it will still be very difficult to find the problem column. And your sql will not pick it up.

You sql will only find the issue if you are exceeding the length of your max char lenghth column, not any columns smaller than the max char length.

Thanx

|||

First you can optimize my sp creating a function let's say LengthColumn that return the max length of the column,

before insert you can verify all your 30 parameters and you can write a string by concatenating message like

'a has a big value'

and finally raise an error that write in event log

I thought your problem is , I quote

"When you do an insert into a table with literally hundreds of char or varchar columns and you get the error that the insert failed due to data loss/truncation on a column.

In your last post you say

"You sql will only find the issue if you are exceeding the length of your max char lenghth column, not any columns smaller than the max char length."

I think is not a problem vis-a-vis "data loss/truncation on a column".

Anyway in my code you can verify that length:

if len(@.i)>@.collength

begin

...

using

if len(@.i) < @.collength

begin

...

|||

Yes you are correct, the stored proc can be modified to check column by column.

It is just time consuming, i guess my main point was i thought it would be a simple things for the DB engine to actually return the column that the truncation was hapeening on, or some sort of easy way to figure out the column.

Even with your code it would be different for each table, and i have the issue of a result set of thousands of rows , so i dont know which insert is causing the problem.Therefore i would not know what parameters of which insert to pass to the store proc.

So it is a bit more complicated then i originally explained, bottom line there is no way sql server will tell you which column insert exceeded the length of the column , i was hoping there was an easy work around.

Thanx

Friday, February 10, 2012

Collation question

INSERT INTO #TMP_Table#
Select * from tabCS, tabCI where
tabCS.col1 COLLATE SQL_Latin1_General_CP1_CS_AS = tabCI.col1 COLLATE SQL_Latin1_General_CP1_CS_AS
Currently this query is run in a case insensitive server:
tabCS is table from a case sensitive server and
tabCI is a table from a Case insensitive server which is the same
server as the above query is run.
Can you please let me know #TMP_Table# is case sensitive or not?
Thanks in advance.Since you are INSERTing into a #temp table, you had to first create it. It
is the creation step that will control what collation the table uses.
CREATE TABLE #TMP_Table# -- Use the COLLATE clause of column definitions
SELECT * INTO #TMP_Table# FROM tabCS -- Uses the collations in tabCS
You should also read the Books Online topic "Collations in Distributed
Queries", for how collations are treated across linked servers.
RLF
<sweetpotatop@.yahoo.com> wrote in message
news:1174668894.028880.118650@.n59g2000hsh.googlegroups.com...
> INSERT INTO #TMP_Table#
> Select * from tabCS, tabCI where
> tabCS.col1 COLLATE SQL_Latin1_General_CP1_CS_AS => tabCI.col1 COLLATE SQL_Latin1_General_CP1_CS_AS
> Currently this query is run in a case insensitive server:
> tabCS is table from a case sensitive server and
> tabCI is a table from a Case insensitive server which is the same
> server as the above query is run.
> Can you please let me know #TMP_Table# is case sensitive or not?
> Thanks in advance.
>|||On Mar 23, 2:30 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Since you are INSERTing into a #temp table, you had to first create it. It
> is the creation step that will control what collation the table uses.
> CREATE TABLE #TMP_Table# -- Use theCOLLATEclause of column definitions
> SELECT * INTO #TMP_Table# FROM tabCS -- Uses the collations in tabCS
> You should also read the Books Online topic "Collations in Distributed
> Queries", for how collations are treated across linked servers.
> RLF
> <sweetpota...@.yahoo.com> wrote in message
> news:1174668894.028880.118650@.n59g2000hsh.googlegroups.com...
>
> > INSERT INTO #TMP_Table#
> > Select * from tabCS, tabCI where
> > tabCS.col1COLLATESQL_Latin1_General_CP1_CS_AS => > tabCI.col1COLLATESQL_Latin1_General_CP1_CS_AS
> > Currently this query is run in a case insensitive server:
> > tabCS is table from a case sensitive server and
> > tabCI is a table from a Case insensitive server which is the same
> > server as the above query is run.
> > Can you please let me know #TMP_Table# is case sensitive or not?
> > Thanks in advance.- Hide quoted text -
> - Show quoted text -
Usually there is no need to "CREATE" a table. In that case, what will
be the default? Will it take whatever from the local server?|||> Usually there is no need to "CREATE" a table. In that case, what will
> be the default? Will it take whatever from the local server?
You cannot insert into a table that doesn't exist. The collation for the column is determined when
you created the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<sweetpotatop@.yahoo.com> wrote in message
news:1174677392.350919.149260@.n59g2000hsh.googlegroups.com...
> On Mar 23, 2:30 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
>> Since you are INSERTing into a #temp table, you had to first create it. It
>> is the creation step that will control what collation the table uses.
>> CREATE TABLE #TMP_Table# -- Use theCOLLATEclause of column definitions
>> SELECT * INTO #TMP_Table# FROM tabCS -- Uses the collations in tabCS
>> You should also read the Books Online topic "Collations in Distributed
>> Queries", for how collations are treated across linked servers.
>> RLF
>> <sweetpota...@.yahoo.com> wrote in message
>> news:1174668894.028880.118650@.n59g2000hsh.googlegroups.com...
>>
>> > INSERT INTO #TMP_Table#
>> > Select * from tabCS, tabCI where
>> > tabCS.col1COLLATESQL_Latin1_General_CP1_CS_AS =>> > tabCI.col1COLLATESQL_Latin1_General_CP1_CS_AS
>> > Currently this query is run in a case insensitive server:
>> > tabCS is table from a case sensitive server and
>> > tabCI is a table from a Case insensitive server which is the same
>> > server as the above query is run.
>> > Can you please let me know #TMP_Table# is case sensitive or not?
>> > Thanks in advance.- Hide quoted text -
>> - Show quoted text -
> Usually there is no need to "CREATE" a table. In that case, what will
> be the default? Will it take whatever from the local server?
>|||<sweetpotatop@.yahoo.com> wrote in message
news:1174677392.350919.149260@.n59g2000hsh.googlegroups.com...
> Usually there is no need to "CREATE" a table. In that case, what will
> be the default? Will it take whatever from the local server?
>
If you're doing an INSERT INTO there is.
You may be thinking SELECT INTO.
In which case I BELIEV (but would have to test) that the collation will be
of the database you create it in. (If not, then it would be the one that
tempdb has.)
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 23, 3:36 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <sweetpota...@.yahoo.com> wrote in message
> news:1174677392.350919.149260@.n59g2000hsh.googlegroups.com...
>
> > Usually there is no need to "CREATE" a table. In that case, what will
> > be the default? Will it take whatever from the local server?
> If you're doing an INSERT INTO there is.
> You may be thinking SELECT INTO.
> In which case I BELIEV (but would have to test) that the collation will be
> of the database you create it in. (If not, then it would be the one that
> tempdb has.)
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
Oh yes, I mean SELECT INTO, so what happens to the temporary
collation? I think it is not taking the local server's collation...|||> Oh yes, I mean SELECT INTO, so what happens to the temporary
> collation?
For SELECT INTO, the collation is determined by the source column's data.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<sweetpotatop@.yahoo.com> wrote in message
news:1174679399.030741.227080@.n59g2000hsh.googlegroups.com...
> On Mar 23, 3:36 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>> <sweetpota...@.yahoo.com> wrote in message
>> news:1174677392.350919.149260@.n59g2000hsh.googlegroups.com...
>>
>> > Usually there is no need to "CREATE" a table. In that case, what will
>> > be the default? Will it take whatever from the local server?
>> If you're doing an INSERT INTO there is.
>> You may be thinking SELECT INTO.
>> In which case I BELIEV (but would have to test) that the collation will be
>> of the database you create it in. (If not, then it would be the one that
>> tempdb has.)
>> --
>> Greg Moore
>> SQL Server DBA Consulting
>> Email: sql (at) greenms.com http://www.greenms.com
> Oh yes, I mean SELECT INTO, so what happens to the temporary
> collation? I think it is not taking the local server's collation...
>|||On Mar 23, 3:53 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Oh yes, I mean SELECT INTO, so what happens to the temporary
> > collation?
> For SELECT INTO, the collation is determined by the source column's data.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <sweetpota...@.yahoo.com> wrote in message
> news:1174679399.030741.227080@.n59g2000hsh.googlegroups.com...
>
> > On Mar 23, 3:36 pm, "Greg D. Moore \(Strider\)"
> > <mooregr_deletet...@.greenms.com> wrote:
> >> <sweetpota...@.yahoo.com> wrote in message
> >>news:1174677392.350919.149260@.n59g2000hsh.googlegroups.com...
> >> > Usually there is no need to "CREATE" a table. In that case, what will
> >> > be the default? Will it take whatever from the local server?
> >> If you're doing an INSERT INTO there is.
> >> You may be thinking SELECT INTO.
> >> In which case I BELIEV (but would have to test) that the collation will be
> >> of the database you create it in. (If not, then it would be the one that
> >> tempdb has.)
> >> --
> >> Greg Moore
> >> SQL Server DBA Consulting
> >> Email: sql (at) greenms.com http://www.greenms.com
> > Oh yes, I mean SELECT INTO, so what happens to the temporary
> > collation? I think it is not taking the local server's collation...- Hide quoted text -
> - Show quoted text -
Then is there a quick way to specify all temporary tables will be
created in case insentive? And ignore what case sensitivity of the
source table or server?
Thanks in advance.|||<sweetpotatop@.yahoo.com> wrote in message
news:1174915057.926229.29490@.y80g2000hsf.googlegroups.com...
> Then is there a quick way to specify all temporary tables will be
> created in case insentive? And ignore what case sensitivity of the
> source table or server?
Yes, use the COLLATION parameter when creating the table.
> Thanks in advance.
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||As Greg said, SELECT INTO #temp# will create the table based on the
underlying properties of the source.
If the source table does not have collation defined then it would use the
source server collation. Since your source is from 2 servers then it's quite
likely it'll use the first servers collation for the temp table.
As you have to have the destination use case insensitive collation then you
would need to create the temp table first, specifying the collation, before
filling it with data. If you don't know what the temp table structure will be
(as you may possibly have unknown queries populating it), then that's a lot
more work but still doable.
Just insert the TOP 1 record into the temp table, then alter it to change
the collation, then do the full insert of data.

Tuesday, March 27, 2012

Combining PIVOT and INSERT queries

Thursday, March 22, 2012

combining 2 queries?

combining 2 queries?

combining 2 queries?

CombinedID

combine update statements....help...

Sunday, March 11, 2012

Columns_Updated () and Update() functions

Columns_Updated () and Update() functions

Thursday, March 8, 2012

Column-conscious bulk insert

Friday, February 24, 2012

column name as group in a table

Column insert help

Sunday, February 19, 2012

Column does not allow nulls

Thursday, February 16, 2012

Column Data truncation , how to identify column?

Friday, February 10, 2012

Collation question

Collation question

Collation problem

Blog Archive

About Me